现在有表t1,表中有年year,月month,值valueid fid year month value
1 1 2008 1 11
2 1 2008 2 12
3 1 2008 3 13
4 2 2008 1 14
5 2 2008 4 15
6 2 2009 5 16数据库:sql sever 2005
现在希望的结果是得到一个表t2,按year,month统一起来,如果某一个fid没有这个月或者年,就新增一个记录,要有这个年和月,然后value值为空 如下:
id fid year month value
1 1 2008 1 11
2 1 2008 2 12
3 1 2008 3 13
4 1 2008 4 null
5 1 2009 5 null
6 2 2008 1 14
7 2 2008 2 null
8 2 2008 3 null
9 2 2008 4 15
10 2 2009 5 16
1 1 2008 1 11
2 1 2008 2 12
3 1 2008 3 13
4 2 2008 1 14
5 2 2008 4 15
6 2 2009 5 16数据库:sql sever 2005
现在希望的结果是得到一个表t2,按year,month统一起来,如果某一个fid没有这个月或者年,就新增一个记录,要有这个年和月,然后value值为空 如下:
id fid year month value
1 1 2008 1 11
2 1 2008 2 12
3 1 2008 3 13
4 1 2008 4 null
5 1 2009 5 null
6 2 2008 1 14
7 2 2008 2 null
8 2 2008 3 null
9 2 2008 4 15
10 2 2009 5 16
go
create table [tb]([id] int,[fid] int,[year] int,[month] int,[value] int)
insert [tb]
select 1,1,2008,1,11 union all
select 2,1,2008,2,12 union all
select 3,1,2008,3,13 union all
select 4,2,2008,1,14 union all
select 5,2,2008,4,15 union all
select 6,2,2009,5,16select
id=identity(int,1,1),
t.*,
tb.value
into t2
from(
select * from
(select distinct [fid] from tb) a,
(select distinct [year],[month] from tb) b
) t
left join tb on t.fid=tb.fid and t.[year]=tb.[year] and t.[month]=tb.[month]
order by fid,[year],[month]select * from t2
--测试结果:
/*
id fid year month value
----------- ----------- ----------- ----------- -----------
1 1 2008 1 11
2 1 2008 2 12
3 1 2008 3 13
4 1 2008 4 NULL
5 1 2009 5 NULL
6 2 2008 1 14
7 2 2008 2 NULL
8 2 2008 3 NULL
9 2 2008 4 15
10 2 2009 5 16(10 行受影响)
*/drop table tb,t2
我现在是真实的表中有很多fid,查询的时候只给几个fid,然后根据这些fid的最早的时间和最晚的时间得到一个新表
因为如果每次都把整个表的所有数据都梳理一遍可能效率很低
这个过程应该怎么写
go
create table [tb]([id] int,[fid] int,[year] int,[month] int,[value] int)
insert [tb]
select 1,1,2008,1,11 union all
select 2,1,2008,2,12 union all
select 3,1,2008,3,13 union all
select 4,2,2008,1,14 union all
select 5,2,2008,4,15 union all
select 6,2,2009,5,16
go-->存储过程
create proc sp_test(@fid varchar(100))
as
begin
set @fid=''''+replace(@fid,',',''',''')+''''
if object_id('[t2]') is not null
drop table [t2]; --注意:这里会清空t2表里所有的数据
exec(
'select
id=identity(int,1,1),
t.*,
tb.value
into t2
from(
select * from
(select distinct [fid] from tb where fid in('+@fid+')) a,
(select distinct [year],[month] from tb where fid in('+@fid+')) b
) t
left join tb on t.fid=tb.fid and t.[year]=tb.[year] and t.[month]=tb.[month]
order by fid,[year],[month]')
end
go-->调用存储过程
exec sp_test '1,2'-->查询
select * from t2
--测试结果:
/*
id fid year month value
----------- ----------- ----------- ----------- -----------
1 1 2008 1 11
2 1 2008 2 12
3 1 2008 3 13
4 1 2008 4 NULL
5 1 2009 5 NULL
6 2 2008 1 14
7 2 2008 2 NULL
8 2 2008 3 NULL
9 2 2008 4 15
10 2 2009 5 16(10 行受影响)
*/drop proc sp_test;
drop table tb,t2;
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[fid] int,[year] int,[month] int,[value] int)
insert [tb]
select 1,1,2008,1,11 union all
select 2,1,2008,2,12 union all
select 3,1,2008,3,13 union all
select 4,2,2008,1,14 union all
select 5,2,2008,4,15 union all
select 6,2,2009,5,16潜水多日。被这句。深深的震撼。