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)
这个效果可以么?
追问太感谢了,不过完全不懂意思,能跟我说下吗
追答你去看那个参考资料的帖子了么?
如果上面的 SQL , 你不怎么看得懂, 那么下面这样的写法,与执行结果,应该能让你更加容易明白一些上面的SQL的处理的原理。
SELECT
channelId ,
subChanID ,
(SELECT COUNT(1)
FROM test sub
WHERE
main.channelId = sub.channelId
AND main.subChanID > sub.subChanID
) AS 有多少行数据subChanID比当前行小
FROM
test main
参考资料:http://hi.baidu.com/wangzhiqing999/blog/item/46903f55aa5143818d543069.html
本回答被提问者采纳