我想要每个组的前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
您可以使用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
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句