select 语句求解 数据表中多月份销售和库存数据,结果需要按月份输出

Question

大类表 test_cat
prd_id     prd_name
1           test_1
2           test_2
3           test_3数据表 prd_values
values_id     prd_id   prd_month         v_1         v_2          
1                3        3               5           6             
2                2        2               3           2               
3                1        3               2           1              
4                2        1               4           6              
5                3        2               1           2              
6                3        3               2           4              需要的结果：
                     month_1            month_2           month_3
cat_name             v_1  v_2           v_1  v_2          v_1  v_2  
test_1                0    0             0    0            2    1    
test_2                4    6             3    2            0    0    
test_3                0    0             1    2            7    10   结果说明：
大类表和数据表通过prd_id关联，数据表中有按月份记录大类表中产品的库存数据和销售数据，
现在希望通过一个存储过程来实现每个产品按月份的库存和销售统计数据。附带建表语句
--建test_cat表
create table test_cat(prd_id int,prd_name varchar(15))  
insert into test_cat select 1,'test_1' 
union all select 2,'test_2' 
union all select 3,'test_3'--建prd_values表 
create table prd_values(values_id int,prd_id int,prd_month int,v_1 int,v_2 int) 
insert into  prd_values select 1,3,3,5,6
union all select 2,2,2,3,2 
union all select 3,1,3,2,1
union all select 4,2,1,4,6
union all select 5,3,2,1,2
union all select 6,3,3,2,4--drop table test_cat
--drop table prd_values

Answer 1

这是哪来的 month_1            month_2           month_3 

Answer 2

--建test_cat表 
create table test_cat(prd_id int,prd_name varchar(15))   
insert into test_cat select 1,'test_1'  
union all select 2,'test_2'  
union all select 3,'test_3' --建prd_values表  
create table prd_values(values_id int,prd_id int,prd_month int,v_1 int,v_2 int)  
insert into  prd_values select 1,3,3,5,6 
union all select 2,2,2,3,2  
union all select 3,1,3,2,1 
union all select 4,2,1,4,6 
union all select 5,3,2,1,2 
union all select 6,3,3,2,4 select a.prd_name ,
sum(case prd_month when 1 then v_1 else 0 end) month_1_v_1,
sum(case prd_month when 1 then v_2 else 0 end) month_1_v_2,
sum(case prd_month when 2 then v_1 else 0 end) month_2_v_1,
sum(case prd_month when 2 then v_2 else 0 end) month_2_v_2,
sum(case prd_month when 3 then v_1 else 0 end) month_3_v_1,
sum(case prd_month when 3 then v_2 else 0 end) month_3_v_2
from test_cat a , prd_values b
where a.prd_id = b.prd_id
group by a.prd_namedrop table test_cat 
drop table prd_values/*
prd_name        month_1_v_1 month_1_v_2 month_2_v_1 month_2_v_2 month_3_v_1 month_3_v_2 
--------------- ----------- ----------- ----------- ----------- ----------- ----------- 
test_1          0           0           0           0           2           1
test_2          4           6           3           2           0           0
test_3          0           0           1           2           7           10（所影响的行数为 3 行）
*/

Answer 3

--建test_cat表 
create table test_cat(prd_id int,prd_name varchar(15))   
insert into test_cat select 1,'test_1'  
union all select 2,'test_2'  
union all select 3,'test_3' --建prd_values表  
create table prd_values(values_id int,prd_id int,prd_month int,v_1 int,v_2 int)  
insert into  prd_values select 1,3,3,5,6 
union all select 2,2,2,3,2  
union all select 3,1,3,2,1 
union all select 4,2,1,4,6 
union all select 5,3,2,1,2 
union all select 6,3,3,2,4 
/*需要的结果： 
                     month_1            month_2           month_3 
cat_name             v_1  v_2           v_1  v_2          v_1  v_2   
test_1                0    0             0    0            2    1     
test_2                4    6             3    2            0    0     
test_3                0    0             1    2            7    10    */declare @s varchar(2000)select @s = 'select a.prd_name'
select @s = @s + ',[month_'+ltrim(prd_month) +'_v_1]= max(case when b.prd_month = '+ltrim(prd_month)+ 
' then v_1 else 0 end) ,[month_'+ltrim(prd_month) +'_v_2]=max(case when b.prd_month = '+ltrim(prd_month)+ 
' then v_2 else 0 end)'
from (select distinct prd_month from prd_values) a
exec( @s+ ' from test_cat a ,prd_values b where a.prd_id = b.prd_id group by a.prd_name')drop table test_cat 
drop table prd_values
/*
prd_name        month_1_v_1 month_1_v_2 month_2_v_1 month_2_v_2 month_3_v_1 month_3_v_2 
--------------- ----------- ----------- ----------- ----------- ----------- ----------- 
test_1          0           0           0           0           2           1
test_2          4           6           3           2           0           0
test_3          0           0           1           2           5           6
*/

Answer 4

--建test_cat表 
create table test_cat(prd_id int,prd_name varchar(15))   
insert into test_cat select 1,'test_1'  
union all select 2,'test_2'  
union all select 3,'test_3' --建prd_values表  
create table prd_values(values_id int,prd_id int,prd_month int,v_1 int,v_2 int)  
insert into  prd_values select 1,3,3,5,6 
union all select 2,2,2,3,2  
union all select 3,1,3,2,1 
union all select 4,2,1,4,6 
union all select 5,3,2,1,2 
union all select 6,3,3,2,4 
/*需要的结果： 
                     month_1            month_2           month_3 
cat_name             v_1  v_2           v_1  v_2          v_1  v_2   
test_1                0    0             0    0            2    1     
test_2                4    6             3    2            0    0     
test_3                0    0             1    2            7    10    */declare @s varchar(2000)select @s = 'select a.prd_name'
select @s = @s + ',[month_'+ltrim(prd_month) +'_v_1]= sum(case when b.prd_month = '+ltrim(prd_month)+ 
' then v_1 else 0 end) ,[month_'+ltrim(prd_month) +'_v_2]=sum(case when b.prd_month = '+ltrim(prd_month)+ 
' then v_2 else 0 end)'
from (select distinct prd_month from prd_values) a
exec( @s+ ' from test_cat a ,prd_values b where a.prd_id = b.prd_id group by a.prd_name')drop table test_cat 
drop table prd_values
/*prd_name        month_1_v_1 month_1_v_2 month_2_v_1 month_2_v_2 month_3_v_1 month_3_v_2 
--------------- ----------- ----------- ----------- ----------- ----------- ----------- 
test_1          0           0           0           0           2           1
test_2          4           6           3           2           0           0
test_3          0           0           1           2           7           10*/

Answer 5

--建test_cat表 
create table test_cat(prd_id int,prd_name varchar(15))   
insert into test_cat select 1,'test_1'  
union all select 2,'test_2'  
union all select 3,'test_3' --建prd_values表  
create table prd_values(values_id int,prd_id int,prd_month int,v_1 int,v_2 int)  
insert into  prd_values select 1,3,3,5,6 
union all select 2,2,2,3,2  
union all select 3,1,3,2,1 
union all select 4,2,1,4,6 
union all select 5,3,2,1,2 
union all select 6,3,3,2,4 --静态SQL，指月份固定.
select a.prd_name ,
  sum(case prd_month when 1 then v_1 else 0 end) month_1_v_1,
  sum(case prd_month when 1 then v_2 else 0 end) month_1_v_2,
  sum(case prd_month when 2 then v_1 else 0 end) month_2_v_1,
  sum(case prd_month when 2 then v_2 else 0 end) month_2_v_2,
  sum(case prd_month when 3 then v_1 else 0 end) month_3_v_1,
  sum(case prd_month when 3 then v_2 else 0 end) month_3_v_2
from test_cat a , prd_values b
where a.prd_id = b.prd_id
group by a.prd_name--动态SQL，指月份不固定.
declare @sql varchar(8000)
set @sql = 'select a.prd_name '
select @sql = @sql + ' , sum(case prd_month when ''' + cast(prd_month as varchar)  + ''' then v_1 else 0 end) [month_' + cast(prd_month as varchar) + '_v_1]'
                   + ' , sum(case prd_month when ''' + cast(prd_month as varchar)  + ''' then v_2 else 0 end) [month_' + cast(prd_month as varchar) + '_v_2]'
from (select distinct prd_month from prd_values) as a
set @sql = @sql + ' from test_cat a , prd_values b where a.prd_id = b.prd_id group by a.prd_name'
exec(@sql) drop table test_cat 
drop table prd_values/*
prd_name        month_1_v_1 month_1_v_2 month_2_v_1 month_2_v_2 month_3_v_1 month_3_v_2 
--------------- ----------- ----------- ----------- ----------- ----------- ----------- 
test_1          0           0           0           0           2           1
test_2          4           6           3           2           0           0
test_3          0           0           1           2           7           10（所影响的行数为 3 行）
*/