mysql> SELECT * FROM atable;

+------+-------+

| id | grade |

+------+-------+

| A | 4 |

| A | 3 |

| A | 2 |

| A | 1 |

| B | 5 |

| B | 4 |

| B | 3 |

| B | 2 |

| B | 1 |

+------+-------+

9 rows in set (0.00 sec)

mysql> SELECT A1.id, A1.grade, COUNT(*) AS rank

-> FROM

-> atable AS A1

-> LEFT OUTER JOIN

-> atable AS A2

-> ON (A1.id = A2.id AND A1.grade <= A2.grade)

-> GROUP BY A1.id, A1.grade

-> HAVING rank <= 3

-> ORDER BY A1.id, A1.grade DESC

-> ;

+------+-------+------+

| id | grade | rank |

+------+-------+------+

| A | 4 | 1 |

| A | 3 | 2 |

| A | 2 | 3 |

| B | 5 | 1 |

| B | 4 | 2 |

| B | 3 | 3 |

+------+-------+------+

6 rows in set (0.00 sec)

mysql> explain SELECT A1.id, A1.grade, COUNT(*) AS rank

-> FROM

-> atable AS A1

-> LEFT OUTER JOIN

-> atable AS A2

-> ON (A1.id = A2.id AND A1.grade <= A2.grade)

-> GROUP BY A1.id, A1.grade

-> HAVING rank <= 3

-> ORDER BY A1.id, A1.grade DESC

-> ;

+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+

| 1 | SIMPLE | A1 | ALL | NULL | NULL | NULL | NULL | 9 | Using temporary; Using filesort |

| 1 | SIMPLE | A2 | ALL | NULL | NULL | NULL | NULL | 9 | |

+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+

2 rows in set (0.00 sec)

인덱스를 걸어줬더니 좀 나은 모양이 되었습니다.

mysql> CREATE INDEX idx ON atable (id, grade);

Query OK, 9 rows affected (0.07 sec)

레코드: 9개 중복: 0개 경고: 0개

mysql> explain SELECT A1.id, A1.grade, COUNT(*) AS rank

-> FROM

-> atable AS A1

-> LEFT OUTER JOIN

-> atable AS A2

-> ON (A1.id = A2.id AND A1.grade <= A2.grade)

-> GROUP BY A1.id, A1.grade

-> HAVING rank <= 3

-> ORDER BY A1.id, A1.grade DESC

-> ;

+----+-------------+-------+-------+---------------+------+---------+--------------+------+----------------------------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+-------+---------------+------+---------+--------------+------+----------------------------------------------+

| 1 | SIMPLE | A1 | index | NULL | idx | 5 | NULL | 9 | Using index; Using temporary; Using filesort |

| 1 | SIMPLE | A2 | ref | idx | idx | 3 | ymovie.A1.id | 4 | Using index |

+----+-------------+-------+-------+---------------+------+---------+--------------+------+----------------------------------------------+

2 rows in set (0.00 sec)

네, 저도 검색해서 찾았던 내용입니다.

문제는 메뉴별로 중간에 ms_seq와 ms_title을 어찌 끼워넣을까인데요..

끼워 넣는 것은 가능은 하겠지만 불가능하다고 봐야죠. 

컬럼을 따로 두고 불러오면 되고 불러오다 컬럼의 값이 바뀌는 것으로 구분하면 되겠네요. 

쿼리문 결과값을 불러오는 구조에 대해 고민해 보세요.