mysql> select * from RB_OCUS_POS_TEST2 limit 10;
+-------+------------+----------------------+--------------+---------------+---------------+------------+------------+------------------------+
| seqno | account_no | stock_symbol | start_volume | start_amount | start_price | stock_type | trustee_id | regtime |
+-------+------------+----------------------+--------------+---------------+---------------+------------+------------+------------------------+
| 1 | 0016100 | BANPU | 0000045000 | 0000645569.57 | 000014.345990 | 02 | | 20190829 15:31:55(410) |
| 2 | 0016100 | BBL | 0000001500 | 0000266962.22 | 000177.974813 | 02 | | 20190829 15:31:55(410) |
| 3 | 0016100 | BPP | 0000002000 | 0000042000.00 | 000021.000000 | 02 | | 20190829 15:31:55(410) |
| 4 | 0016100 | CK | 0000015375 | 0000296816.51 | 000019.305139 | 02 | | 20190829 15:31:55(410) |
| 5 | 0016100 | IEC | 0012286603 | 0000308909.54 | 000000.025142 | 02 | | 20190829 15:31:55(410) |
| 6 | 0016100 | IVL | 0000011769 | 0000309729.03 | 000026.317362 | 02 | | 20190829 15:31:55(410) |
| 7 | 0016100 | KTB | 0000005000 | 0000103785.51 | 000020.757102 | 02 | | 20190829 15:31:55(410) |
| 8 | 0055920 | ASAP | 0000001000 | 0000003106.87 | 000003.106870 | 02 | | 20190829 15:31:55(411) |
| 9 | 0055920 | BCP | 0000001000 | 0000033574.22 | 000033.574220 | 02 | | 20190829 15:31:55(411) |
| 10 | 0055920 | CBG | 0000000800 | 0000042844.70 | 000053.555875 | 02 | | 20190829 15:31:55(411) |
+-------+------------+----------------------+--------------+---------------+---------------+------------+------------+------------------------+
10 rows in set (0.00 sec)
mysql>
mysql> select * from DUMMY;
+-------+------------+----------------------+--------------+---------------+---------------+------------+------------+
| seqno | account_no | stock_symbol | start_volume | start_amount | start_price | stock_type | trustee_id |
+-------+------------+----------------------+--------------+---------------+---------------+------------+------------+
| 1 | 0016100 | BANPU | 0000045000 | 0000645569.57 | 000014.345990 | 02 | |
+-------+------------+----------------------+--------------+---------------+---------------+------------+------------+
1 row in set (0.00 sec)
mysql>
mysql> select @r:=@r+1 As rowNum, T2.* from DUMMY T1, (select substr(regtime,1,17) as t_regtime, count(1) as t_cnt from RB_OCUS_POS_TEST2 group by substr(regtime,1,17)) T2, (Select @r:=0) r limit 100;
+--------+-------------------+-------+
| rowNum | t_regtime | t_cnt |
+--------+-------------------+-------+
| 1 | 20190829 15:31:55 | 2640 |
| 2 | 20190829 15:31:56 | 4744 |
| 3 | 20190829 15:31:57 | 5036 |
| 4 | 20190829 15:31:58 | 5270 |
| 5 | 20190829 15:31:59 | 2587 |
| 6 | 20190829 15:32:00 | 2909 |
| 7 | 20190829 15:32:01 | 3261 |
| 8 | 20190829 15:32:02 | 5019 |
| 9 | 20190829 15:32:03 | 5488 |
| 10 | 20190829 15:32:04 | 5853 |
| 11 | 20190829 15:32:05 | 4381 |
| 12 | 20190829 15:32:06 | 4307 |
| 13 | 20190829 15:32:07 | 2871 |
| 14 | 20190829 15:32:08 | 345 |
| 15 | 20190829 15:32:09 | 3368 |
| 16 | 20190829 15:32:10 | 3087 |
mysql> select @r:=@r+1 As rowNum, T2.* from DUMMY T1, (select substr(regtime,1,17) as t_regtime, count(1) as t_cnt from RB_OCUS_POS_TEST2 group by substr(regtime,1,17)) T2, (Select @r:=0) r limit 100;
+--------+-------------------+-------+
| rowNum | t_regtime | t_cnt |
+--------+-------------------+-------+
| 1 | 20190829 15:31:55 | 2640 |
| 2 | 20190829 15:31:56 | 4744 |
| 3 | 20190829 15:31:57 | 5036 |
| 4 | 20190829 15:31:58 | 5270 |
| 5 | 20190829 15:31:59 | 2587 |
| 6 | 20190829 15:32:00 | 2909 |
| 7 | 20190829 15:32:01 | 3261 |
| 8 | 20190829 15:32:02 | 5019 |
| 9 | 20190829 15:32:03 | 5488 |
| 10 | 20190829 15:32:04 | 5853 |
| 11 | 20190829 15:32:05 | 4381 |
| 12 | 20190829 15:32:06 | 4307 |
>>아래 쿼리의 내용은 큐를 이용해서 데이타를 입력했을때에 순서적으로 입력이 안되는 상태임을 보여주는 예입니다.
>>큐가 10개라면, SERIAL하게 데이타가 입력되는것에 대한 정합성을 보장하지 않습니다.
>>저장하는 용도의 입력이면 괜찮지만, 순서적으로 입력이 보장되어야 한다면, 다른 로직을 넣어서 이를 해결해야 합니다.
mysql> select T2.rowNum, T2.t_seqno, T2.rowNum - T2.t_seqno from DUMMY T1, (select @r:=@r+1 As rowNum, T1.seqno as t_seqno from RB_OCUS_POS_TEST2 T1, (Select @r:=0) r order by regtime asc) T2 order by T2.t_seqno asc limit 100;
+--------+---------+------------------------+
| rowNum | t_seqno | T2.rowNum - T2.t_seqno |
+--------+---------+------------------------+
| 1 | 1 | 0 |
| 2 | 2 | 0 |
| 3 | 3 | 0 |
| 4 | 4 | 0 |
| 5 | 5 | 0 |
| 6 | 6 | 0 |
| 7 | 7 | 0 |
| 8 | 8 | 0 |
| 9 | 9 | 0 |
| 10 | 10 | 0 |
| 12 | 11 | 1 |
| 11 | 12 | -1 |
| 13 | 13 | 0 |
mysql> select T2.rowNum, T2.t_seqno, T2.rowNum - T2.t_seqno from DUMMY T1, (select @r:=@r+1 As rowNum, T1.seqno as t_seqno from RB_OCUS_POS_TEST2 T1, (Select @r:=0) r order by regtime asc) T2 where T2.rowNum - T2.t_seqno <> 0 order by T2.t_seqno asc limit 100;
+--------+---------+------------------------+
| rowNum | t_seqno | T2.rowNum - T2.t_seqno |
+--------+---------+------------------------+
| 12 | 11 | 1 |
| 11 | 12 | -1 |
| 32 | 31 | 1 |
| 33 | 32 | 1 |
| 40 | 33 | 7 |
| 31 | 34 | -3 |
| 34 | 35 | -1 |
| 35 | 36 | -1 |
| 36 | 37 | -1 |
| 37 | 38 | -1 |
| 38 | 39 | -1 |
| 39 | 40 | -1 |
| 47 | 43 | 4 |
| 43 | 44 | -1 |
| 44 | 45 | -1 |
| 45 | 46 | -1 |
| 46 | 47 | -1 |
| 60 | 53 | 7 |
'데이타베이스 > MySQL' 카테고리의 다른 글
| #LINUX,send_msg_to_queue.c (2번째) (0) | 2019.10.30 |
|---|---|
| #LINUX,recv_msg_from_queue.c (두번째) (0) | 2019.10.30 |
| 1달전 자료 지우는 쿼리 (0) | 2019.10.29 |
| MySQL LOCK 확인 및 해제(kill) 방법 (0) | 2019.10.29 |
| #innodb_flush_log_at_trx_commit 에 따른 입력건수 성능비교 (0) | 2019.10.29 |