34,593
社区成员
发帖
与我相关
我的任务
分享
;WITH CTE(autoID,devID,curTime,v1,v2) AS(
SELECT 1,'001','2014-10-10 10:12:35',3.5,3.8 UNION ALL
SELECT 2,'001','2014-10-10 10:15:35',3.2,3.9 UNION ALL
SELECT 3,'001','2014-10-12 11:11:35',2.5,3.0 UNION ALL
SELECT 4,'001','2014-10-12 10:12:35',3.5,3.9 UNION ALL
SELECT 5,'001','2014-10-15 18:12:35',3.5,3.9 UNION ALL
SELECT 6,'001','2014-10-15 18:12:39',1.2,2.5
)
SELECT *
FROM (
SELECT * ,ROW_NUMBER()OVER(PARTITION BY CONVERT(VARCHAR(13),curTime,120) ORDER BY curTime) AS RN
FROM CTE
) AS D
WHERE D.RN=1 AND D.devID='001' AND D.curTime BETWEEN '2014-10-10 00:00:00' AND '2014-10-15 23:59:59'
/*
1 001 2014-10-10 10:12:35 3.5 3.8 1
4 001 2014-10-12 10:12:35 3.5 3.9 1
3 001 2014-10-12 11:11:35 2.5 3.0 1
5 001 2014-10-15 18:12:35 3.5 3.9 1
*/