id code time
1 C1 2012-01-01
2 C2 2012-02-05
3 C1 2012-01-02
4 C2 2012-02-06
5 C1 2012-01-03
6 C2 2012-02-07
根据code分组,得到最大time及最小time对应的id
结果(粗体部分):
code 最大时间对应的id 最小时间对应的id
C1 5(2012-01-03) 1(2012-01-01)
C2 6(2012-02-07) 2(2012-02-05)
1 C1 2012-01-01
2 C2 2012-02-05
3 C1 2012-01-02
4 C2 2012-02-06
5 C1 2012-01-03
6 C2 2012-02-07
根据code分组,得到最大time及最小time对应的id
结果(粗体部分):
code 最大时间对应的id 最小时间对应的id
C1 5(2012-01-03) 1(2012-01-01)
C2 6(2012-02-07) 2(2012-02-05)
with tb(id,code,time) as (
select 1,'c1','2012-01-01' union
select 2,'c2','2012-02-05' union
select 3,'c1','2012-01-02' union
select 4,'c2','2012-02-06' union
select 5,'c1','2012-01-03' union
select 6,'c2','2012-02-07'
)
select a.code,b.id 最大,c.id 最小 from (select code,max(time) maxa,min(time) mina from tb group by code) a
left join tb b on a.code=b.code and maxa=b.time
left join tb c on a.code=c.code and mina=c.time
GO
---->生成表tb
--
--if object_id('tb') is not null
-- drop table tb
--Go
--Create table tb([id] smallint,[code] nvarchar(2),[time] datetime)
--Insert into tb
--Select 1,N'C1','2012-01-01'
--Union all Select 2,N'C2','2012-02-05'
--Union all Select 3,N'C1','2012-01-02'
--Union all Select 4,N'C2','2012-02-06'
--Union all Select 5,N'C1','2012-01-03'
--Union all Select 6,N'C2','2012-02-07'
select
code
,(SELECT id FROM tb AS x WHERE x.code=a.code AND time=MAX(a.time)) AS 最大时间对应的id
,(SELECT id FROM tb AS x WHERE x.code=a.code AND time=MIN(a.time)) AS 最大时间对应的id
from tb AS a
GROUP BY code/*
code 最大时间对应的id 最大时间对应的id
---- --------- ---------
C1 5 1
C2 6 2
*/
select
code
,(SELECT id FROM tb AS x WHERE x.code=a.code AND time=MAX(a.time)) AS 最大时间对应的id
,(SELECT id FROM tb AS x WHERE x.code=a.code AND time=MIN(a.time)) AS 最小时间对应的id
from tb AS a
GROUP BY code/*
code 最大时间对应的id 最小时间对应的id
---- --------- ---------
C1 5 1
C2 6 2
*/