MYSQL不支持子查询使用limit
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)