sql语句，多列分类汇总问题？？？

[iswjh]

假设表：
支付类型 商品 金额
微信 商品1 80
支付宝 商品2 120
支付宝 商品1 80
微信 商品1 60

如何用GROUP BY生成结果，如下：
商品 金额 微信 支付宝
商品1 220 140 80

[zrssweet]

SELECT TABLE.商品, sum(TABLE.金额) as 金额 ,sum(TABLE2.金额) as 微信,sum(TABLE3..金额) as 支付宝 FROM TABLE LEFT JOIN TABLE AS TABLE2 on TABLE2.商品=TABLE.商品 and TABLE2.支付类型=‘微信’ LEFT JOIN TABLE AS TABLE2 on TABLE3.商品=TABLE.商品 and TABLE3.支付类型=‘支付宝’ group by TABLE.商品

[zrssweet]

第二个LEFT JOIN TABLE AS TABLE2改成LEFT JOIN TABLE AS TABLE3

[RINK_1]

if OBJECT_ID(N'TEMPDB..#T1') IS NOT NULL
DROP TABLE #T1
GO

CREATE TABLE #T1
(PAYTYPE NVARCHAR(20),
 ITEM NVARCHAR(20),
 AMOUNT INT)

INSERT INTO #T1
SELECT '微信','商品1',80 UNION ALL
SELECT '支付宝','商品2',120 UNION ALL
SELECT '支付宝','商品1',80 UNION ALL
SELECT '微信','商品1',60

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL=ISNULL(@SQL+',','')+'SUM(CASE WHEN PAYTYPE='''+PAYTYPE+''' THEN AMOUNT ELSE 0 END) AS '+PAYTYPE 
FROM (SELECT PAYTYPE FROM #T1 GROUP BY PAYTYPE) AS A

SET @SQL='SELECT ITEM,SUM(AMOUNT) AS TOTAL,'+@SQL+' FROM #T1 GROUP BY ITEM'

EXEC(@SQL)

[二月十六]

https://blog.csdn.net/sinat_28984567/article/details/79865046

[砸死牛顿的苹果]

pivot 可以

[懒笑翻]

我的是MySQL的

CREATE TABLE test( paytype VARCHAR(10),goods VARCHAR(10),money INT) ENGINE=INNODB; 

INSERT INTO test VALUES('微信','商品1',80);  
INSERT INTO test VALUES('支付宝','商品2',120);  
INSERT INTO test VALUES('支付宝','商品1',80);  
INSERT INTO test VALUES('微信','商品1',60);  


SELECT * FROM test


SELECT goods AS "商品", 
    SUM(money) AS "金额", 
    SUM(IF(paytype="微信",money,0)) AS "微信",  
    SUM(IF(paytype="支付宝",money,0)) AS "支付宝"
    
FROM test   
GROUP BY goods  

[懒笑翻]

专门为您这个问题解决写的bloghttps://blog.csdn.net/c_lanxiaofang/article/details/80223310