我想做一个sql,但它不工作。
我有一个这样的表:
访问数量|哪台计算机访问过|日期和时间
11 computer 1 2013-12-30 09:00:00
2 computer 2 2013-12-30 10:00:00
30 computer 2 2013-12-30 10:11:00
17 computer 3 2013-12-30 17:30:00
200 computer 4 2013-12-30 07:00:00
150 computer 1 2013-12-30 14:00:00
19 computer 1 2013-12-30 06:00:00
我想要得到的结果是这样的:在每台计算机中,最大访问次数是多少,时间是什么?上面表的SQL操作结果如下所示:
访问数量|哪台计算机访问过|日期和时间
30 computer 2 2013-12-30 10:11:00
17 computer 3 2013-12-30 17:30:00
200 computer 4 2013-12-30 07:00:00
150 computer 1 2013-12-30 14:00:00
计算机2在准确时间2013-12-30 10:00:00有最多30个访问........
问题是我如何在每个结果中显示准确的日期?有人帮助吗?
谢谢
我想你想知道每台计算机访问次数最多的日期?您可以在CTE中使用row_number。SQL小提琴
with CTE
as
(select
accesses,
computer,
dt,
row_number() over (partition by computer order by accesses desc) rn
from
table1
)
select
*
from
CTE
where
rn = 1
您可以通过使用子查询来做到这一点,在子查询中,您首先限制要查找的值,然后将它们连接回主表。我根据你的信息做了一个样本。这适用于SQL 2005,并且允许与特定日期/时间一起返回的最大访问次数,而不是将最大访问次数与最大日期结合起来,这可能并不总是正确的。
CREATE TABLE #Tmp(QoA INT NOT NULL,
Computer VARCHAR(30) NOT NULL,
AccDt DATETIME NOT NULL)
Insert into #Tmp values (11,'computer 1','2013-12-30 09:00:00')
Insert into #Tmp values (2,'computer 2','2013-12-30 10:00:00')
Insert into #Tmp values (30,'computer 2','2013-12-30 10:11:00')
Insert into #Tmp values (17,'computer 3','2013-12-30 17:30:00')
Insert into #Tmp values (200,'computer 4','2013-12-30 07:00:00')
Insert into #Tmp values (150,'computer 1','2013-12-30 14:00:00')
Insert into #Tmp values (19,'computer 1','2013-12-30 06:00:00')
Select * From #Tmp
Select m.*
from #Tmp m
Join (Select Max(QoA) 'QoA', Computer
from #Tmp
Group by Computer) l
on m.qoa = l.qoa and m.computer = l.computer
Drop table #Tmp
搜索结果QoA Computer AccDt
200计算机4 2013-12-30 07:00:00.000
17电脑3 2013-12-30 17:30:00.000
30电脑2 2013-12-30 10:11:00.000
150计算机1 2013-12-30 14:00:00.000
像这样
with MySortedData as
(
select
ROW_NUMBER() over(partition by computer order by quantity_of_access desc) as RowNum
, quantity_of_access
, computer
, datetimecolumn
from
mytable
)
select *
from MySortedData
where RowNum = 1
不太清楚您要查找的是哪个日期,但似乎您正在查找最大日期,按计算机分组并查找最大结果。
select
max(quantity_of_access),
computer,
max(datetimecolumn)
from
mytable
group by
computer