使用带有计数的SQL选择每个电池的最新记录

BatteryId   TimeStamp                    Temprature
1           2017-02-13 12:16:14.000      23
1           2016-02-13 12:13:14.000      21
1           2015-01-13 12:16:14.000      19
2           2017-02-11 12:16:14.000      22
2           2016-02-13 12:16:14.000      16
3           2017-02-13 11:16:14.000      12
3           2016-02-13 12:15:14.000      25

I have table with multiple records for each battery as above following sql query is returning latest record for each battery

我有每个电池的多个记录的表，如上面的sql查询返回每个电池的最新记录

SELECT * FROM (SELECT BatteryId, Timestamp, Temperature
ROW_NUMBER() OVER(PARTITION BY BatteryId ORDER BY timestamp DESC)
AS N FROM tblBattery) AS TT WHERE N = 1

如

BatteryId   TimeStamp                    Temprature
1           2017-02-13 12:16:14.000      23
2           2017-02-11 12:16:14.000      22
3           2017-02-13 11:16:14.000      12

How I can add Count for each BatteryId, Here is what I need

我如何为每个BatteryId添加Count，这就是我需要的

BatteryId   TimeStamp                    Temprature   Count
1           2017-02-13 12:16:14.000      23           3
2           2017-02-11 12:16:14.000      22           2 
3           2017-02-13 11:16:14.000      12           2

3 个解决方案

#1

Use the count window function.

使用计数窗口功能。

SELECT * FROM 
(SELECT BatteryId, Timestamp, Temperature,
 ROW_NUMBER() OVER(PARTITION BY BatteryId ORDER BY timestamp DESC) AS N,
 COUNT(*) OVER(PARTITION BY BatteryId) as Cnt
 FROM tblBattery) TT 
WHERE N = 1

#2

Hoping, i understood your problem correctly.

希望，我正确理解你的问题。

Please check if below query can help you.

请检查以下查询是否可以帮助您。

SELECT *
FROM
  (SELECT BatteryId,
    TIMESTAMP,
    Temperature ,  ROW_NUMBER() OVER(PARTITION BY BatteryId ORDER BY TIMESTAMP DESC) AS N ,
    COUNT(0) OVER(PARTITION BY BatteryId ) CNT
  FROM tblBattery
  ) AS TT
WHERE N = 1;

#3

Add a sub query before you perform the PARTITION BY

在执行PARTITION BY之前添加子查询

SELECT * 
FROM (SELECT 
         BatteryId
        ,Timestamp
        ,Temperature
        ,Count
        ,ROW_NUMBER() OVER(PARTITION BY BatteryId ORDER BY timestamp DESC) AS N
FROM (SELECT *, COUNT(BatteryId) As Count FROM tblBattery GROUP BY BatteryId)) AS TT WHERE N = 1

This should solve your issue.

这应该可以解决您的问题。

#1