时间戳

首页 > 解决方案 > 具有特定条件的多个 GROUP BY

mysql - 具有特定条件的多个 GROUP BY

问题描述

这是我正在使用的数据集的示例：

+------------+----------+--------+
| vegetables | basket   | amount |
+------------+----------+--------+
| tomatoes   | basketA  |   11   |
| cabbage    | basketB  |   21   |
| carrot     | basketC  |   12   |
| beans      | basketD  |   23   |
| cabbage    | basketD  |   13   |
| tomatoes   | basketB  |   23   |  
| beans      | basketB  |   13   |
| carrot     | basketA  |   13   |
+------------+----------+--------+

我在从数据中进行查询时遇到问题，以便它可以按如下方式显示表格。也许使用 GROUP BY 但我无法编译它

+------------+----------+---------+----------+---------+---------+
| vegetables | basketA  | basketB | basketC  | basketD | total   |
+------------+----------+---------+----------+---------+---------+
| tomatoes   |    11    |   23    |          |         |   34    |
| cabbage    |          |   21    |          |   13    |   34    |
| carrot     |    13    |         |    12    |         |   35    |
| beans      |          |   13    |          |   23    |   36    |
+------------+----------+---------+----------+---------+---------+

标签： mysql

解决方案

这是旋转的经典案例：

SELECT vegetables,
  SUM (CASE WHEN basket = 'basketA' THEN amount ELSE 0 END) AS "basketA",
  SUM (CASE WHEN basket = 'basketB' THEN amount ELSE 0 END) AS "basketB",
  SUM (CASE WHEN basket = 'basketC' THEN amount ELSE 0 END) AS "basketC",
  SUM (CASE WHEN basket = 'basketD' THEN amount ELSE 0 END) AS "basketD",
  SUM (amount) AS "total"
FROM your_table
GROUP BY vegetables

推荐阅读