MYSQL不支持子查询使用limit

最新推荐文章于 2021-03-24 01:24:33 发布

pingwei000

最新推荐文章于 2021-03-24 01:24:33 发布

阅读量339

点赞数

文章标签： MySQL SQL

本文链接：https://blog.csdn.net/pingwei000/article/details/83640047

版权

MYSQL不支持子查询使用limit 再仔细看这位高人的解决办法~ 确实赞~
mysql> -- -------------------------------
mysql> --
mysql> -- -------------------------------
mysql>
mysql> -- > 生成测试数据: @T
mysql> DROP TABLE IF EXISTS tb ;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE tb(录象文件编号 INT,录象文件级别 INT,录象文件时间 TIME);
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO tb
-> SELECT 1,1,'12:00:01' UNION ALL
-> SELECT 2,1,'12:00:02' UNION ALL
-> SELECT 3,1,'12:00:03' UNION ALL
-> SELECT 4,2,'12:00:01' UNION ALL
-> SELECT 5,2,'12:00:02' UNION ALL
-> SELECT 6,2,'12:00:03' UNION ALL
-> SELECT 7,3,'12:00:01' UNION ALL
-> SELECT 8,3,'12:00:02' UNION ALL
-> SELECT 9,3,'12:00:03';
Query OK, 9 rows affected (0.09 sec)
Records: 9 Duplicates: 0 Warnings: 0
mysql>
mysql> -- SQL查询如下:
mysql>
mysql> SELECT *
-> FROM tb AS A
-> WHERE 2>(SELECT COUNT(*) FROM tb
-> WHERE A.录象文件级别=录象文件级别
-> AND 录象文件时间<A.录象文件时间);
+--------------+--------------+--------------+
| 录象文件编号 | 录象文件级别 | 录象文件时间 |
+--------------+--------------+--------------+
| 1 | 1 | 12:00:01 |
| 2 | 1 | 12:00:02 |
| 4 | 2 | 12:00:01 |
| 5 | 2 | 12:00:02 |
| 7 | 3 | 12:00:01 |
| 8 | 3 | 12:00:02 |
+--------------+--------------+--------------+
6 rows in set (0.00 sec)