sql 获取某一时段中每一天中最大的时间的一条记录

 

SELECT *
FROM (
SELECT ROW_NUMBER() OVER(
PARTITION BY CONVERT(CHAR(10), DataTime, 120) ORDER BY DataTime DESC
) AS rid,
*
FROM Hour_4684807221950341358
) AS t
WHERE rid = 1

 

 

--沉降监测1

select * from (
SELECT * FROM
(SELECT *,ROW_NUMBER() OVER (PARTITION BY CONVERT(VARCHAR(10),DataTime,23),HFCodeID ORDER BY DataTime DESC) AS RN FROM Hour_4684807221950341358
where DataTime BETWEEN '2019-06-03 00:00:00' and '2019-06-04 23:00:00'
) AS A WHERE RN=1

UNION ALL
--沉降监测2
SELECT * FROM
(SELECT *,ROW_NUMBER() OVER (PARTITION BY CONVERT(VARCHAR(10),DataTime,23),HFCodeID ORDER BY DataTime DESC) AS RN FROM Hour_4964749819094058863
where DataTime BETWEEN '2019-06-03 00:00:00' and '2019-06-04 23:00:00'
) AS A WHERE RN=1
UNION ALL
--渗压监测
SELECT * FROM
(SELECT *,ROW_NUMBER() OVER (PARTITION BY CONVERT(VARCHAR(10),DataTime,23),HFCodeID ORDER BY DataTime DESC) AS RN FROM Hour_5157124432278248782
where DataTime BETWEEN '2019-06-03 00:00:00' and '2019-06-04 23:00:00'
) AS A WHERE RN=1
) xx order by DataTime asc

--沉降监测
SELECT * FROM
(SELECT *,ROW_NUMBER() OVER (PARTITION BY CONVERT(VARCHAR(10),DataTime,23),HFCodeID ORDER BY DataTime DESC) AS RN FROM Hour_4884202820057099879
where DataTime BETWEEN '2019-06-04 00:00:00' and '2019-06-04 23:00:00'
) AS A WHERE RN=1
UNION ALL
--渗压监测
SELECT * FROM
(SELECT *,ROW_NUMBER() OVER (PARTITION BY CONVERT(VARCHAR(10),DataTime,23),HFCodeID ORDER BY DataTime DESC) AS RN FROM Hour_4909605008448672773
where DataTime BETWEEN '2019-06-04 00:00:00' and '2019-06-04 23:00:00'
) AS A WHERE RN=1
UNION ALL
--倾斜监测
SELECT * FROM
(SELECT *,ROW_NUMBER() OVER (PARTITION BY CONVERT(VARCHAR(10),DataTime,23),HFCodeID ORDER BY DataTime DESC) AS RN FROM Hour_5030118834484935845
where DataTime BETWEEN '2019-06-04 00:00:00' and '2019-06-04 23:00:00'
) AS A WHERE RN=1

转载于:https://www.cnblogs.com/yelanggu/p/10968613.html