mysql - 有没有办法在 SQL 中获得以下输出

Question

我有一个表(product_tb):

  id   product   unit   quantity
 ---- --------- ------ ----------
   1   A         1kg         100
   2   B         1kg          50
   3   A         5kg          50

我想要输出为:

  product   1kg   2kg   5kg
 --------- ----- ----- -----
  A         100     0    50
  B          50     0     0

提前谢谢

Answer 1

是的，CASE WHEN和聚合。试试这个:

SELECT product, SUM(CASE WHEN unit='1kg' THEN quantity ELSE 0 END) AS '1kg', 
                SUM(CASE WHEN unit='2kg' THEN quantity ELSE 0 END) AS '2kg',
                SUM(CASE WHEN unit='5kg' THEN quantity ELSE 0 END) AS '5kg'
FROM YourTable
GROUP BY product, unit; 

添加总要做的事情:

SELECT product, SUM(CASE WHEN unit='1kg' THEN quantity ELSE 0 END) AS '1kg', 
                SUM(CASE WHEN unit='2kg' THEN quantity ELSE 0 END) AS '2kg',
                SUM(CASE WHEN unit='5kg' THEN quantity ELSE 0 END) AS '5kg',
                SUM(quantity) AS Total
FROM YourTable
GROUP BY product, unit;