34,594
社区成员
发帖
与我相关
我的任务
分享
declare @t table(name varchar(10),date datetime,value int)
insert into @t select 'aaa','2012-01-01',1
union all select 'bbb','2012-01-01',1
union all select 'aaa','2012-01-02',1
union all select 'bbb','2012-01-02',1
union all select 'ccc','2012-01-02',1
union all select 'aaa','2012-01-03',1
union all select 'bbb','2012-01-03',1
union all select 'aaa','2012-01-04',1
union all select 'bbb','2012-01-04',1
;with t1 as (select name from @t group by name),
t2 as (select date from @t group by date),
t3 as (select t1.name,t2.date from t1 ,t2 )
select *,value=(case
when (select value from @t where name=t3.name and date=t3.date)is not null
then (select value from @t where name=t3.name and date=t3.date) else null end)
from t3 order by date
/*结果
name date value
aaa 2012-01-01 00:00:00.000 1
bbb 2012-01-01 00:00:00.000 1
ccc 2012-01-01 00:00:00.000 NULL
aaa 2012-01-02 00:00:00.000 1
bbb 2012-01-02 00:00:00.000 1
ccc 2012-01-02 00:00:00.000 1
aaa 2012-01-03 00:00:00.000 1
bbb 2012-01-03 00:00:00.000 1
ccc 2012-01-03 00:00:00.000 NULL
aaa 2012-01-04 00:00:00.000 1
bbb 2012-01-04 00:00:00.000 1
ccc 2012-01-04 00:00:00.000 NULL
*/
create table t1(name varchar(10),[date] datetime,value int)
insert t1
select 'aaa', '2012-01-01', 1 union all
select 'bbb', '2012-01-01', 2 union all
select 'aaa', '2012-01-02', 3 union all
select 'bbb', '2012-01-02', 4 union all
select 'ccc', '2012-01-02', 5 union all
select 'aaa', '2012-01-03', 6 union all
select 'bbb', '2012-01-03', 7 union all
select 'aaa', '2012-01-04', 8 union all
select 'bbb', '2012-01-04', 9
go
;with cte as(
select name='aaa',[date],vaule=null from t1
union all
select name='bbb',[date],vaule=null from t1
union all
select name='ccc',[date],vaule=null from t1
union all
select * from t1
)
select name,[date],value=MAX(vaule) from cte a
group by name,[date]
/*
name date vaule
--- --- ----
aaa 2012-01-01 00:00:00.000 1
bbb 2012-01-01 00:00:00.000 2
ccc 2012-01-01 00:00:00.000 NULL
aaa 2012-01-02 00:00:00.000 3
bbb 2012-01-02 00:00:00.000 4
ccc 2012-01-02 00:00:00.000 5
aaa 2012-01-03 00:00:00.000 6
bbb 2012-01-03 00:00:00.000 7
ccc 2012-01-03 00:00:00.000 NULL
aaa 2012-01-04 00:00:00.000 8
bbb 2012-01-04 00:00:00.000 9
ccc 2012-01-04 00:00:00.000 NULL
*/
go
drop table t1
select a.* from
(select date from a group by date) tb1
left join a on a.date=tb1.date
declare @start datetime
declare @end datetime
set @start = '2012-01-01'
set @end = '2012-01-04'
;with cte as
(
select dateadd(dd,number,@start) as date
from master..spt_values
where [type] = 'p' and number between 0 and datediff(dd,@start,@end)
),ach as
(
select a.date,b.name
from cte a cross join (select [name] from tb group by [name]) b
)
select convert(varchar(10),a.date,120) date,a.[name],b.[value]
from ach a left join tb b
on convert(varchar(10),a.date,120) = convert(varchar(10),b.date,120)
and a.[name] = b.[name]