如何在SQL Server中使用last_value和group by以及count？

jk9hmnmh 于 2022-11-28 发布在 SQL Server

关注(0)|答案(2)|浏览(182)

我已表喜：

name    |   timeStamp   |   previousValue   |   newValue    
--------+---------------+-------------------+------------
Mark    |   13.12.2020  |       123         |   155     
Mark    |   12.12.2020  |       123         |    12     
Tom     |   14.12.2020  |       123         |   534     
Mark    |   12.12.2020  |       123         |    31     
Tom     |   11.12.2020  |       123         |    84     
Mark    |   19.12.2020  |       123         |    33     
Mark    |   17.12.2020  |       123         |    96     
John    |   22.12.2020  |       123         |    69     
John    |   19.12.2020  |       123         |    33

我想混合last_value、count（*）和group来得到这个结果：

name    |   count   |   lastValue   
--------+-----------+-------------
Mark    |       5   |        33
Tom     |       2   |       534
John    |       2   |        69

本部分：

select name, count(*) 
from table 
group by name

返回表：

name    |   count   
--------+---------
Mark    |       5   
Tom     |       2   
John    |       2

但我必须为每个名称添加最后一个值。
怎么做呢？
顺祝商祺！

sql-server

来源：https://stackoverflow.com/questions/66027931/how-to-use-last-value-with-group-by-with-count-in-sql-server

2条答案

按热度按时间

cs7cruho1#

LAST_VALUE是一个窗口函数，因此您需要首先获取该值，然后进行聚合：

WITH CTE AS(
    SELECT [name],
           [timeStamp], --This is a poor choice for a column's name. timestamp is a (deprecated) synonym of rowversion, and a rowversion is not a date and time value
           previousValue,
           newValue,
           LAST_VALUE(newValue) OVER (PARTITION BY [name] ORDER BY [timeStamp] ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lastValue
    FROM dbo.YourTable)
SELECT [Name],
       COUNT(*) AS [count],
       lastValue
FROM CTE
GROUP BY [Name],
         lastValue;

赞(0）回复(0）举报 2022-11-28

eimct9ow2#

我有一个可行的解决方案，但这里还有一个：

SELECT 
[name], COUNT([name]), [lastValue]
FROM (
    SELECT 
        [name], FIRST_VALUE([newValue]) OVER (PARTITION BY [name] ORDER BY TimeStamp DESC ROWS UNBOUNDED PRECEDING) AS [lastValue]
        FROM [table] 
    ) xyz GROUP BY [name], [lastValue]

好好保管！

赞(0）回复(0）举报 2022-11-28

我来回答

如何在SQL Server中使用last_value和group by以及count？

2条答案

相关问题

热门标签

最新问答