编程论坛 → SQL Server论坛

SQL记录拆分

jimmy178293 发布于 2014-12-20 10:43， 554 次点击

只有本站会员才能查看附件，请登录

请问大家能否把图中两条记录用SQL语句，拆分为4条，也就是查询出来有12条记录，谢谢！
（拆分的依据是S1，S2，S3，S4中有没有数量，有的话独立开来）,求救！！！
拆分前：              arrival_id  manual_id    supply_no warehouse_no    goods_no   colorid    long   s1  s2  s3 s4
                      123456      abcd         123a      kkk             fff        ddd         0     0    5   9  0
                      123456      abcd         123a      kkk             zzz        ddd         0     0    4   1  0

这两条记录拆分后如下：arrival_id  manual_id    supply_no warehouse_no    goods_no   colorid    long   s1  s2  s3 s4
                      123456      abcd         123a      kkk             fff        ddd         0     0    5   0  0
                      123456      abcd         123a      kkk             fff        ddd         0     0    0   9  0
                      123456      abcd         123a      kkk             zzz        ddd         0     0    4   0  0
                      123456      abcd         123a      kkk             zzz        ddd         0     0    0   1  0

4 回复

#2

tlliqi2014-12-21 19:08

先看看

#3

jimmy1782932014-12-22 09:18

回复 2楼 tlliqi

大哥求救！

#4

volte2014-12-24 13:07

select arrival_id, manual_id,supply_no,,warehouse_no,goods_no,colorid,long,s1, 0 as s2, 0 as s3,0 as s4 from Table where s1>0 and s2=0 and s3=0 and s4=0
union all
select arrival_id, manual_id,supply_no,,warehouse_no,goods_no,colorid,long,0 as s1, s2, 0 as s3,0 as s4 from Table where s1=0 and s2>0 and s3=0 and s4=0
union all
select arrival_id, manual_id,supply_no,,warehouse_no,goods_no,colorid,long,0 as s1, 0 as s2, s3,0 as s4 from Table where s1=0 and s2=0 and s3>0 and s4=0
union all
select arrival_id, manual_id,supply_no,,warehouse_no,goods_no,colorid,long,0 as s1, 0 as s2, 0 as s3,s4 from Table where s1=0 and s2=0 and s3=0 and s4>0

#5

antjl2014-12-26 16:57

---个人习惯，首字母大写，便于阅读；
---算法：
---     1、S1列到S4列不管有没有0都要求结果有是0的行;
---     2、S1列到S4如果非0的话，单独列一行；
---     3、Union会自动剔除重复行
Select Arrival_ID,Manual_ID,Supply_No,Warehouse_No,Goods_No,ColorID,[Long],0 As S1,S2,S3,S4 From Arrival
Union Select Arrival_ID,Manual_ID,Supply_No,Warehouse_No,Goods_No,ColorID,[Long],S1,0 As S2,S3,S4 From Arrival
Union Select Arrival_ID,Manual_ID,Supply_No,Warehouse_No,Goods_No,ColorID,[Long],S1,S2,0 As S3,S4 From Arrival
Union Select Arrival_ID,Manual_ID,Supply_No,Warehouse_No,Goods_No,ColorID,[Long],S1,S2,S3,0 As S4 From Arrival
Union Select Arrival_ID,Manual_ID,Supply_No,Warehouse_No,Goods_No,ColorID,[Long],S1,S2,S3,S4 From Arrival Where S1<>0
Union Select Arrival_ID,Manual_ID,Supply_No,Warehouse_No,Goods_No,ColorID,[Long],S1,S2,S3,S4 From Arrival Where S2<>0
Union Select Arrival_ID,Manual_ID,Supply_No,Warehouse_No,Goods_No,ColorID,[Long],S1,S2,S3,S4 From Arrival Where S3<>0
Union Select Arrival_ID,Manual_ID,Supply_No,Warehouse_No,Goods_No,ColorID,[Long],S1,S2,S3,S4 From Arrival Where S4<>0

1