제 추측이 틀린건가요? 데이터가 이상하네요.
group_id 1 번에 대해 동일한 sequence_no 0000000001999999 가 4개나 있네요?
4개 다 메인글인가요?
댓글로 추정되는 sequence_no 가 5개가 있는데?
어떤 근거로 연결하나요? sequence_no 로 댓글관계 확인이 가능한가요?
article_id | group_id | sequence_no | posting_date | read_count | writer_name | password | title | content |
-----------+----------+------------------+---------------------+------------+-------------+----------+--------+---------+
1 | 1 | 0000000001999999 | 2017-06-08 00:05:00 | 19 | a | a | a | a |
2 | 1 | 0000000001999999 | 2017-06-08 00:05:08 | 3 | b | b | b23 | b23 |
3 | 1 | 0000000001999999 | 2017-06-08 00:05:16 | 2 | c | c | c | c |
4 | 1 | 0000000001999999 | 2017-06-08 00:05:27 | 5 | d | d | d | d |
43 | 1 | 0000000001989999 | 2017-06-08 03:28:58 | 0 | a | a | re: | a |
44 | 1 | 0000000001979999 | 2017-06-08 03:30:32 | 0 | a | 1 | re: | a |
50 | 1 | 0000000001969999 | 2017-06-08 03:39:30 | 2 | g | g | re: | g |
54 | 1 | 0000000001969899 | 2017-06-08 03:41:43 | 0 | d | d | re: | d |
68 | 1 | 0000000001959999 | 2017-06-11 02:22:42 | 0 | 55 | 55 | re: | 55 |
-----------+----------+------------------+---------------------+------------+-------------+----------+--------+---------+
(5, 13, 6) 의 댓글 6개
(1, 63, 9) 의 댓글 4개
+-----+------------+----------+------------------+---------------------+------------+-------------+----------+--------+---------+
| bid | article_id | group_id | sequence_no | posting_date | read_count | writer_name | password | title | content |
+-----+------------+----------+------------------+---------------------+------------+-------------+----------+--------+---------+
| 5 | 13 | 6 | 0000000006999999 | 2017-06-08 00:57:15 | 5 | gg | gg | gg | gg |
| 5 | 45 | 6 | 0000000006989999 | 2017-06-08 03:30:47 | 0 | d | d | re: | d |
| 5 | 46 | 6 | 0000000006979999 | 2017-06-08 03:31:30 | 4 | asdasd | a | re: | a |
| 5 | 79 | 6 | 0000000006979899 | 2017-06-11 17:30:18 | 5 | yy | yy | yy3 | yy3 |
| 5 | 80 | 6 | 0000000006979898 | 2017-06-11 17:30:25 | 2 | gf | gf | gf | gf |
| 5 | 81 | 6 | 0000000006979897 | 2017-06-11 20:07:35 | 0 | jj | jj | re: jj | jj |
| 5 | 82 | 6 | 0000000006979799 | 2017-06-11 20:07:48 | 0 | vv | vv | re: vv | vv |
+-----+------------+----------+------------------+---------------------+------------+-------------+----------+--------+---------+
| bid | article_id | group_id | sequence_no | posting_date | read_count | writer_name | password | title | content |
+-----+------------+----------+------------------+---------------------+------------+-------------+----------+--------+---------+
| 1 | 63 | 9 | 0000000009999999 | 2017-06-09 22:59:58 | 4 | tt | tt | tt | tt |
| 1 | 66 | 9 | 0000000009989999 | 2017-06-10 14:27:51 | 2 | yy | yy | yy | yy |
| 1 | 85 | 9 | 0000000009989899 | 2017-06-11 21:46:50 | 1 | ii | ii | re: | ii |
| 1 | 111 | 9 | 0000000009979999 | 2017-06-14 00:58:39 | 0 | zx | zx | re: zx | zx |
| 1 | 112 | 9 | 0000000009969999 | 2017-06-14 00:58:56 | 0 | ui | ui | re: ui | ui |
+-----+------------+----------+------------------+---------------------+------------+-------------+----------+--------+---------+
SELECT b.* FROM (SELECT COUNT(*) - 1 cnt FROM board a INNER JOIN board b ON b.bid = a.bid AND b.group_id = a.group_id AND b.sequence_no LIKE CONCAT ( SUBSTR(a.sequence_no, 1, INSTR(SUBSTR(a.sequence_no, 11, 6), '99') + 9) , '__' , SUBSTR(a.sequence_no, 12 + INSTR(SUBSTR(a.sequence_no, 11, 6), '99') , 5 - INSTR(SUBSTR(a.sequence_no, 11, 6), '99') ) ) GROUP BY a.bid, a.group_id, a.article_id, a.sequence_no ORDER BY cnt DESC LIMIT 1 ) a INNER JOIN (SELECT a.bid, a.group_id, a.article_id, a.sequence_no , COUNT(*) - 1 cnt FROM board a INNER JOIN board b ON b.bid = a.bid AND b.group_id = a.group_id AND b.sequence_no LIKE CONCAT ( SUBSTR(a.sequence_no, 1, INSTR(SUBSTR(a.sequence_no, 11, 6), '99') + 9) , '__' , SUBSTR(a.sequence_no, 12 + INSTR(SUBSTR(a.sequence_no, 11, 6), '99') , 5 - INSTR(SUBSTR(a.sequence_no, 11, 6), '99') ) ) GROUP BY a.bid, a.group_id, a.article_id, a.sequence_no ) b ON a.cnt = b.cnt ;