各位高手看看，这个查询语句如何写，谢谢！

wangfeng002 2008-10-21 10:28:06
id fileID uid createDate
1 10 1 2008-7-3 11:41:00
2 10 2 2008-7-4 11:41:00
3 11 1 2008-7-5 11:41:00
4 10 1 2008-7-6 11:41:00
5 10 2 2008-7-10 11:41:00
假设有很多条记录。

查询要求：查询fileID=10的文件最近访问前3条记录并用户（uid）不可以重复。
各位高手看看，这个查询语句如何写，谢谢！
...全文
245 27 打赏 收藏 转发到动态 举报
写回复
用AI写文章
27 条回复
切换为时间正序
请发表友善的回复…
发表回复
xiaoliaoyun 2008-10-21
  • 打赏
  • 举报
 回复 


-- 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
liangCK 2008-10-21
  • 打赏
  • 举报
 回复 
加个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 行受影响)

*/
liangCK 2008-10-21
  • 打赏
  • 举报
 回复
哦..错了..
liangCK 2008-10-21
  • 打赏
  • 举报
 回复 
--> 测试数据: @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 行受影响)

*/
pt1314917 2008-10-21
  • 打赏
  • 举报
 回复
[Quote=引用 20 楼 fcuandy 的回复:]
这话就有点问题了，结果都取不对，要效率何用
[/Quote]



--偶像的结果似乎也有点问题，还是不能将用户去重。

--> 测试数据: @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
fcuandy 2008-10-21
  • 打赏
  • 举报
 回复
这话就有点问题了，结果都取不对，要效率何用
ziqing_1_2_3 2008-10-21
  • 打赏
  • 举报
 回复
[Quote=引用 17 楼 fcuandy 的回复:]
引用 16 楼 ziqing_1_2_3 的回复:
可以探讨一下 这两语句，哪个效率最高
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 create…



[/Quote]

sql语句，追求逻辑严密，也追求效率。
逻辑可以千变万化，，效率单位只有ms
时光瞄 2008-10-21
  • 打赏
  • 举报
 回复 
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练练笔
fcuandy 2008-10-21
  • 打赏
  • 举报
 回复
[Quote=引用 16 楼 ziqing_1_2_3 的回复:]
可以探讨一下 这两语句，哪个效率最高
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
[/Quote]

这有什么好探讨的，两条语句的功能都不一样。

我的是表中取原记录，下面这条是直接取聚合值，逻辑上千差万别。
ziqing_1_2_3 2008-10-21
  • 打赏
  • 举报
 回复
可以探讨一下 这两语句，哪个效率最高
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
handanyiying 2008-10-21
  • 打赏
  • 举报
 回复
select *from 表 where id in(select top 3 id from 表 where fileId=10 group by uid order by createDate desc)

对的啊～～～～～～～～～你们怎么不测试吗？？晕～～
wer123q 2008-10-21
  • 打赏
  • 举报
 回复 


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
ziqing_1_2_3 2008-10-21
  • 打赏
  • 举报
 回复
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
fcuandy 2008-10-21
  • 打赏
  • 举报
 回复 
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
wangfeng002 2008-10-21
  • 打赏
  • 举报
 回复
to:wufeng4552

fileID=10有好幾條記錄,它的前3條如何劃分?

按时间倒序，uid不可以重复，取出前3条。谢谢！
水族杰纶 2008-10-21
  • 打赏
  • 举报
 回复 
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

*/
liangCK 2008-10-21
  • 打赏
  • 举报
 回复
[Quote=引用 8 楼 liangCK 的回复:]
貌似错了..
[/Quote]

貌似是对的.
liangCK 2008-10-21
  • 打赏
  • 举报
 回复
貌似错了..
水族杰纶 2008-10-21
  • 打赏
  • 举报
 回复
[Quote=引用楼主 wangfeng002 的帖子:]
id fileID uid createDate
1 10 1 2008-7-3 11:41:00
2 10 2 2008-7-4 11:41:00
3 11 1 2008-7-5 11:41:00
4 10 1 2008-7-6 11:41:00
5 10 2 2008-7-10 11:41:00
假设有很多条记录。

查询要求：查询fileID=10的文件最近访问前3条记录并用户（uid）不可以重复。
各位高手看看，这个查询语句如何写，谢谢！
[/Quote]
fileID=10有好幾條記錄,它的前3條如何劃分?
liangCK 2008-10-21
  • 打赏
  • 举报
 回复 
--> 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 行受影响)





*/
加载更多回复（7）
生成数据库值为插入语句
在做数据库安装程序时，发现数据库中有许多初始值若要添加，但如果手动一条条生成插入语句太烦，就写了这个东东，用来将选择的表中的数据生成为插入语句。使用很简单，看看界面就行，使用的是sql。如果有什么错误，...
写出高性能SQL语句的35条方法
 如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系) 《不知道是干嘛用的,理解了半天还不知道，有理解的高手一起讨论一下啊》 （2）删除重复记录：  最高效的删除重复记录...
search语句的用法c语言,各位高手，search怎么用啊？我是新手，在这里多谢啦！！！...
被查找的对象集称为查找表，查找表是一个集合结构，常见的存储方法有顺序存储、树形存储和散列存储，存储方法不同，其查找效率也不同。本章内容是上述三种存储、查找的实现，及各种查找方法的平均查找长度。查...
bat的for语句精通
这篇文章转载了namejm的文章，详细讲解了批处理中for语句的用法，由简到繁，而且还讲解了和for语句最为紧密的变量延迟。读完这篇文章。for语句基本上就可以无忧了。好了废话不多说，上干货！   批处理For语句从入门...
【CMD】批处理 For 语句从入门到精通（完整版）_20101225
在批处理中，for是最为强大的命令语句，它的出现，使得解析文本内容、遍历文件路径、数值递增/...在这个系列教程中，我将通过实际应用中频繁出现的例子，带领大家步入for语句的神奇之门，一步步迈向for语句的魔幻殿堂。
疑难问题

22,298

社区成员

121,733

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章