SQL 这种查询时间最小的数据改怎么写！！

qq_39027319 2017-11-16 04:16:09

例如
DJBH SPDM RQ SL
001 S1 2016 -01-01 23
001 S1 2016 -01-02 14
001 S2 2016 -01-01 45
002 S1 2016 -01-01 1
002 S3 2016 -02-09 0
002 S3 2016 -07-12 90
003 S2 2017 -03-01 11
003 S4 2016 -07-11 13
003 S4 2016 -08-01 14

我要的结果是这样
DJBH SPDM RQ SL
001 S1 2016 -01-01 23
001 S2 2016 -01-01 45
002 S1 2016 -01-01 1
002 S3 2016 -02-09 0
003 S2 2017 -03-01 11
003 S4 2016 -07-11 13

也就是说同一个DJBH 同一个SPDM 取时间最小的数据

...全文

315 3 打赏收藏转发到动态举报

写回复

用AI写文章

3 条回复

切换为时间正序

请发表友善的回复…

发表回复

顺势而为1 2017-11-16

打赏
举报



if not object_id(N'Tempdb..#tab') is null
    drop table #tab
Go
Create table #tab(DJBH nvarchar(23),SPDM nvarchar(22),RQ Date,SL int)
Insert #tab
select N'001',N'S1','2016-01-01',23 union all
select N'001',N'S1','2016-01-02',14 union all
select N'001',N'S2','2016-01-01',45 union all
select N'002',N'S1','2016-01-01',1 union all
select N'002',N'S3','2016-02-09',0 union all
select N'002',N'S3','2016-07-12',90 union all
select N'003',N'S2','2017-03-01',11 union all
select N'003',N'S4','2016-07-11',13 union all
select N'003',N'S4','2016-08-01',14

Select * 
From #tab a
Where RQ=(Select min(rq) From #tab where SPDM=a.SPDM and djbh=a.DJBH)

吉普赛的歌 2017-11-16

打赏
举报

select
* 
from 
(
    select row_number() over(partition by djbh,spdm order by rq) as rid,* 
) as t
where t.rid=1

听雨停了 2017-11-16

打赏
举报





use Tempdb

go

--> --> 听雨停了-->测试数据

 

if not object_id(N'Tempdb..#tab') is null

	drop table #tab

Go

Create table #tab(DJBH nvarchar(23),SPDM nvarchar(22),RQ Date,SL int)

Insert #tab

select N'001',N'S1','2016-01-01',23 union all

select N'001',N'S1','2016-01-02',14 union all

select N'001',N'S2','2016-01-01',45 union all

select N'002',N'S1','2016-01-01',1 union all

select N'002',N'S3','2016-02-09',0 union all

select N'002',N'S3','2016-07-12',90 union all

select N'003',N'S2','2017-03-01',11 union all

select N'003',N'S4','2016-07-11',13 union all

select N'003',N'S4','2016-08-01',14

--测试数据结束



;WITH cte AS (

         SELECT *,

                ROW_NUMBER() OVER(PARTITION BY djbh, spdm ORDER BY rq) AS rn

         FROM   #tab

     )



SELECT DJBH,

       SPDM,

       RQ,

       SL

FROM   cte

WHERE  rn = 1

ORDER BY DJBH,

       SPDM