按时间汇总的查询语句怎么写？

declare @sql varchar(8000) set @sql='select 拉号,产品编号,产品名称' select @sql=@sql+',sum(case when convert(varchar(10),日期,101)='''+日期+''' then 日生产数量 else 0 end) ['+日期+']' from (select distinct 日期 from( select top 100 percent convert(varchar(10),日期,101) as 日期 from tb) tt) t exec (@sql+' from tb group by 拉号,产品编号,产品名称')

declare @sql varchar(8000) set @sql='select 拉号,产品编号,产品名称' select @sql=@sql+',['+convert(varchar(10),日期,120)+']=sum(case 日期 when '''+convert(varchar(10),日期,120)+''' then 日生产数量 else 0 end)' from (select distinct convert(varchar(10),日期,120) from 表名)a set @sql=@sql+' from 表名 group by 拉号,产品编号,产品名称' exec(@sql)

create table tb(拉号 varchar(5),产品编号 varchar(10),产品名称 varchar(10),日生产数量 int,日期 datetime) insert into tb select '01' , 'k1084' , 'A公仔' , 1000 , '2008/05/10' insert into tb select '01' , 'k1088' , 'B公仔' , 500 , '2008/05/11' insert into tb select '01' , 'k1085' , 'C公仔' , 5000 , '2008/05/12' insert into tb select '01' , 'k1085' , 'D公仔' , 10000 , '2008/05/13' insert into tb select '02' , 'k1087' , 'E公仔' , 6000 , '2008/05/10' insert into tb select '02' , 'k1088' , 'B公仔' , 3000 , '2008/05/11' insert into tb select '02' , 'k1089' , 'G公仔' , 9000 ,'2008/05/12' insert into tb select '02' , 'k1084' , 'A公仔' , 6000 ,'2008/05/13' go declare @sql varchar(8000) set @sql='select 拉号,产品编号,产品名称' select @sql=@sql+',sum(case when convert(varchar(10),日期,101)='''+日期+''' then 日生产数量 else 0 end) ['+日期+']' from (select distinct 日期 from( select top 100 percent convert(varchar(10),日期,101) as 日期 from tb) tt) t exec (@sql+' from tb group by 拉号,产品编号,产品名称') go drop table tb /* 拉号产品编号产品名称 05/10/2008 05/11/2008 05/12/2008 05/13/2008 ----- ---------- ---------- ----------- ----------- ----------- ----------- 01 k1084 A公仔 1000 0 0 0 01 k1085 C公仔 0 0 5000 0 01 k1085 D公仔 0 0 0 10000 01 k1088 B公仔 0 500 0 0 02 k1084 A公仔 0 0 0 6000 02 k1087 E公仔 6000 0 0 0 02 k1088 B公仔 0 3000 0 0 02 k1089 G公仔 0 0 9000 0 */

create table tb(拉号 varchar(5),产品编号 varchar(10),产品名称 varchar(10),日生产数量 int,日期 datetime) insert into tb select '01' , 'k1084' , 'A公仔' , 1000 , '2008/05/10' insert into tb select '01' , 'k1088' , 'B公仔' , 500 , '2008/05/11' insert into tb select '01' , 'k1085' , 'C公仔' , 5000 , '2008/05/12' insert into tb select '01' , 'k1085' , 'D公仔' , 10000 , '2008/05/13' insert into tb select '02' , 'k1087' , 'E公仔' , 6000 , '2008/05/10' insert into tb select '02' , 'k1088' , 'B公仔' , 3000 , '2008/05/11' insert into tb select '02' , 'k1089' , 'G公仔' , 9000 ,'2008/05/12' insert into tb select '02' , 'k1084' , 'A公仔' , 6000 ,'2008/05/13' go declare @sql varchar(8000) set @sql='select 拉号,产品编号,产品名称' select @sql=@sql+',sum(case when convert(varchar(10),日期,101)='''+日期+''' then 日生产数量 else 0 end) ['+日期+']' from (select distinct 日期 from( select top 100 percent convert(varchar(10),日期,101) as 日期 from tb order by 日期) tt) t exec (@sql+' from tb group by 拉号,产品编号,产品名称') go drop table tb /* 拉号产品编号产品名称 05/10/2008 05/11/2008 05/12/2008 05/13/2008 ----- ---------- ---------- ----------- ----------- ----------- ----------- 01 k1084 A公仔 1000 0 0 0 01 k1085 C公仔 0 0 5000 0 01 k1085 D公仔 0 0 0 10000 01 k1088 B公仔 0 500 0 0 02 k1084 A公仔 0 0 0 6000 02 k1087 E公仔 6000 0 0 0 02 k1088 B公仔 0 3000 0 0 02 k1089 G公仔 0 0 9000 0 */

/* -- Author:Flystone -- Date:2008-05-15 -- Version:V1.001 */ -- Test Data: Ta If object_id('Ta') is not null Drop table Ta Go Create table Ta(拉号 varchar(2),产口编号 varchar(5),产品名称 varchar(6),日生产数量 int,日期 smalldatetime) Go Insert into Ta select '01','k1084','A公仔',1000,'2008/05/10' union all select '01','k1088','B公仔',500,'2008/05/11' union all select '01','k1085','C公仔',5000,'2008/05/12' union all select '01','k1085','D公仔',10000,'2008/05/13' union all select '02','k1087','E公仔',6000,'2008/05/10' union all select '02','k1088','B公仔',3000,'2008/05/11' union all select '02','k1089','G公仔',9000,'2008/05/12' union all select '02','k1084','A公仔',6000,'2008/05/13' Go --Start declare @s varchar(8000) select @s = isnull(@s+',','')+'['+convert(char(10),日期,120)+'] = max(case when datediff(d,日期,'''+ convert(char(10),日期,120)+''') = 0 then 日生产数量 else 0 end)' from (select distinct 日期 from ta) a exec('select 拉号,产口编号,产品名称,'+ @s + ' from ta group by 拉号,产口编号,产品名称 order by 拉号,产品名称') --Result: /* 拉号产口编号产品名称 2008-05-10 2008-05-11 2008-05-12 2008-05-13 ---- ----- ------ ----------- ----------- ----------- ----------- 01 k1084 A公仔 1000 0 0 0 01 k1085 C公仔 0 0 5000 0 01 k1085 D公仔 0 0 0 10000 01 k1088 B公仔 0 500 0 0 02 k1084 A公仔 0 0 0 6000 02 k1087 E公仔 6000 0 0 0 02 k1088 B公仔 0 3000 0 0 02 k1089 G公仔 0 0 9000 0 */ --End

create table tb(拉号 varchar(5),产品编号 varchar(10),产品名称 varchar(10),日生产数量 int,日期 datetime) insert into tb select '01' , 'k1084' , 'A公仔' , 1000 , '2008/05/10' insert into tb select '01' , 'k1088' , 'B公仔' , 500 , '2008/05/11' insert into tb select '01' , 'k1085' , 'C公仔' , 5000 , '2008/05/12' insert into tb select '01' , 'k1085' , 'D公仔' , 10000 , '2008/05/13' insert into tb select '02' , 'k1087' , 'E公仔' , 6000 , '2008/05/10' insert into tb select '02' , 'k1088' , 'B公仔' , 3000 , '2008/05/11' insert into tb select '02' , 'k1089' , 'G公仔' , 9000 ,'2008/05/12' insert into tb select '02' , 'k1084' , 'A公仔' , 6000 ,'2008/05/13' go declare @sql varchar(8000) set @sql='select 拉号,产品编号,产品名称' select @sql=@sql+',['+convert(varchar(10),日期,111)+']=sum(case 日期 when '''+convert(varchar(10),日期,111)+''' then 日生产数量 else 0 end)' from (select distinct convert(varchar(10),日期,111)日期 from tb)a set @sql=@sql+' from tb group by 拉号,产品编号,产品名称' exec(@sql)

加为好友

发送私信

在线聊天

happyflystone
无枪狙击手
等级:

发表于：2008-05-16 16:24:1210楼得分:10

SQL code

/*
-- Author:Flystone 
-- Date:2008-05-15 
-- Version:V1.001
*/

-- Test Data: Ta
If object_id('Ta') is not null 
    Drop table Ta
Go
Create table Ta(拉号 varchar(2),产口编号 varchar(5),产品名称 varchar(6),日生产数量 int,日期 smalldatetime)
Go
Insert into Ta
select '01','k1084','A公仔',1000,'2008/05/10' union all
select '01','k1088','B公仔',500,'2008/05/11' union all
select '01','k1085','C公仔',5000,'2008/05/12' union all
select '01','k1085','D公仔',10000,'2008/05/13' union all
select '02','k1087','E公仔',6000,'2008/05/10' union all
select '02','k1088','B公仔',3000,'2008/05/11' union all
select '02','k1089','G公仔',9000,'2008/05/12' union all
select '02','k1084','A公仔',6000,'2008/05/13' 
Go
--Start
declare @s varchar(8000)
select @s = isnull(@s+',','')+'['+convert(char(10),日期,111)+'] = max(case when datediff(d,日期,'''+ convert(char(10),日期,111)+''') = 0 then 日生产数量 else 0 end)'
from (select distinct 日期 from ta) a
exec('select 拉号,产口编号,产品名称,'+ @s + ' from ta group by 拉号,产口编号,产品名称
order by 拉号,产品名称')

--Result:
/*
拉号   产口编号  产品名称   2008/05/10  2008/05/11  2008/05/12  2008/05/13  
---- ----- ------ ----------- ----------- ----------- ----------- 
01   k1084 A公仔    1000        0           0           0
01   k1088 B公仔    0           500         0           0
01   k1085 C公仔    0           0           5000        0
01   k1085 D公仔    0           0           0           10000
02   k1084 A公仔    0           0           0           6000
02   k1088 B公仔    0           3000        0           0
02   k1087 E公仔    6000        0           0           0
02   k1089 G公仔    0           0           9000        0


*/
--End

修改删除举报引用回复