create table table1(tno varchar(50),canliang int,tdate datetime)
insert into table1
select '01' , 12 , '2005-04-01'
union all select '01' , 44 , '2005-04-05'
union all select '01', 18 , '2005-04-11'
union all select '02', 12 , '2005-04-17'
union all select '02', 32 , '2005-04-18'
union all select '04', 12 , '2005-04-20'declare @date varchar(50)
set @date='200504'declare @days int
set @days=day(dateadd(month,1,cast(stuff(@date,5,0,'/')+'/01' as datetime))-1)
create table #table1(tid int identity(1,1),tno varchar(50),canliang int,tdate datetime)
insert into #table1(tno,canliang,tdate)
exec ('select top ' + @days + ' ''01'',null,null from sysobjects,syscolumns')
update #table1 set tdate=cast(stuff(@date,5,0,'/')+'/01' as datetime)+tid-1update b set tno=a.tno,canliang=a.canliang
from table1 a,#table1 b where a.tdate=b.tdatedeclare @s1 nvarchar(4000)
select @s1=''
select @s1=@s1+','+quotename(tno)+'=isnull(sum(case when tno='''+tno+''' then canliang end),0)' from #table1 group by tno
select @s1='select tdate' + @s1 + ' from #table1 where convert(varchar(6),tdate,112)=''' +@date + ''' group by tdate'
exec(@s1)drop table table1,#table1
insert into table1
select '01' , 12 , '2005-04-01'
union all select '01' , 44 , '2005-04-05'
union all select '01', 18 , '2005-04-11'
union all select '02', 12 , '2005-04-17'
union all select '02', 32 , '2005-04-18'
union all select '04', 12 , '2005-04-20'declare @date varchar(50)
set @date='200504'declare @days int
set @days=day(dateadd(month,1,cast(stuff(@date,5,0,'/')+'/01' as datetime))-1)
create table #table1(tid int identity(1,1),tno varchar(50),canliang int,tdate datetime)
insert into #table1(tno,canliang,tdate)
exec ('select top ' + @days + ' ''01'',null,null from sysobjects,syscolumns')
update #table1 set tdate=cast(stuff(@date,5,0,'/')+'/01' as datetime)+tid-1update b set tno=a.tno,canliang=a.canliang
from table1 a,#table1 b where a.tdate=b.tdatedeclare @s1 nvarchar(4000)
select @s1=''
select @s1=@s1+','+quotename(tno)+'=isnull(sum(case when tno='''+tno+''' then canliang end),0)' from #table1 group by tno
select @s1='select tdate' + @s1 + ' from #table1 where convert(varchar(6),tdate,112)=''' +@date + ''' group by tdate'
exec(@s1)drop table table1,#table1
表table1
字段1(人员编号) 字段2(产量) 字段3(日期)
01 12 2005-04-01
01 44 2005-04-05
01 18 2005-04-11
02 12 2005-04-17
02 32 2005-04-18
04 12 2005-04-20第1步:当我使用select distinct(人员编号) from table1 where 日期是2005年04月这个范围,这样我就获取了一个人员的记录集。是:
01
02
04
这样的三行(根据实际情况每个月可能有不同人员和数量。当前4月份的只有01、02、04)
第2步:根据这个动态的记录集,生成类似下面的记录集:2005-04-01 01 02 04
2005-04-02
2005-04-03
……
……
2005-04-29
2005-04-30
核心问题是我怎么利用第1步中得到的人员记录集作为查询条件,查询所有人员的日产量。
insert into table1
select '01' , 12 , '2005-04-01'
union all select '01' , 44 , '2005-04-05'
union all select '01', 18 , '2005-04-11'
union all select '02', 12 , '2005-04-17'
union all select '02', 32 , '2005-04-18'
union all select '04', 12 , '2005-04-20'
union all select '07', 12 , '2005-05-20'
declare @date varchar(50)
set @date='200504'declare @days int
set @days=day(dateadd(month,1,cast(stuff(@date,5,0,'/')+'/01' as datetime))-1)
create table #table1(tid int identity(1,1),tdate datetime) --每月天數
insert into #table1(tdate)
exec ('select top ' + @days + ' null from sysobjects,syscolumns')
update #table1 set tdate=cast(stuff(@date,5,0,'/')+'/01' as datetime)+tid-1declare @s1 nvarchar(4000),@s2 varchar(500)
select @s1='',@s2=''
select @s2=@s2+',isnull(' + quotename(tno) +',0) as '+quotename(tno),@s1=@s1+','+quotename(tno)+'=isnull(sum(case when tno='''+tno+''' then canliang end),0)' from table1 where convert(varchar(6),tdate,112)=@date group by tno
select @s1='select tdate' + @s1 + ' from table1 where convert(varchar(6),tdate,112)=''' +@date + ''' group by tdate'
exec('select a.tdate' + @S2 +' from #table1 a left join ('+@s1+') b on a.tdate=b.tdate')drop table table1,#table1
谢谢
xiaomeixiang(Little Sheep)