A B1 B2 B3
OPEL 2006 4 3
OPEL 2006 4 5
OPEL 2006 4 7
OPEL 2006 4 12
OPEL 2006 6 3
VW 2006 7 7
BENZ 2006 12 11
......
结果:
OPEL 2006 6 3
VW 2006 7 7
BENZ 2006 12 11
......
A列取相同的,比如5个OPEL选一个,B1+B2+B3形成一个时间,这个取最近的(2006-6-3)
这个语句怎么写呀?
OPEL 2006 4 3
OPEL 2006 4 5
OPEL 2006 4 7
OPEL 2006 4 12
OPEL 2006 6 3
VW 2006 7 7
BENZ 2006 12 11
......
结果:
OPEL 2006 6 3
VW 2006 7 7
BENZ 2006 12 11
......
A列取相同的,比如5个OPEL选一个,B1+B2+B3形成一个时间,这个取最近的(2006-6-3)
这个语句怎么写呀?
A=a.A and convert(datetime,'''+rtrim(B1)+'-'+rtrim(B2)+'-'+rtrim(B3))
>convert(datetime,'''+rtrim(a.B1)+'-'+rtrim(a.B2)+'-'+rtrim(a.B3)))
declare @t table(A varchar(10),B1 int,B2 int,B3 int)
insert @t
select 'OPEL', 2006, 4, 3 union all
select 'OPEL', 2006, 4, 5 union all
select 'OPEL', 2006, 4, 7 union all
select 'OPEL', 2006, 4, 12 union all
select 'OPEL', 2006, 6, 3 union all
select 'VW', 2006, 7, 7 union all
select 'BENZ', 2006, 12, 11 select * from @t as t
where not exists(select 1 from @t where A = t.A and
cast(rtrim(B1) + '-' + rtrim(B2) + '-' + rtrim(B3) as datetime) >
cast(rtrim(t.B1) + '-' + rtrim(t.B2) + '-' + rtrim(t.B3) as datetime))/*结果
A B1 B2 B3
--------------------------------------------
OPEL 2006 6 3
VW 2006 7 7
BENZ 2006 12 11
*/
go
insert into test
select 'a','2006','1','1'
union all
select 'a','2006','2','1'
union all
select 'a','2006','3','1'
union all
select 'b','2006','1','1'
union all
select 'b','2006','6','1'
union all
select 'b','2006','4','4'
union all
select 'c','2005','1','1'
union all
select 'c','2006','1','1'
union all
select 'd','2006','1','1'
goselect a,b1,b2,b3 from test t where b1+b2+b3<=(select min(b1+b2+b3) from test where a=t.a)