如何在SQL Server中使用逐列分组，根据where子句获取每组的前1条记录

Question

我想要每组的前1条记录，组列为imei。如果用户有多个设备，它应该显示所有设备的最新记录。

SQL

SELECT TOP 1 
p.imei, DEVICEID, timestatmp, ph, do, temp 
FROM 
dbo.pond AS p
INNER JOIN 
customerdevices AS cd ON p.imei = cd.imei  
INNER JOIN
users AS u ON cd.CustomerId = u.CustomerId 
WHERE
u.username = 'bob'  
ORDER BY
timestatmp DESC

样本数据

imei            DEVICEID    timestatmp            ph     do     temp
-----------------------------------------------------------------------
869668021752476 1027    2020-03-16 04:33:00.617 7.86    5.04    22.52
869668021752476 1027    2020-03-16 04:03:00.957 7.88    4.99    22.59
869668021732924 1025    2020-03-16 03:57:01.130 7.39    5.40    21.85

预期输出：

imei            DEVICEID    timestatmp            ph     do     temp
-----------------------------------------------------------------------
869668021752476 1027    2020-03-16 04:33:00.617 7.86    5.04    22.52
869668021732924 1025    2020-03-16 03:57:01.130 7.39    5.40    21.85

Answer 1

您可以将TOP 1 WITH TIES与ROW_NUMBER结合使用，以坚持您当前的方法：

SELECT TOP 1 WITH TIES p.imei, DEVICEID, timestatmp, ph, do, temp
FROM dbo.pond AS p
INNER JOIN customerdevices AS cd
ON p.imei = cd.imei
INNER JOIN users AS u
ON cd.CustomerId = u.CustomerId
WHERE u.username = 'bob'
ORDER BY
ROW_NUMBER() OVER (PARTITION BY p.imei, DEVICEID ORDER BY timestatmp DESC);

这种技巧之所以有效，是因为对于每个imei和DEVICEID组，保留的所有记录都将具有值为1的行号，这只会出现在最新的记录中。

Answer 2

您可以使用ROW_NUMBER((函数

SELECT 
imei,
DEVICEID,
timestatmp,
ph,
do,
temp FROM
(
SELECT 
p.imei,
DEVICEID,
timestatmp,
ph,
do,
temp,
RN=ROW_NUMBER() OVER( PARTITION BY p.imei ORDER BY timestatmp DESC) 
FROM dbo.pond as p
inner JOIN customerdevices as cd ON p.imei = cd.imei  inner Join users
as u on cd.CustomerId = u.CustomerId where u.username='bob' )
T
Where T.RN=1 order by timestatmp desc