mysql - 如何使用 PIVOT 编写此查询而不会出现“语法错误”

Question

SELECT * FROM (
SELECT id, revenue, month FROM Department)
pivot 
(MAX (revenue) 
FOR month IN  (Jan Jan_Revenue, Feb Feb_Revenue,
              Mar Mar_Revenue, Apr Apr_Revenue,
              May May_Revenue, Jun Jun_Revenue,
              Jul Jul_Revenue, Aug Aug_Revenue,
              Sep Sep_Revenue, Oct Oct_Revenue, 
              Nov Nov_Revenue, 'Dec' Dec_Revenue))
              

您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以在第 4 行的 '(revenue) FOR month IN (Jan Jan_Revenue, Feb Feb_Revenue, Mar Mar' 附近使用正确的语法

标签： mysqlsql

Answer 1

使用条件聚合：

SELECT id,
       max(case when month = 'Jan' then revenue end) as jan_revenue,
       max(case when month = 'Feb' then revenue end) as feb_revenue,
       . . . 
FROM Department d
GROUP BY id;