sql 查询分组后再列转行统计

发布网友发布时间：2022-04-10 04:48

共2个回答

热心网友时间：2022-04-10 06:18

SELECT no1 ,sum(s_a) AS s_a,max(a) AS a,sum(s_b) AS s_b,max(b) AS b from
(SELECT no1 ,(CASE WHEN t1='a' THEN s1 ELSE 0 end) AS s_a ,
(CASE when t1='a' THEN t1 ELSE NULL end) AS a,
(CASE WHEN t1='b' THEN s1 ELSE 0 end) AS s_b ,
(CASE when t1='b' THEN t1 ELSE NULL end) AS b
FROM
(SELECT no1,sum(s1) AS s1,t1 FROM #t1 GROUP by no1,t1) AS xx) AS xx2 GROUP BY no1

热心网友时间：2022-04-10 07:36

select t1.id,t2.sc1,t2.type as type1,t3.sc2,t3.type as type2
from (select distinct no
          from test) t1
         left join
       (select no,type,sum(source) sc1
        from test
         where type=1
         group by no,type) t2 on t1.id=t2.id
         left join
      (select no,type,sum(source) sc2
        from test
         where type=2
         group by no,type
         ) t3 on t1.id=t3.id

想着直接写的，没测试，，有问题再追问吧，，，，