34,575
社区成员
发帖
与我相关
我的任务
分享
select *
from [表名] a
where not exists(select 1
from [表名] b
where b.StationID=a.StationID
and convert(varchar,b.AddTime,23)=convert(varchar,a.AddTime,23)
and b.vMax>a.vMax)
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([StationID] nvarchar(24),[vMax] decimal(18,7),[vMaxTime] int,[AddTime] DATETIME)
Insert #T
select N'T003',39,1403,N'2019-08-01 13:00' union all
select N'T006',37,1356,N'2019-08-01 14:00' union all
select N'T003',38.5,1348,N'2019-07-31 15:00'
Go
--测试数据结束
SELECT * FROM (
SELECT
ROW_NUMBER()OVER(PARTITION BY StationID,
CONVERT(VARCHAR(100), AddTime, 23) ORDER BY vMax DESC) rn,*
FROM
#T
)t WHERE t.rn=1
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([StationID] nvarchar(24),[vMax] decimal(18,7),[vMaxTime] int,[AddTime] DATETIME)
Insert #T
select N'T003',39,1403,N'2019-08-01 13:00' union all
select N'T006',37,1356,N'2019-08-01 14:00' union all
select N'T003',38.5,1348,N'2019-07-31 15:00'
Go
--测试数据结束
SELECT
StationID,
CONVERT(VARCHAR(100), AddTime, 23) AS AddDate,
MAX(vMax) AS vMax
FROM
#T
GROUP BY
StationID,
CONVERT(VARCHAR(100), AddTime, 23);