101.交叉表分析

雨点打透心脏的1/2处 2024-02-19 18:59 58阅读 0赞

--逐步去理解
    
    --示例数据(用少量数据说明)
    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