交叉表分析.sql

sqlserver 数据库编程的绝好脚本

--逐步去理解

--示例数据(用少量数据说明)
create table tb(rqty int,item_no varchar(10),wh_no int)
insert tb select 3,'1F40001A',801
union all select 2,'1Z40031A',801
union all select 1,'1Z40031A',400
go

--第一步,wh_no 固定的情况,生成交叉的列
select item_no
	,[801]=case wh_no when 801 then rqty else 0 end
	,[400]=case wh_no when 400 then rqty else 0 end
from tb

/*--结果

item_no    801         400         
---------- ----------- ----------- 
1F40001A   3           0
1Z40031A   0           2    --和下面这条的item_no相同,现在被放在了两行上
1Z40031A   0           1

（所影响的行数为 3 行）
--*/

--第二步,按上面的写法,还要处理 item_no 相同的行合并在一齐,因此再用sum合并
select item_no
	,[801]=sum(case wh_no when 801 then rqty else 0 end)
	,[400]=sum(case wh_no when 400 then rqty else 0 end)
from tb
group by item_no

/*--结果

item_no    801         400         
---------- ----------- ----------- 
1F40001A   3           0
1Z40031A   2           1  --这样合并了相同的 item_no ,得到了正确的结果

（所影响的行数为 2 行）
--*/

--第三步,如果 wh_no 列的值不是预知的,那我们就只能通过查询表中的数据来获得 wh_no
/*--并按第二步的方式生成重语句
观察第二步的语句可以知道
	,[801]=sum(case wh_no when 801 then rqty else 0 end)
	,[400]=sum(case wh_no when 400 then rqty else 0 end)
中的 801,400 是根据表中的 wh_no 值生成的,语句的条数也是根据 distinct wh_no 的条数决定的
所以我们先按上面的格式很容易写出生成处理语句的select
--*/
select ',['+rtrim(wh_no)+']=sum(case wh_no when '+rtrim(wh_no)+' then rqty else 0 end)'
from(select distinct wh_no from tb)a

/*--结果

,[400]=sum(case wh_no when 400 then rqty else 0 end)
,[801]=sum(case wh_no when 801 then rqty else 0 end)
--*/

--第四步,上面生成的语句正好是我们需要的,但我们还要想办法把它放到一个变量中,所以做如下处理
declare @s varchar(8000) --定义一个保存结果的变量
set @s=''  --初始化变量(不初始化的变量值是null,无法进行后面的处理)

select @s=@s+ --按顺序相加上面生成的结果(这个处理过程可以理解为一个游标逐条处理)
	',['+rtrim(wh_no)+']=sum(case wh_no when '+rtrim(wh_no)+' then rqty else 0 end)'
from(select distinct wh_no from tb)a

select 得到的变量值=@s 

/*--结果

得到的变量值                                                                                                                                                                                                                                                           
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
,[400]=sum(case wh_no when 400 then rqty else 0 end),[801]=sum(case wh_no when 801 then rqty else 0 end)

（所影响的行数为 1 行）
--*/

--第五步,通过上面的处理,已经把动态处理的sql脚本部分放到一个变量@s中了
--按下来只需要加上固定的部分就是一个完整的处理过程了
set @s='select item_no' --加上第二步处理语句中的头
	+@s                 --@s代替了第二步中的列处理部分
	+'from tb
group by item_no'       --加上第二步处理中的尾
select 最终生成的动态sql语句=@s

/*--结果

select item_no,[400]=sum(case wh_no when 400 then rqty else 0 end),[801]=sum(case wh_no when 801 then rqty else 0 end)from tb
group by item_no
--*/

--第六步,通过上面的处理,已经得到了和第二步分析中一样的sql语句(格式稍有区别)
--剩下的就是用exec执行这个动态语句就OK了
exec(@s)

/*--结果(与第二步的结果一致,除了列序)

item_no    400         801         
---------- ----------- ----------- 
1F40001A   0           3
1Z40031A   1           2
--*/
go

--删除测试
drop table tb