下面的情况用SQL如何写???
原始数据
列1 列2 列3 列4
1463010000 20060601 1090400400
1463010001 20060602 2000681100
1463010004 39915 20060606 1000681300
1463010004 39915 20060606 1090400400
1463010004 39915 20060606 1000732600 结果数据
列1 列2 列3 列4
1463010000 0 20060601 1090400400
1463010001 0 20060602 2000681100
1463010004 39915 20060606 1000681300
1463010004 0 20060606 1090400400
1463010004 0 20060606 1000732600
原始数据
列1 列2 列3 列4
1463010000 20060601 1090400400
1463010001 20060602 2000681100
1463010004 39915 20060606 1000681300
1463010004 39915 20060606 1090400400
1463010004 39915 20060606 1000732600 结果数据
列1 列2 列3 列4
1463010000 0 20060601 1090400400
1463010001 0 20060602 2000681100
1463010004 39915 20060606 1000681300
1463010004 0 20060606 1090400400
1463010004 0 20060606 1000732600
case when 列2 is null or 列2='' then 0 else 列2 end as 列2,
case when 列3 is null or 列3='' then 0 else 列3 end as 列3,
case when 列4 is null or 列4='' then 0 else 列4 end as 列4,
from 表
--> 测试数据: @s
declare @s table (列1 int,列2 int,列3 datetime,列4 int)
insert into @s
select 1463010000,null,'20060601',1090400400 union all
select 1463010001,null,'20060602',2000681100 union all
select 1463010004,39915,'20060606',1000681300 union all
select 1463010004,39915,'20060606',1090400400 union all
select 1463010004,39915,'20060606',1000732600
select 列1,列2=case when not exists(select 1 from @s where 列1=a.列1 and 列4<a.列4) then isnull(列2,0) else 0 end,
列3,列4
from @s a
--结果:列1 列2 列3 列4
----------- ----------- ------------------------------------------------------ -----------
1463010000 0 2006-06-01 00:00:00.000 1090400400
1463010001 0 2006-06-02 00:00:00.000 2000681100
1463010004 39915 2006-06-06 00:00:00.000 1000681300
1463010004 0 2006-06-06 00:00:00.000 1090400400
1463010004 0 2006-06-06 00:00:00.000 1000732600
INSERT @a SELECT '1463010000',NULL, '20060601', '1090400400'
UNION ALL SELECT '1463010001', null ,'20060602' ,'2000681100'
UNION ALL SELECT '1463010004', '39915' ,'20060606' ,'1000681300'
UNION ALL SELECT '1463010004', '39915' ,'20060606' ,'1090400400'
UNION ALL SELECT '1463010004', '39915' ,'20060606' ,'1000732600'
SELECT 列1,
列2=(case when 列4= (select min(列4) from @a WHERE 列1=a.列1 and 列2=a.列2 and 列3=a.列3) then 列2 else 0 end ),
列3,
列4
FROM @a a--result
/*
列1 列2 列3 列4
-------------------- ----------- -------------------- --------------------
1463010000 0 20060601 1090400400
1463010001 0 20060602 2000681100
1463010004 39915 20060606 1000681300
1463010004 0 20060606 1090400400
1463010004 0 20060606 1000732600(所影响的行数为 5 行)
*/
declare @s table (列1 int,列2 int,列3 datetime,列4 int)
insert into @s
select 1463010000,null,'20060601',1090400400 union all
select 1463010001,null,'20060602',2000681100 union all
select 1463010004,39915,'20060606',1000681300 union all
select 1463010004,39915,'20060606',1090400400 union all
select 1463010004,39915,'20060606',1000732600
select * from @s
select 列1,
isnull((case when exists(select 1 from @s where 列2 = t.列2 and 列4 < t.列4) then 0 else 列2 end),0)as 列2,列3,列4
from @s t
原始数据
列1 列2 列3 列4
1463010000 20060601 1090400400
1463010001 20060602 2000681100
1463010004 39915 20060606 1000681300
1463010004 39915 20060606 1090400400
1463010004 39915 20060606 1000732600 结果数据
列1 列2 列3 列4
1463010000 0 20060601 1090400400
1463010001 0 20060602 2000681100
1463010004 39915 20060606 1000681300
1463010004 0 20060606 1090400400
1463010004 0 20060606 1000732600
你要作的就是把select语句中表名改一下即可达到要求
goif object_id ('dbo.aa') is not null
drop table dbo.aa
gocreate table aa
(
列1 varchar(100),
列2 varchar(100),
列3 varchar(100),
列4 varchar(100)
)
insert into aa (列1,列3,列4) values (1463010000,20060601,1090400400)
insert into aa (列1,列2,列3,列4) values (1463010001,'',20060602,2000681100)
insert into aa (列1,列2,列3,列4) values (1463010004,39915,20060606,1000681300)
insert into aa (列1,列2,列3,列4) values (1463010004,39915,20060606,1000681300)
insert into aa (列1,列2,列3,列4) values (1463010004,39915,20060606,1000681300)
insert into aa (列1,列2,列3,列4) values (1234455667,'',32134,30090101)declare @col1 varchar(100),@col2 varchar(100),@col3 varchar(100),@col4 varchar(100)declare @tcol1 varchar(100),@tcol2 varchar(100),@tcol3 varchar(100),@tcol4 varchar(100)declare @t table (c1 varchar(100),c2 varchar(100),c3 varchar(100),c4 varchar(100))declare @count int
set @count=0declare tmp cursor fast_forward for select 列1,case when 列2 is null or 列2='' then '0' else 列2 end as 列2,列3,列4 from aa
open tmp
fetch next from tmp into @col1,@col2,@col3,@col4
while(@@fetch_status=0)
begin
set @count=@count+1 if(@count=1)
begin
select @tcol1=@col1,@tcol2=@col2,@tcol3=@col3,@tcol4=@col4
insert into @t (c1,c2,c3,c4) values (@tcol1,@tcol2,@tcol3,@tcol4)
end
else if(@count>1)
begin
if(@tcol1=@col1 and @tcol2=@col2 and @tcol3=@col3)
begin
insert into @t (c1,c2,c3,c4) values (@col1,'0',@col3,@col4)
end
else
begin
insert into @t (c1,c2,c3,c4) values (@col1,@col2,@col3,@col4)
select @tcol1=@col1,@tcol2=@col2,@tcol3=@col3,@tcol4=@col4
end
end
fetch next from tmp into @col1,@col2,@col3,@col4
end
close tmp
deallocate tmpselect * from @t
select distinct a.列1,a.列2,a.列3,a.列4 from aa a left join (select 列1,列2,列3 from aa) b
on (a.列1=b.列1 and a.列2=b.列2 and a.列3=b.列3)