d1 a1 d2 a2 d3 a3
-------------------------------------------------------------------------
2009-01-23 8 NULL NULL NULL NULL
NULL NULL 2009-07-20 4 2009-07-20 3
NULL NULL 2009-07-25 1 2009-07-25 1
NULL NULL 2009-07-26 9 NULL NULL
NULL NULL NULL NULL 2009-07-23 8
d1 d2 d3 肯定不会同时为NULL的,谢谢
-------------------------------------------------------------------------
想要的结果是
d a1 a2 a3
-------------------------------------------------------------------------
2009-01-23 8 0 0
2009-07-20 0 4 3
2009-07-25 0 1 1
2009-07-26 0 9 0
2009-07-23 0 0 8
-------------------------------------------------------------------------
2009-01-23 8 NULL NULL NULL NULL
NULL NULL 2009-07-20 4 2009-07-20 3
NULL NULL 2009-07-25 1 2009-07-25 1
NULL NULL 2009-07-26 9 NULL NULL
NULL NULL NULL NULL 2009-07-23 8
d1 d2 d3 肯定不会同时为NULL的,谢谢
-------------------------------------------------------------------------
想要的结果是
d a1 a2 a3
-------------------------------------------------------------------------
2009-01-23 8 0 0
2009-07-20 0 4 3
2009-07-25 0 1 1
2009-07-26 0 9 0
2009-07-23 0 0 8
a1,a2,a3
from 一个表
--> 测试时间:2009-12-14 23:18:17--> 我的淘宝:《戒色坊》http://shop36766744.taobao.com/if object_id('[TB]') is not null drop table [TB]
create table [TB]([d1] datetime,[a1] int,[d2] datetime,[a2] int,[d3] datetime,[a3] int)
insert [TB]
select '2009-01-23',8,null,null,null,null union all
select null,null,'2009-07-20',4,'2009-07-20',3 union all
select null,null,'2009-07-25',1,'2009-07-25',1 union all
select null,null,'2009-07-26',9,null,null union all
select null,null,null,null,'2009-07-23',8select d=COALESCE(d1,d2,d3),
a1=isnull(a1,0),
a2=isnull(a2,0),
a3=isnull(a3,0)
from [TB]
/*d a1 a2 a3
------------------------------------------------------ ----------- ----------- -----------
2009-01-23 00:00:00.000 8 0 0
2009-07-20 00:00:00.000 0 4 3
2009-07-25 00:00:00.000 0 1 1
2009-07-26 00:00:00.000 0 9 0
2009-07-23 00:00:00.000 0 0 8(所影响的行数为 5 行)*/drop table [TB]
-------------------------------------------------------------------------
2009-01-23 8 NULL NULL NULL NULL
NULL NULL 2009-07-20 4 2009-07-20 3
NULL NULL 2009-07-25 1 2009-07-25 1
NULL NULL 2009-07-26 9 NULL NULL
NULL NULL NULL NULL 2009-07-23 8
d1 d2 d3 肯定不会同时为NULL的,谢谢
-------------------------------------------------------------------------
想要的结果是
d a1 a2 a3
-------------------------------------------------------------------------
2009-01-23 8 0 0
2009-07-20 0 4 3
2009-07-25 0 1 1
2009-07-26 0 9 0
2009-07-23 0 0 8
isnull(a1,0) as a1,
isnull(a2,0) as a2,
isnull(a3,0) as a3
from 一个表
COALESCE(D1,D2,D3) as D,
isnull(a1,0) as A1,
isnull(a2,0) as A2,
isnull(a3,0) as A3
from
tname
go
create table [tb]([d1] datetime,[a1] int,[d2] datetime,[a2] int,[d3] datetime,[a3] int)
insert [tb]
select '2009-01-23',8,null,null,null,null union all
select null,null,'2009-07-20',4,'2009-07-20',3 union all
select null,null,'2009-07-25',1,'2009-07-25',1 union all
select null,null,'2009-07-26',9,null,null union all
select null,null,null,null,'2009-07-23',8select d1,max(a1)a1,max(a2)a2,max(a3)a3
from(
select d1,a1,0 as a2,0 as a3 from tb
union all
select d2,0,a2,0 from tb
union all
select d3,0,0,a3 from tb) t
where d1 is not null
group by d1
--测试结果:
/*
d1 a1 a2 a3
------------------------------------------------------ ----------- ----------- -----------
2009-01-23 00:00:00.000 8 0 0
2009-07-20 00:00:00.000 0 4 3
2009-07-23 00:00:00.000 0 0 8
2009-07-25 00:00:00.000 0 1 1
2009-07-26 00:00:00.000 0 9 0(所影响的行数为 5 行)*/
insert into tb values('2009-01-23',8 , NULL , NULL,NULL , NULL )
insert into tb values(NULL ,NULL, '2009-07-20', 4 ,'2009-07-20', 3 )
insert into tb values(NULL ,NULL, '2009-07-25', 1 ,'2009-07-25', 1 )
insert into tb values(NULL ,NULL, '2009-07-26', 9 ,NULL , NULL)
insert into tb values(NULL ,NULL, NULL , NULL,'2009-07-23', 8 )
goselect distinct * from
(
select d1 , case when a1 is not null then a1 else 0 end a1, case when a2 is not null then a2 else 0 end a2, case when a3 is not null then a3 else 0 end a3 from tb where d1 is not null
union all
select d2 , case when a1 is not null then a1 else 0 end a1, case when a2 is not null then a2 else 0 end a2, case when a3 is not null then a3 else 0 end a3 from tb where d2 is not null
union all
select d3 , case when a1 is not null then a1 else 0 end a1, case when a2 is not null then a2 else 0 end a2, case when a3 is not null then a3 else 0 end a3 from tb where d3 is not null
) t
order by d1drop table tb/*
d1 a1 a2 a3
---------- ----------- ----------- -----------
2009-01-23 8 0 0
2009-07-20 0 4 3
2009-07-23 0 0 8
2009-07-25 0 1 1
2009-07-26 0 9 0(所影响的行数为 5 行)
*/
COALESCE(D1,D2,D3) as D,
isnull(a1,0) as A1,
isnull(a2,0) as A2,
isnull(a3,0) as A3
from
tname