mysql - mysql查询中独立列的分组

Question

我正在尝试对查询结果进行分组并显示在单独的列中。

表名称:category_results

+---------+-----------------+
| cat_id  | Total           |
+---------+-----------------+
| CA001   |          150.00 | 
| CA002   |          130.00 | 
| CA002   |          200.00 |
| CA003   |           70.00 | 
| CA001   |           75.00 |
+---------+-----------------+

我想要这个结果:

+--------+--------+--------+
| CA001  | CA002  | CA003  |
+--------+--------+--------+
| 255.00 | 330.00 | 70.00  | 
+--------+--------+--------+

有什么想法吗？

Answer 1

您可以使用 SUM 结合 CASE WHEN 来实现您想要的:

SELECT SUM(t.CA001) AS CA001,
       SUM(t.CA002) AS CA002,
       SUM(t.CA003) AS CA003
FROM
(
    SELECT CASE WHEN cat_id = 'CA001' THEN Total ELSE 0 END AS CA001,
           CASE WHEN cat_id = 'CA002' THEN Total ELSE 0 END AS CA002,
           CASE WHEN cat_id = 'CA003' THEN Total ELSE 0 END AS CA003
    FROM category_results
) t

点击下面的链接即可运行演示。

SQLFiddle