CREATE TABLE test (
channelId int,
subChanID INT
);
INSERT INTO test
SELECT 1, 11 UNION ALL
SELECT 1, 12 UNION ALL
SELECT 1, 13 UNION ALL
SELECT 1, 14 UNION ALL
SELECT 1, 15 UNION ALL
SELECT 2, 21 UNION ALL
SELECT 2, 22 UNION ALL
SELECT 2, 23 UNION ALL
SELECT 2, 24 UNION ALL
SELECT 2, 25;
SELECT
*
FROM
test main
WHERE
(SELECT COUNT(1)
FROM test sub
WHERE
main.channelId = sub.channelId
AND main.subChanID > sub.subChanID
) < 3;
+-----------+-----------+
| channelId | subChanID |
+-----------+-----------+
| 1 | 11 |
| 1 | 12 |
| 1 | 13 |
| 2 | 21 |
| 2 | 22 |
| 2 | 23 |
+-----------+-----------+
6 rows in set (0.00 sec)
这个效果可以么?