得到每天的多个条目只有最后一排TSQL
我有一个表,是这样的:得到每天的多个条目只有最后一排TSQL
Id Name EnteredOn Percentage
`````````````````````````````````````````````````````````````
01 person1 2011-03-09 17:29:35.683 56.29
02 person1 2011-03-09 17:29:35.731 76.29
03 person1 2011-03-09 18:15:78.683 56.29
04 person1 2011-03-10 17:29:35.683 56.29
05 person1 2011-03-10 16:29:31.683 56.29
06 person1 2011-03-11 17:29:35.683 56.29
总结上面的表格,有行为天和行一天。
现在,我只想选择最新的行--单排 - 每天。
(一行9,一个用于10和一个用于11)
我不能因为时间戳的使用是不同的。我不能组和使用:
CAST(CONVERT(FLOAT, EnteredOn) AS INT)
因为当我选择EnteredOn字段,它抱怨说,它没有分组。我不能合并distinct(cast..date...)
,因为我不能得到正确的语法。
我该如何选择 - 只有姓名,输入的百分比字段与每天不同?
非常感谢提前。
;with cte as
(
select
*,
row_number() over(partition by datediff(d, 0, EnteredOn) order by EnteredOn desc) as rn
from YourTable
)
select *
from cte
where rn = 1
1行/天:
SELECT t1.Name, t1.EnteredOn, t1.Percentage
FROM table t1
JOIN (SELECT MAX(EnteredOn) Max_EnteredOn_By_Day
FROM table
GROUP BY convert(varchar, EnteredOn, 112)) t2
ON t1.EnteredOn = t2.Max_EnteredOn_By_Day
1行/人/天:
SELECT t1.Name, t1.EnteredOn, t1.Percentage
FROM table t1
JOIN (SELECT Name, MAX(EnteredOn) Max_EnteredOn_By_Day
FROM table
GROUP BY Name, convert(varchar, EnteredOn, 112)) t2
ON t1.Name = t2.Name
AND t1.EnteredOn = t2.Max_EnteredOn_By_Day
嗨@曼吉,嘘ldn't''group by clause''也包含''Id''吗? – iamserious 2011-03-24 19:43:09
你是对的,Id与你需要的无关 – manji 2011-03-24 19:51:33
非常感谢你帮助我:-) – iamserious 2011-03-25 11:28:49
SELECT Name, EnteredOn, Percentage
FROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY CONVERT(VARCHAR(8),EnteredOn,112) ORDER BY EnteredOn DESC) Corr
FROM YourTable) A
WHERE Corr = 1
我建议一个技巧在这里:
select top 1 with ties
Name, EnteredOn, Percentage
from YourTable
order by row_number() over(partition by datediff(d, 0, EnteredOn) order by Name, EnteredOn desc)
嗨,这个工作,但是,我不明白它的大部分,查询在做什么? esp''partition'' - 这是做什么的?这个查询在大量的行上减慢了吗?也谢谢 – iamserious 2011-03-25 10:43:57
,这个只考虑日期,以前提不起来,我也想过滤人。 – iamserious 2011-03-25 10:45:26
添加到最后的评论''选择一个特定的人每天一行和特定的百分比'' - 这样的事情.. – iamserious 2011-03-25 10:46:11