mysql - 转置 mysql 查询

Question

我见过很多类似问题的答案，但没有一个问题得到解释，因此我无法理解它并用于我的案例。

SELECT st.name, SUM(sa.val), sa.sale_date FROM sales sa
INNER JOIN employee e ON sa.employee_id
INNER JOIN store st ON e.store_id
GROUP BY st.name, sa.sale_date

考虑以下查询结果:

http://sqlfiddle.com/#!9/0faa35/5

+---------+-------------+------------------------+
|  name   | SUM(sa.val) |       sale_date        |
+---------+-------------+------------------------+
| Store 1 |         800 | July, 29 2015 00:00:00 |
| Store 1 |         700 | July, 30 2015 00:00:00 |
| Store 2 |         800 | July, 29 2015 00:00:00 |
| Store 2 |         700 | July, 30 2015 00:00:00 |
+---------+-------------+------------------------+

我需要对其进行转置(对商店名称进行分组)，使其变为:

+------------+---------+---------+
|    Date    | Store 1 | Store 2 |
+------------+---------+---------+
| 2015-07-29 |     800 |     800 |
| 2015-07-30 |     700 |     700 |
+------------+---------+---------+

Answer 1

这是一个基本的数据透视查询。在MySQL中，最简单的方法是条件聚合。给定 SQL Fiddle 中的查询，逻辑是:

SELECT sa.sale_date,
       SUM(CASE WHEN st.name = 'Store 1' THEN val ELSE 0 END) as Store1,
       SUM(CASE WHEN st.name = 'Store 2' THEN val ELSE 0 END) as Store2
FROM sales sa INNER JOIN
     employee e
     ON sa.employee_id INNER JOIN
     store st
     ON e.store_id
GROUP BY sa.sale_date;

注意:您应该将实际查询放入问题中。

Here是一个 SQL Fiddle。