--> liangCK小梁 于2008-10-21 --> 生成测试数据: #T IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T CREATE TABLE #T (id INT,fileID INT,uid INT,createDate DATETIME) INSERT INTO #T SELECT 1,10,1,'2008-7-3 11:41:00' UNION ALL SELECT 2,10,2,'2008-7-4 11:41:00' UNION ALL SELECT 3,11,1,'2008-7-5 11:41:00' UNION ALL SELECT 4,10,1,'2008-7-6 11:41:00' UNION ALL SELECT 5,10,2,'2008-7-10 11:41:00' --SQL查询如下:SELECT TOP 3 * FROM #T AS t WHERE NOT EXISTS ( SELECT * FROM #T WHERE uid=t.uid AND createDate>t.createDate ) AND fileID=10 ORDER BY createDate DESC;/* id fileID uid createDate ----------- ----------- ----------- ----------------------- 5 10 2 2008-07-10 11:41:00.000 4 10 1 2008-07-06 11:41:00.000(2 行受影响) */
fileID=10有好幾條記錄,它的前3條如何劃分?
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T CREATE TABLE #T (id INT,fileID INT,uid INT,createDate DATETIME) INSERT INTO #T SELECT 1,10,1,'2008-7-3 11:41:00' UNION ALL SELECT 2,10,2,'2008-7-4 11:41:00' UNION ALL SELECT 3,11,1,'2008-7-5 11:41:00' UNION ALL SELECT 4,10,1,'2008-7-6 11:41:00' UNION ALL SELECT 5,10,2,'2008-7-10 11:41:00' select top 3 * from #T t where id !<all(select id from #T where uid=t.uid AND createDate>t.createDate) and fileid=10 ORDER BY createDate DESC /* id fileID uid createDate ----------- ----------- ----------- ------------------------------------------------------ 5 10 2 2008-07-10 11:41:00.000 4 10 1 2008-07-06 11:41:00.000 */
IF OBJECT_ID('tb','u') IS NOT NULL DROP TABLE tb GO CREATE TABLE tb (id INT,fileID INT,uid INT,createDate DATETIME) GO INSERT INTO tb SELECT 1,10,1,'2008-7-3 11:41:00' UNION ALL SELECT 2,10,2,'2008-7-4 11:41:00' UNION ALL SELECT 3,11,1,'2008-7-5 11:41:00' UNION ALL SELECT 4,10,1,'2008-7-6 11:41:00' UNION ALL SELECT 5,10,2,'2008-7-10 11:41:00' UNION ALL SELECT 6,10,3,GETDATE() GO SELECT a.* FROM tb a INNER JOIN (SELECT TOP 3 UID,MAX(createDate) md FROM tb WHERE fileID=10 GROUP BY uid ORDER BY md DESC ) b ON a.uid=b.uid AND createDate = md ORDER BY md DESC
select top 3 * from aa inner join (select distinct uid from bb order by createDate desc ) cc on aa.uid=cc.uid where fileid=10
CREATE TABLE #tb (id INT,fileID INT,uid INT,createDate DATETIME) GO INSERT INTO #tb SELECT 1,10,1,'2008-7-3 11:41:00' UNION ALL SELECT 2,10,2,'2008-7-4 11:41:00' UNION ALL SELECT 3,11,1,'2008-7-5 11:41:00' UNION ALL SELECT 4,10,1,'2008-7-6 11:41:00' UNION ALL SELECT 5,10,2,'2008-7-10 11:41:00' UNION ALL SELECT 6,10,3,GETDATE()select top 3 * from (select uid,createDate=min(createDate) from #tb where fileid=10 group by uid)aa order by createDate asc
select *from 表 where id in(select top 3 id from 表 where fileId=10 group by uid order by createDate desc)对的啊~~~~~~~~~你们怎么不测试吗??晕~~
可以探讨一下 这两语句,哪个效率最高 SELECT a.* FROM tb a INNER JOIN (SELECT TOP 3 UID,MAX(createDate) md FROM tb WHERE fileID=10 GROUP BY uid ORDER BY md DESC ) b ON a.uid=b.uid AND createDate = md ORDER BY md DESC ------select top 3 * from (select uid,createDate=min(createDate) from #tb where fileid=10 group by uid)aa order by createDate asc
select top 3 * from tb t where not exists(select * from tb where uid=t.uid and createDate>t.createDate) and fileId=10 order by createDate desc 借小梁的sql练练笔
sql语句,追求逻辑严密,也追求效率。 逻辑可以千变万化,,效率单位只有ms
--偶像的结果似乎也有点问题,还是不能将用户去重。 --> 测试数据: @s declare @s table (id int,fileID int,uid int,createDate datetime) insert into @s select 1,10,1,'2008-7-3 11:41:00' union all select 2,10,2,'2008-7-4 11:41:00' union all select 3,11,1,'2008-7-5 11:41:00' union all select 4,10,1,'2008-7-6 11:42:00' union all select 5,10,2,'2008-7-10 11:41:00' union all select 6,10,4,'2008-7-10 11:41:00' union all select 7,10,3,'2008-7-10 11:44:00' union all select 8,10,5,'2008-7-10 11:45:00' union all select 9,10,5,'2008-7-10 11:45:00' SELECT a.* FROM @s a INNER JOIN (SELECT TOP 3 UID,MAX(createDate) md FROM @s WHERE fileID=10 GROUP BY uid ORDER BY md DESC ) b ON a.uid=b.uid AND createDate = md ORDER BY md DESC --结果: id fileID uid createDate ----------- ----------- ----------- -------------------------- 8 10 5 2008-07-10 11:45:00.000 9 10 5 2008-07-10 11:45:00.000 7 10 3 2008-07-10 11:44:00.000 6 10 4 2008-07-10 11:41:00.000
--> 测试数据: @s declare @s table (id int,fileID int,uid int,createDate datetime) insert into @s select 1,10,1,'2008-7-3 11:41:00' union all select 2,10,2,'2008-7-4 11:41:00' union all select 3,11,1,'2008-7-5 11:41:00' union all select 4,10,1,'2008-7-6 11:42:00' union all select 5,10,2,'2008-7-10 11:41:00' union all select 6,10,4,'2008-7-10 11:41:00' union all select 7,10,3,'2008-7-10 11:44:00' union all select 8,10,5,'2008-7-10 11:45:00' union all select 9,10,5,'2008-7-10 11:45:00' SELECT DISTINCT a.fileID, b.uid,b.createDate FROM @s AS a CROSS APPLY ( SELECT TOP 3 uid,MAX(createDate) createDate FROM @s WHERE fileID=10 GROUP BY uid ORDER BY createDate ) AS b WHERE a.fileID=10 ORDER BY b.createDate DESC/* fileID uid createDate ----------- ----------- ----------------------- 10 2 2008-07-10 11:41:00.000 10 4 2008-07-10 11:41:00.000 10 1 2008-07-06 11:42:00.000(3 行受影响) */
加个DESC..--> 测试数据: @s declare @s table (id int,fileID int,uid int,createDate datetime) insert into @s select 1,10,1,'2008-7-3 11:41:00' union all select 2,10,2,'2008-7-4 11:41:00' union all select 3,11,1,'2008-7-5 11:41:00' union all select 4,10,1,'2008-7-6 11:42:00' union all select 5,10,2,'2008-7-10 11:41:00' union all select 6,10,4,'2008-7-10 11:41:00' union all select 7,10,3,'2008-7-10 11:44:00' union all select 8,10,5,'2008-7-10 11:45:00' union all select 9,10,5,'2008-7-10 11:45:00' SELECT DISTINCT a.fileID, b.uid,b.createDate FROM @s AS a CROSS APPLY ( SELECT TOP 3 uid,MAX(createDate) createDate FROM @s WHERE fileID=A.fileID GROUP BY uid ORDER BY createDate DESC ) AS b WHERE a.fileID=10 ORDER BY b.createDate DESC/* fileID uid createDate ----------- ----------- ----------------------- 10 5 2008-07-10 11:45:00.000 10 3 2008-07-10 11:44:00.000 10 4 2008-07-10 11:41:00.000(3 行受影响) */
-- sql2000 写法 declare @s table (id int,fileID int,uid int,createDate datetime) insert into @s select 1,10,1,'2008-7-3 11:41:00' union all select 2,10,2,'2008-7-4 11:41:00' union all select 3,11,1,'2008-7-5 11:41:00' union all select 4,10,1,'2008-7-6 11:42:00' union all select 5,10,2,'2008-7-10 11:41:00' union all select 6,10,4,'2008-7-10 11:41:00' union all select 7,10,3,'2008-7-10 11:44:00' union all select 8,10,5,'2008-7-10 11:45:00' union all select 9,10,5,'2008-7-10 11:45:00'select top 3 * from @s a where fileID = 10 and not exists (select * from @s b where b.fileID = a.fileID and a.uid = b.uid and(b.createDate > a.createDate or(b.createDate = a.createDate and b.id > a.id))) order by createDate desc select top 3 max(id) as id,fileID,uid,max(createDate) as createDate from @s where fileID = 10 group by fileID,uid order by createDate desc
select top 3 from 表 where id in (select id,count(id) from aaa group by id having count(id) = 1) and fileid=10 order by createDate
select top 3 * from 表 where id in (select id,count(id) from aaa group by id having count(id) = 1) and fileid=10 order by createDate desc
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (id INT,fileID INT,uid INT,createDate DATETIME)
INSERT INTO #T
SELECT 1,10,1,'2008-7-3 11:41:00' UNION ALL
SELECT 2,10,2,'2008-7-4 11:41:00' UNION ALL
SELECT 3,11,1,'2008-7-5 11:41:00' UNION ALL
SELECT 4,10,1,'2008-7-6 11:41:00' UNION ALL
SELECT 5,10,2,'2008-7-10 11:41:00' --SQL查询如下:SELECT TOP 3 *
FROM #T AS t
WHERE NOT EXISTS
(
SELECT *
FROM #T
WHERE uid=t.uid
AND createDate>t.createDate
)
AND fileID=10
ORDER BY createDate DESC;/*
id fileID uid createDate
----------- ----------- ----------- -----------------------
5 10 2 2008-07-10 11:41:00.000
4 10 1 2008-07-06 11:41:00.000(2 行受影响)
*/
CREATE TABLE #T (id INT,fileID INT,uid INT,createDate DATETIME)
INSERT INTO #T
SELECT 1,10,1,'2008-7-3 11:41:00' UNION ALL
SELECT 2,10,2,'2008-7-4 11:41:00' UNION ALL
SELECT 3,11,1,'2008-7-5 11:41:00' UNION ALL
SELECT 4,10,1,'2008-7-6 11:41:00' UNION ALL
SELECT 5,10,2,'2008-7-10 11:41:00'
select top 3 * from #T t where id !<all(select id from #T where uid=t.uid
AND createDate>t.createDate) and fileid=10
ORDER BY createDate DESC
/*
id fileID uid createDate
----------- ----------- ----------- ------------------------------------------------------
5 10 2 2008-07-10 11:41:00.000
4 10 1 2008-07-06 11:41:00.000
*/
DROP TABLE tb
GO
CREATE TABLE tb (id INT,fileID INT,uid INT,createDate DATETIME)
GO
INSERT INTO tb
SELECT 1,10,1,'2008-7-3 11:41:00' UNION ALL
SELECT 2,10,2,'2008-7-4 11:41:00' UNION ALL
SELECT 3,11,1,'2008-7-5 11:41:00' UNION ALL
SELECT 4,10,1,'2008-7-6 11:41:00' UNION ALL
SELECT 5,10,2,'2008-7-10 11:41:00' UNION ALL
SELECT 6,10,3,GETDATE()
GO
SELECT a.* FROM tb a
INNER JOIN
(SELECT TOP 3 UID,MAX(createDate) md
FROM tb WHERE fileID=10
GROUP BY uid ORDER BY md DESC
) b
ON a.uid=b.uid AND createDate = md
ORDER BY md DESC
inner join (select distinct uid from bb order by createDate desc ) cc on aa.uid=cc.uid
where fileid=10
CREATE TABLE #tb (id INT,fileID INT,uid INT,createDate DATETIME)
GO
INSERT INTO #tb
SELECT 1,10,1,'2008-7-3 11:41:00' UNION ALL
SELECT 2,10,2,'2008-7-4 11:41:00' UNION ALL
SELECT 3,11,1,'2008-7-5 11:41:00' UNION ALL
SELECT 4,10,1,'2008-7-6 11:41:00' UNION ALL
SELECT 5,10,2,'2008-7-10 11:41:00' UNION ALL
SELECT 6,10,3,GETDATE()select top 3 * from
(select uid,createDate=min(createDate) from #tb where fileid=10 group by uid)aa order by createDate asc
SELECT a.* FROM tb a
INNER JOIN
(SELECT TOP 3 UID,MAX(createDate) md
FROM tb WHERE fileID=10
GROUP BY uid ORDER BY md DESC
) b
ON a.uid=b.uid AND createDate = md
ORDER BY md DESC
------select top 3 * from
(select uid,createDate=min(createDate) from #tb where fileid=10 group by uid)aa order by createDate asc
and fileId=10 order by createDate desc
借小梁的sql练练笔
sql语句,追求逻辑严密,也追求效率。
逻辑可以千变万化,,效率单位只有ms
--> 测试数据: @s
declare @s table (id int,fileID int,uid int,createDate datetime)
insert into @s
select 1,10,1,'2008-7-3 11:41:00' union all
select 2,10,2,'2008-7-4 11:41:00' union all
select 3,11,1,'2008-7-5 11:41:00' union all
select 4,10,1,'2008-7-6 11:42:00' union all
select 5,10,2,'2008-7-10 11:41:00' union all
select 6,10,4,'2008-7-10 11:41:00' union all
select 7,10,3,'2008-7-10 11:44:00' union all
select 8,10,5,'2008-7-10 11:45:00' union all
select 9,10,5,'2008-7-10 11:45:00'
SELECT a.* FROM @s a
INNER JOIN
(SELECT TOP 3 UID,MAX(createDate) md
FROM @s WHERE fileID=10
GROUP BY uid ORDER BY md DESC
) b
ON a.uid=b.uid AND createDate = md
ORDER BY md DESC
--结果:
id fileID uid createDate
----------- ----------- ----------- --------------------------
8 10 5 2008-07-10 11:45:00.000
9 10 5 2008-07-10 11:45:00.000
7 10 3 2008-07-10 11:44:00.000
6 10 4 2008-07-10 11:41:00.000
declare @s table (id int,fileID int,uid int,createDate datetime)
insert into @s
select 1,10,1,'2008-7-3 11:41:00' union all
select 2,10,2,'2008-7-4 11:41:00' union all
select 3,11,1,'2008-7-5 11:41:00' union all
select 4,10,1,'2008-7-6 11:42:00' union all
select 5,10,2,'2008-7-10 11:41:00' union all
select 6,10,4,'2008-7-10 11:41:00' union all
select 7,10,3,'2008-7-10 11:44:00' union all
select 8,10,5,'2008-7-10 11:45:00' union all
select 9,10,5,'2008-7-10 11:45:00'
SELECT DISTINCT a.fileID,
b.uid,b.createDate
FROM @s AS a
CROSS APPLY
(
SELECT TOP 3 uid,MAX(createDate) createDate
FROM @s
WHERE fileID=10
GROUP BY uid
ORDER BY createDate
) AS b
WHERE a.fileID=10
ORDER BY b.createDate DESC/*
fileID uid createDate
----------- ----------- -----------------------
10 2 2008-07-10 11:41:00.000
10 4 2008-07-10 11:41:00.000
10 1 2008-07-06 11:42:00.000(3 行受影响)
*/
declare @s table (id int,fileID int,uid int,createDate datetime)
insert into @s
select 1,10,1,'2008-7-3 11:41:00' union all
select 2,10,2,'2008-7-4 11:41:00' union all
select 3,11,1,'2008-7-5 11:41:00' union all
select 4,10,1,'2008-7-6 11:42:00' union all
select 5,10,2,'2008-7-10 11:41:00' union all
select 6,10,4,'2008-7-10 11:41:00' union all
select 7,10,3,'2008-7-10 11:44:00' union all
select 8,10,5,'2008-7-10 11:45:00' union all
select 9,10,5,'2008-7-10 11:45:00'
SELECT DISTINCT a.fileID,
b.uid,b.createDate
FROM @s AS a
CROSS APPLY
(
SELECT TOP 3 uid,MAX(createDate) createDate
FROM @s
WHERE fileID=A.fileID
GROUP BY uid
ORDER BY createDate DESC
) AS b
WHERE a.fileID=10
ORDER BY b.createDate DESC/*
fileID uid createDate
----------- ----------- -----------------------
10 5 2008-07-10 11:45:00.000
10 3 2008-07-10 11:44:00.000
10 4 2008-07-10 11:41:00.000(3 行受影响)
*/
-- sql2000 写法
declare @s table (id int,fileID int,uid int,createDate datetime)
insert into @s
select 1,10,1,'2008-7-3 11:41:00' union all
select 2,10,2,'2008-7-4 11:41:00' union all
select 3,11,1,'2008-7-5 11:41:00' union all
select 4,10,1,'2008-7-6 11:42:00' union all
select 5,10,2,'2008-7-10 11:41:00' union all
select 6,10,4,'2008-7-10 11:41:00' union all
select 7,10,3,'2008-7-10 11:44:00' union all
select 8,10,5,'2008-7-10 11:45:00' union all
select 9,10,5,'2008-7-10 11:45:00'select top 3 * from @s a
where fileID = 10
and not exists (select * from @s b where b.fileID = a.fileID and a.uid = b.uid
and(b.createDate > a.createDate or(b.createDate = a.createDate and b.id > a.id)))
order by createDate desc
select top 3 max(id) as id,fileID,uid,max(createDate) as createDate
from @s
where fileID = 10
group by fileID,uid
order by createDate desc
from aaa
group by id
having count(id) = 1) and fileid=10 order by createDate
from aaa
group by id
having count(id) = 1) and fileid=10 order by createDate desc