请教一个比较复杂是SQL语句,主要是为了提高性能考虑,对字段分组地获得最早数据

jyg8888 2009-04-23 01:34:04

我有这样一张表

录象文件编号,录象文件级别,录象文件时间
1,1,12:00:01
2,1,12:00:02
3,1,12:00:03
4,2,12:00:01
5,2,12:00:02
6,2,12:00:03
7,3,12:00:01
8,3,12:00:02
9,3,12:00:03
------------------------
我要同过一个SQL语句查询出
每种录象文件级别的前N个最早录象文件时间的数据

比如我要查前2个最早录象文件时间结果应该是

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

怎么实现呀小弟目前用了三次查询分别查询,但是如果级别一多那查询的次数也越多
求教

...全文

82 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

ACMAIN_CHM 2009-04-23

打赏
举报

mysql> select * from t5;

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

| id | cls  | ct                  |

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

|  1 |    1 | 2004-04-12 12:00:01 |

|  2 |    1 | 2004-04-12 12:00:02 |

|  3 |    1 | 2004-04-12 12:00:03 |

|  4 |    2 | 2004-04-12 12:00:01 |

|  5 |    2 | 2004-04-12 12:00:02 |

|  6 |    2 | 2004-04-12 12:00:03 |

|  7 |    3 | 2004-04-12 12:00:01 |

|  8 |    3 | 2004-04-12 12:00:02 |

|  9 |    3 | 2004-04-12 12:00:03 |

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

9 rows in set (0.00 sec)



mysql> select *

    -> from t5 a

    -> where 2>=(select count(*) from t5 where cls=a.cls and ct<=a.ct)

    -> order by cls,ct;

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

| id | cls  | ct                  |

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

|  1 |    1 | 2004-04-12 12:00:01 |

|  2 |    1 | 2004-04-12 12:00:02 |

|  4 |    2 | 2004-04-12 12:00:01 |

|  5 |    2 | 2004-04-12 12:00:02 |

|  7 |    3 | 2004-04-12 12:00:01 |

|  8 |    3 | 2004-04-12 12:00:02 |

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

6 rows in set (0.00 sec)

WWWWA 2009-04-23

打赏
举报

贴记录，举例说明

jyg8888 2009-04-23

打赏
举报

有这种情况 1级别优先级的录象文件有多个
2级别优先级的录象文件可能一个都没有

jyg8888 2009-04-23

打赏
举报

但问题是结果好象不对
我查出来怎么会只有2条记录应该有6条记录的

ACMAIN_CHM 2009-04-23

打赏
举报

2>=(select count(*) from yourTable where 录象文件级别=a.录象文件级别 and 录象文件时间 <=a.录象文件时间)

(select count(*) from yourTable where 录象文件级别=a.录象文件级别 and 录象文件时间 <=a.录象文件时间)
会得到一个 count(*) 整数，也就是录象文件级别=a.录象文件级别并且时间早于当前记录时间的记录数

这个记录总数要<=2;

jyg8888 2009-04-23

打赏
举报

select *
from yourTable a
where 2>=(select count(*) from yourTable where 录象文件级别=a.录象文件级别 and 录象文件时间<=a.录象文件时间)
order by 录象文件级别;

是什么意思呀我怎么看不明白啊
2>=是什么啊

子查询不是要先处理吗怎么在子查询里用到外面SELECT的表格了应该看不见啊
不理解求教

WWWWA 2009-04-23

打赏
举报

select a.录象文件级别,a.录象文件时间 from tt a
left join tt b
on a.录象文件级别=b.录象文件级别 and a.录象文件时间>=b.录象文件时间
group by a.录象文件级别,a.录象文件时间
having count(b.录象文件级别)<=2

ACMAIN_CHM 2009-04-23

打赏
举报

select * 

from yourTable a 

where 2>=(select count(*) from yourTable where 录象文件级别=a.录象文件级别 and 录象文件时间<=a.录象文件时间)

order by 录象文件级别;

liangCK 2009-04-23

打赏
举报

mysql> -- -------------------------------

mysql> --  Author: liangCK 小梁

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)

liangCK 2009-04-23

打赏
举报

---------------------------------

--  Author: liangCK 小梁

--  Title : 查每个分组前N条记录

--  Date  : 2008-11-13 17:19:23

---------------------------------

 

--> 生成测试数据: #T

IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T

CREATE TABLE #T (ID VARCHAR(3),GID INT,Author VARCHAR(29),Title VARCHAR(39),Date DATETIME)

INSERT INTO #T

SELECT '001',1,'邹建','深入浅出SQLServer2005开发管理与应用实例','2008-05-10' UNION ALL

SELECT '002',1,'胡百敬','SQLServer2005性能调校','2008-03-22' UNION ALL

SELECT '003',1,'格罗夫Groff.J.R.','SQL完全手册','2009-07-01' UNION ALL

SELECT '004',1,'KalenDelaney','SQLServer2005技术内幕存储引擎','2008-08-01' UNION ALL

SELECT '005',2,'Alex.Kriegel.Boris.M.Trukhnov','SQL宝典','2007-10-05' UNION ALL

SELECT '006',2,'飞思科技产品研发中心','SQLServer2000高级管理与开发','2007-09-10' UNION ALL

SELECT '007',2,'胡百敬','SQLServer2005数据库开发详解','2008-06-15' UNION ALL

SELECT '008',3,'陈浩奎','SQLServer2000存储过程与XML编程','2005-09-01' UNION ALL

SELECT '009',3,'赵松涛','SQLServer2005系统管理实录','2008-10-01' UNION ALL

SELECT '010',3,'黄占涛','SQL技术手册','2006-01-01'



--SQL查询如下:



--按GID分组,查每个分组中Date最新的前2条记录





--1.字段ID唯一时:

SELECT * FROM #T AS T WHERE ID IN(SELECT TOP 2 ID FROM #T WHERE GID=T.GID ORDER BY Date DESC)



--2.如果ID不唯一时:

SELECT * FROM #T AS T WHERE 2>(SELECT COUNT(*) FROM #T WHERE GID=T.GID AND Date>T.Date)



--SQL Server 2005 使用新方法



--3.使用ROW_NUMBER()进行排位分组

SELECT ID,GID,Author,Title,Date

FROM

(

   SELECT rid=ROW_NUMBER() OVER(PARTITION BY GID ORDER BY Date DESC),*

   FROM #T

) AS T

WHERE rid<=2



--4.使用APPLY

SELECT DISTINCT b.*

FROM #T AS a

CROSS APPLY

(

    SELECT TOP(2) * FROM #T WHERE a.GID=GID ORDER BY Date DESC

) AS b





--结果

/*



ID   GID         Author                        Title                                   Date

---- ----------- ----------------------------- --------------------------------------- -----------------------

003  1           格罗夫Groff.J.R.                 SQL完全手册                                 2009-07-01 00:00:00.000

004  1           KalenDelaney                  SQLServer2005技术内幕存储引擎                   2008-08-01 00:00:00.000

005  2           Alex.Kriegel.Boris.M.Trukhnov SQL宝典                                   2007-10-05 00:00:00.000

007  2           胡百敬                           SQLServer2005数据库开发详解                    2008-06-15 00:00:00.000

009  3           赵松涛                           SQLServer2005系统管理实录                     2008-10-01 00:00:00.000

010  3           黄占涛                           SQL技术手册                                 2006-01-01 00:00:00.000



(6 行受影响)

*/