sql行合并
如图1：
如图2：
如图3：
图1和图2是渠道"WH00026"某天的相关数据，怎么把它合并一条数据呢如成图3的效果？有两种方法：
第一种: 用表变量
1 declare @ac table 2 ( 3 channelid varchar(100), 4 pmoney decimal(18,2), 5 ptype tinyint, 6 inday varchar(50) 7 ) 8 insert into @ac(channelid,pmoney,ptype,inday) 9 select a.channelid,a.pmoney,a.ptype,a.inday 10 from( 11 select channelid,sum(totalFee) as pmoney,inday,1 as ptype from orderCPS where inday='2016-10-13' and channelid='WH00026' and comOrderStatus is null group by channelid,inday 12 union all 13 select channelid,sum(totalFee) as pmoney,inday,2 as ptype from orderCPS where inday='2016-10-13' and channelid='WH00026' and orderStatus=0 group by channelid,inday 14 ) a 15 16 17 declare @bc table 18 ( 19 channelid varchar(100), 20 pmoney decimal(18,2), 21 pcount int, 22 ptype tinyint, 23 inday varchar(50) 24 ) 25 insert into @bc(channelid,pmoney,pcount,ptype,inday) 26 select a.channelid,a.pmoney,a.pcount,a.ptype,a.inday 27 from( 28 select channelid,count(*) as pcount,sum(totalFee) as pmoney,1 as ptype ,inday from myOrderCPS where inday='2016-10-13' and channelid='WH00026' group by channelid,inday 29 union all 30 select channelid,count(*) as pcount,sum(totalFee) as pmoney,2 as ptype ,inday from OrderCPS where inday='2016-10-13' and channelid='WH00026' and orderStatus=0 group by channelid,inday 31 32 ) a 33 34 select a.*,b.* from ( 35 select channelid, 36 max(case ptype when 1 then pmoney end) as '对内金额', 37 max(case ptype when 2 then pmoney end) as '对外金额', 38 inday 39 from @ac 40 group by channelid,inday 41 ) a join 42 ( 43 select channelid, 44 max(case ptype when 1 then pcount end) as '请求数', 45 max(case ptype when 2 then pcount end) as '成功数', 46 max(case ptype when 1 then pmoney end) as '对内金额', 47 max(case ptype when 2 then pmoney end) as '对外金额', 48 inday 49 from @bc 50 group by channelid,inday 51 ) b on a.channelid=b.channelid and a.inday=b.inday
第二种：用with cte 短语：
1 with ac as 2 ( 3 select a.channelid,a.pmoney,a.ptype,a.inday 4 from( 5 select channelid,sum(totalFee) as pmoney,inday,1 as ptype from orderCPS where inday='2016-10-13' and channelid='WH00026' and comOrderStatus is null group by channelid,inday 6 union all 7 select channelid,sum(totalFee) as pmoney,inday,2 as ptype from orderCPS where inday='2016-10-13' and channelid='WH00026' and orderStatus=0 group by channelid,inday 8 ) a 9 ), 10 bc as 11 ( 12 select a.channelid,a.pmoney,a.pcount,a.ptype,a.inday 13 from( 14 select channelid,count(*) as pcount,sum(totalFee) as pmoney,1 as ptype ,inday from myOrderCPS where inday='2016-10-13' and channelid='WH00026' group by channelid,inday 15 union all 16 select channelid,count(*) as pcount,sum(totalFee) as pmoney,2 as ptype ,inday from OrderCPS where inday='2016-10-13' and channelid='WH00026' and orderStatus=0 group by channelid,inday 17 ) a 18 ) 19 select a.*,b.* from ( 20 select channelid, 21 max(case ptype when 1 then pmoney end) as '对内金额', 22 max(case ptype when 2 then pmoney end) as '对外金额', 23 inday 24 from ac 25 group by channelid,inday 26 ) a join 27 ( 28 select channelid, 29 max(case ptype when 1 then pcount end) as '请求数', 30 max(case ptype when 2 then pcount end) as '成功数', 31 max(case ptype when 1 then pmoney end) as '对内金额', 32 max(case ptype when 2 then pmoney end) as '对外金额', 33 inday 34 from bc 35 group by channelid,inday 36 ) b on a.channelid=b.channelid and a.inday=b.inday
