select 产品类型ID,sum(工时时长) as '工时', sum(case when 月份='6月' then 工时时长 else 0 end) as '6月', sum(case when 月份='7月' then 工时时长 else 0 end) as '7月', sum(case when 月份='8月' then 工时时长 else 0 end) as '8月', sum(case when 月份='9月' then 工时时长 else 0 end) as '9月' from 工时表 group by 产品类型ID
create table work(worktime decimal,workmonth decimal,type varchar(20)) create table chanpin(type varchar(20),cpname varchar(60)) insert into work values(11, '1', 'c01') insert into work values( 11, '1', 'c02') insert into work values(11, '2', 'c02') insert into work values(12, '1', 'c02') insert into work values( 12, '2', 'c02') insert into work values(13, '1', 'c02') insert into work values(14, '1', 'c03') insert into work values(15, '1', 'c03')insert into chanpin values('c01', 'cc0011') insert into chanpin values('c02', 'cc0022') insert into chanpin values('c03', 'cc0033') 想查出的表是: 产品 产品工时 1月 2月 cc0011 11 11 0 cc0022 59 36 23 cc0033 29 29 0 其中月份是通过参数输入的,我知道交叉表的用法,但是这个地方查出来的 数据表不是通过某张表查出来的,而是参数输入的,请问这个时候该怎么处理,谢谢各位了
--Create table a(a_time int,a_month int,a_id nvarchar(20)) --create table b (b_id nvarchar(20),b_name nvarchar(40))select tmp.a_time,tmp.month_6,tmp.month_7,tmp.month_8,tmp.month_9,tmp.a_id, b.b_name from (select sum(a_time) as a_time, sum(case a_month when 6 then a_time else 0 end) as month_6, sum(case a_month when 7 then a_time else 0 end) as month_7, sum(case a_month when 8 then a_time else 0 end) as month_8, sum(case a_month when 9 then a_time else 0 end) as month_9,a_id from a group by a_id) tmp left join b on tmp.a_id = b.b_id order by b.b_id
create table tb_test_1(h int,m int,[id] int) insert into tb_test_1 select 160,6,1 union all select 158,6,2 union all select 158,6,3 union all select 150,7,1 union all select 155,7,2 union all select 156,7,3 union all select 160,7,1 union all select 161,8,2 union all select 153,8,3 union all select 164,9,1 union all select 155,9,2 union all select 154,9,3create table tb_test_2([id]int,p_name varchar(20)) insert into tb_test_2 select 1,'产品1'union all select 2,'产品2'union all select 3,'产品3'select b.p_name as 产品, a.h as 工时, isnull(s6.h,0) as '6月', isnull(s7.h,0) as '7月', isnull(s8.h,0) as '8月', isnull(s9.h,0) as '9月' from (select [id],sum(h)as h from tb_test_1 group by [id])a left join tb_test_2 b on a.[id]=b.[id] left join(select [id],sum(h)as h from tb_test_1 where m=6 group by [id])s6 on a.[id]=s6.[id] left join(select [id],sum(h)as h from tb_test_1 where m=7 group by [id])s7 on a.[id]=s7.[id] left join(select [id],sum(h)as h from tb_test_1 where m=8 group by [id])s8 on a.[id]=s8.[id] left join(select [id],sum(h)as h from tb_test_1 where m=9 group by [id])s9 on a.[id]=s9.[id] drop table tb_test_1,tb_test_2/* 产品 工时 6月 7月 8月 9月 -------------------- ----------- ----------- ----------- ----------- ----------- 产品1 634 160 310 0 164 产品2 629 158 155 161 155 产品3 621 158 156 153 154(所影响的行数为 3 行) */
create table work1 (worktime decimal,workmonth char(2),type varchar(20)) go create table chanpin(type varchar(20),cpname varchar(60)) go insert into work1 values(11, '1', 'c01') insert into work1 values( 11, '1', 'c02') insert into work1 values(11, '2', 'c02') insert into work1 values(12, '1', 'c02') insert into work1 values( 12, '2', 'c02') insert into work1 values(13, '1', 'c02') insert into work1 values(14, '1', 'c03') insert into work1 values(15, '1', 'c03')insert into chanpin values('c01', 'cc0011') insert into chanpin values('c02', 'cc0022') insert into chanpin values('c03', 'cc0033') go ------------------------------------------------------------ create proc my_p1 (@m1 char(2)='0',@m2 char(2)='0',@m3 char(2)='0',@m4 char(2)='0',@m5 char(2)='0',@m6 char(2)='0', @m7 char(2)='0',@m8 char(2)='0',@m9 char(2)='0',@m10 char(2)='0',@m11 char(2)='0',@m12 char(2)='0') as declare @sql varchar(4000) select @sql='select cpname as ''产品'',sum(worktime) as ''产品工时'',' if @m1<>'0' select @sql=@sql+'sum(case workmonth when '''+@m1+''' then worktime else 0 end) as '''+@m1+'月'',' if @m2<>'0' select @sql=@sql+'sum(case workmonth when '''+@m2+''' then worktime else 0 end) as '''+@m2+'月'',' if @m3<>'0' select @sql=@sql+'sum(case workmonth when '''+@m3+''' then worktime else 0 end) as '''+@m3+'月'',' if @m4<>'0' select @sql=@sql+'sum(case workmonth when '''+@m4+''' then worktime else 0 end) as '''+@m4+'月'',' if @m5<>'0' select @sql=@sql+'sum(case workmonth when '''+@m5+''' then worktime else 0 end) as '''+@m5+'月'',' if @m6<>'0' select @sql=@sql+'sum(case workmonth when '''+@m6+''' then worktime else 0 end) as '''+@m6+'月'',' if @m7<>'0' select @sql=@sql+'sum(case workmonth when '''+@m7+''' then worktime else 0 end) as '''+@m7+'月'',' if @m8<>'0' select @sql=@sql+'sum(case workmonth when '''+@m8+''' then worktime else 0 end) as '''+@m8+'月'',' if @m9<>'0' select @sql=@sql+'sum(case workmonth when '''+@m9+''' then worktime else 0 end) as '''+@m9+'月'',' if @m10<>'0' select @sql=@sql+'sum(case workmonth when '''+@m10+''' then worktime else 0 end) as '''+@m10+'月'',' if @m11<>'0' select @sql=@sql+'sum(case workmonth when '''+@m11+''' then worktime else 0 end) as '''+@m11+'月'',' if @m12<>'0' select @sql=@sql+'sum(case workmonth when '''+@m12+''' then worktime else 0 end) as '''+@m12+'月'',' select @sql=left(@sql,len(@sql)-1) select @sql=@sql+' from work1 w,chanpin c where w.type=c.type group by cpname' exec(@sql) go--调用示例: my_p1 '1','2'注意:我把你的work表改成work1了
create table 工时表(产品ID char(50),工时时长 int,月份 int) insert into 工时表 select '101',100,2 insert into 工时表 select '102',300,2 insert into 工时表 select '101',200,6 insert into 工时表 select '103',100,4 insert into 工时表 select '102', 50,7 insert into 工时表 select '101',100,7 insert into 工时表 select '102',300,8 insert into 工时表 select '101',200,8 insert into 工时表 select '103',100,9 insert into 工时表 select '102', 50,9 go create table 产品表(产品ID int,产品 char(50)) insert into 产品表 select '101','产品a' insert into 产品表 select '102','产品b' insert into 产品表 select '103','产品c' go declare @s varchar(8000) set @s ='' select @s = @s+'['+convert(varchar(10),月份)+'月份]=sum(case a.月份 when '+convert(varchar(10),月份)+' then a.工时时长 else 0 end),' from 工时表 where 月份 between 6 and 9 group by 月份 set @s = 'select '+@s+'b.产品 from 工时表 a Left outer Join 产品表 b on a.产品ID=b.产品ID group by a.产品ID,b.产品'--print @sexec (@s) drop table 工时表,产品表
create table gs(gslen decimal(10),ym int,item_no varchar(10)) insert gs select 20,200506,'1000' union all select 40,200506,'2000' union all select 15,200506,'3000' union all select 10,200506,'4000' union all select 30,200507,'1000' union all select 60,200507,'2000' union all select 25,200507,'3000' union all select 50,200507,'4000' union all select 15,200508,'1000' union all select 30,200508,'2000' union all select 40,200508,'3000' union all select 10,200508,'4000' union all select 35,200509,'1000' union all select 70,200509,'2000' union all select 70,200509,'3000' union all select 30,200509,'4000' gocreate table prod (item_no varchar(10),item_name varchar(10)) insert prod select '1000','item1' union all select '2000','item2' union all select '3000','item3' union all select '4000','item4' go declare @s varchar(8000) set @s='' select @s=@s+',['+cast(ym as varchar(6))+']=isnull(sum(case ym when '+ cast(ym as varchar(6)) + ' then gslen end),0)' from gs group by ymselect @s='select b.item_name,a.* from (select item_no,Total= sum(gslen)' + @s+' from gs group by item_no) a ,prod b where a.item_no=b.item_no'exec(@s)drop table gs drop table prod/* item_name item_no Total 200506 200507 200508 200509 ---------- ---------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- item1 1000 100 20 30 15 35 item2 2000 200 40 60 30 70 item3 3000 150 15 25 40 70 item4 4000 100 10 50 10 30警告: 聚合或其它 SET 操作消除了空值。*/
给出这两个表的样例数据和你需要的结果,应该是交叉表的问题一般可以用case when语法
應該就是行列轉換方法就可以實現。
http://community.csdn.net/Expert/topic/4357/4357527.xml?temp=7.455081E-02
sum(case when 月份='6月' then 工时时长 else 0 end) as '6月',
sum(case when 月份='7月' then 工时时长 else 0 end) as '7月',
sum(case when 月份='8月' then 工时时长 else 0 end) as '8月',
sum(case when 月份='9月' then 工时时长 else 0 end) as '9月'
from 工时表
group by 产品类型ID
create table chanpin(type varchar(20),cpname varchar(60))
insert into work values(11, '1', 'c01')
insert into work values( 11, '1', 'c02')
insert into work values(11, '2', 'c02')
insert into work values(12, '1', 'c02')
insert into work values( 12, '2', 'c02')
insert into work values(13, '1', 'c02')
insert into work values(14, '1', 'c03')
insert into work values(15, '1', 'c03')insert into chanpin values('c01', 'cc0011')
insert into chanpin values('c02', 'cc0022')
insert into chanpin values('c03', 'cc0033')
想查出的表是:
产品 产品工时 1月 2月
cc0011 11 11 0
cc0022 59 36 23
cc0033 29 29 0
其中月份是通过参数输入的,我知道交叉表的用法,但是这个地方查出来的
数据表不是通过某张表查出来的,而是参数输入的,请问这个时候该怎么处理,谢谢各位了
--create table b (b_id nvarchar(20),b_name nvarchar(40))select tmp.a_time,tmp.month_6,tmp.month_7,tmp.month_8,tmp.month_9,tmp.a_id,
b.b_name from
(select sum(a_time) as a_time,
sum(case a_month when 6 then a_time else 0 end) as month_6,
sum(case a_month when 7 then a_time else 0 end) as month_7,
sum(case a_month when 8 then a_time else 0 end) as month_8,
sum(case a_month when 9 then a_time else 0 end) as month_9,a_id
from a group by a_id) tmp left join b on tmp.a_id = b.b_id order by b.b_id
insert into tb_test_1
select 160,6,1 union all
select 158,6,2 union all
select 158,6,3 union all
select 150,7,1 union all
select 155,7,2 union all
select 156,7,3 union all
select 160,7,1 union all
select 161,8,2 union all
select 153,8,3 union all
select 164,9,1 union all
select 155,9,2 union all
select 154,9,3create table tb_test_2([id]int,p_name varchar(20))
insert into tb_test_2
select 1,'产品1'union all
select 2,'产品2'union all
select 3,'产品3'select b.p_name as 产品,
a.h as 工时,
isnull(s6.h,0) as '6月',
isnull(s7.h,0) as '7月',
isnull(s8.h,0) as '8月',
isnull(s9.h,0) as '9月'
from (select [id],sum(h)as h from tb_test_1 group by [id])a
left join tb_test_2 b on a.[id]=b.[id]
left join(select [id],sum(h)as h from tb_test_1 where m=6 group by [id])s6 on a.[id]=s6.[id]
left join(select [id],sum(h)as h from tb_test_1 where m=7 group by [id])s7 on a.[id]=s7.[id]
left join(select [id],sum(h)as h from tb_test_1 where m=8 group by [id])s8 on a.[id]=s8.[id]
left join(select [id],sum(h)as h from tb_test_1 where m=9 group by [id])s9 on a.[id]=s9.[id]
drop table tb_test_1,tb_test_2/*
产品 工时 6月 7月 8月 9月
-------------------- ----------- ----------- ----------- ----------- -----------
产品1 634 160 310 0 164
产品2 629 158 155 161 155
产品3 621 158 156 153 154(所影响的行数为 3 行)
*/
(worktime decimal,workmonth char(2),type varchar(20))
go
create table chanpin(type varchar(20),cpname varchar(60))
go
insert into work1 values(11, '1', 'c01')
insert into work1 values( 11, '1', 'c02')
insert into work1 values(11, '2', 'c02')
insert into work1 values(12, '1', 'c02')
insert into work1 values( 12, '2', 'c02')
insert into work1 values(13, '1', 'c02')
insert into work1 values(14, '1', 'c03')
insert into work1 values(15, '1', 'c03')insert into chanpin values('c01', 'cc0011')
insert into chanpin values('c02', 'cc0022')
insert into chanpin values('c03', 'cc0033')
go
------------------------------------------------------------
create proc my_p1 (@m1 char(2)='0',@m2 char(2)='0',@m3 char(2)='0',@m4 char(2)='0',@m5 char(2)='0',@m6 char(2)='0',
@m7 char(2)='0',@m8 char(2)='0',@m9 char(2)='0',@m10 char(2)='0',@m11 char(2)='0',@m12 char(2)='0')
as
declare @sql varchar(4000)
select @sql='select cpname as ''产品'',sum(worktime) as ''产品工时'','
if @m1<>'0'
select @sql=@sql+'sum(case workmonth when '''+@m1+''' then worktime else 0 end) as '''+@m1+'月'','
if @m2<>'0'
select @sql=@sql+'sum(case workmonth when '''+@m2+''' then worktime else 0 end) as '''+@m2+'月'','
if @m3<>'0'
select @sql=@sql+'sum(case workmonth when '''+@m3+''' then worktime else 0 end) as '''+@m3+'月'','
if @m4<>'0'
select @sql=@sql+'sum(case workmonth when '''+@m4+''' then worktime else 0 end) as '''+@m4+'月'','
if @m5<>'0'
select @sql=@sql+'sum(case workmonth when '''+@m5+''' then worktime else 0 end) as '''+@m5+'月'','
if @m6<>'0'
select @sql=@sql+'sum(case workmonth when '''+@m6+''' then worktime else 0 end) as '''+@m6+'月'','
if @m7<>'0'
select @sql=@sql+'sum(case workmonth when '''+@m7+''' then worktime else 0 end) as '''+@m7+'月'','
if @m8<>'0'
select @sql=@sql+'sum(case workmonth when '''+@m8+''' then worktime else 0 end) as '''+@m8+'月'','
if @m9<>'0'
select @sql=@sql+'sum(case workmonth when '''+@m9+''' then worktime else 0 end) as '''+@m9+'月'','
if @m10<>'0'
select @sql=@sql+'sum(case workmonth when '''+@m10+''' then worktime else 0 end) as '''+@m10+'月'','
if @m11<>'0'
select @sql=@sql+'sum(case workmonth when '''+@m11+''' then worktime else 0 end) as '''+@m11+'月'','
if @m12<>'0'
select @sql=@sql+'sum(case workmonth when '''+@m12+''' then worktime else 0 end) as '''+@m12+'月'','
select @sql=left(@sql,len(@sql)-1)
select @sql=@sql+' from work1 w,chanpin c where w.type=c.type group by cpname'
exec(@sql)
go--调用示例:
my_p1 '1','2'注意:我把你的work表改成work1了
(worktime decimal,workmonth char(2),type varchar(20))
insert into 工时表 select '101',100,2
insert into 工时表 select '102',300,2
insert into 工时表 select '101',200,6
insert into 工时表 select '103',100,4
insert into 工时表 select '102', 50,7
insert into 工时表 select '101',100,7
insert into 工时表 select '102',300,8
insert into 工时表 select '101',200,8
insert into 工时表 select '103',100,9
insert into 工时表 select '102', 50,9
go
create table 产品表(产品ID int,产品 char(50))
insert into 产品表 select '101','产品a'
insert into 产品表 select '102','产品b'
insert into 产品表 select '103','产品c'
go
declare @s varchar(8000)
set @s =''
select @s = @s+'['+convert(varchar(10),月份)+'月份]=sum(case a.月份 when '+convert(varchar(10),月份)+' then a.工时时长 else 0 end),' from 工时表 where 月份 between 6 and 9 group by 月份
set @s = 'select '+@s+'b.产品 from 工时表 a Left outer Join 产品表 b on a.产品ID=b.产品ID group by a.产品ID,b.产品'--print @sexec (@s)
drop table 工时表,产品表
insert gs
select 20,200506,'1000' union all
select 40,200506,'2000' union all
select 15,200506,'3000' union all
select 10,200506,'4000' union all
select 30,200507,'1000' union all
select 60,200507,'2000' union all
select 25,200507,'3000' union all
select 50,200507,'4000' union all
select 15,200508,'1000' union all
select 30,200508,'2000' union all
select 40,200508,'3000' union all
select 10,200508,'4000' union all
select 35,200509,'1000' union all
select 70,200509,'2000' union all
select 70,200509,'3000' union all
select 30,200509,'4000'
gocreate table prod (item_no varchar(10),item_name varchar(10))
insert prod
select '1000','item1' union all
select '2000','item2' union all
select '3000','item3' union all
select '4000','item4'
go
declare @s varchar(8000)
set @s=''
select @s=@s+',['+cast(ym as varchar(6))+']=isnull(sum(case ym when '+ cast(ym as varchar(6)) + ' then gslen end),0)' from gs group by ymselect @s='select b.item_name,a.* from (select item_no,Total= sum(gslen)' + @s+' from gs group by item_no) a ,prod b where a.item_no=b.item_no'exec(@s)drop table gs
drop table prod/*
item_name item_no Total 200506 200507 200508 200509
---------- ---------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
item1 1000 100 20 30 15 35
item2 2000 200 40 60 30 70
item3 3000 150 15 25 40 70
item4 4000 100 10 50 10 30警告: 聚合或其它 SET 操作消除了空值。*/