首页 文章

每天只获得TSQL中的多个条目的最后一行

提问于
浏览 次
7

我有一张 table ，类似于：

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


总结上表，日 **09** 有 **three** 行，日 **10** 有 **two** 行 . 

现在，我只想选择最新一行 - 每天 **one single row**  . 
（一行为9，一行为10，一行为11）

由于时间戳，我不能使用distinct . 我不能分组并使用：

```java
CAST(CONVERT(FLOAT, EnteredOn) AS INT)

因为当我选择EnteredOn字段时，它会抱怨它没有分组 . 我无法合并 distinct(cast..date...) 因为我无法获得正确的语法 .

我如何选择 - 只有 Name, EnteredOn, Percentage 字段与每天不同？

提前谢谢了 .

sql-server tsql sql-server-2008

4 回答

  • 8
    ;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
    回复于
  • 6

    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
    回复于
  • 14
    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
    回复于
  • 2

    我建议再说一遍：

    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)
    回复于

相关问题