mysql - SQL Crosstab 月行作为列？

Question

早上好，我尝试使用 mysql/php 创建报告失败，我希望将行作为类似于 Access Crosstab 或 Excel Pivot 的列。我有销售人员和月销售额，我想在整个页面上显示，月份作为列标题。

我下面的查询输出了数据，但是每个销售人员每个月都有一行，读起来不太好，

      Jan | Feb | Mar | April

销售 1
销售2
销售3
销售4

Select
  tblcontacts.ContactFullName,
  Count(tblcases.CaseID) As cases,
  MonthName(tblcases.CaseDate) As Monthly
From
  tblcases Inner Join
  tblcontacts On tblcases.ContactAssignedTo =
  tblcontacts.ContactID
Group By
  tblcontacts.ContactFullName,
  MonthName(tblcases.CaseDate)  
  with rollup

感谢 Ant 的建议或指点，我已经研究过了，但其中大部分都超出了我的理解范围!

亲切的问候

Answer 1

您可以做的只是简单地按每个联系人分组并使用条件聚合来计算基于每个月的行数:

SELECT
    a.ContactFullName,
    SUM(MONTH(b.CaseDate) = 1) AS Jan,
    SUM(MONTH(b.CaseDate) = 2) AS Feb,
    SUM(MONTH(b.CaseDate) = 3) AS Mar,
    SUM(MONTH(b.CaseDate) = 4) AS Apr,
    SUM(MONTH(b.CaseDate) = 5) AS May,
    SUM(MONTH(b.CaseDate) = 6) AS Jun,
    SUM(MONTH(b.CaseDate) = 7) AS Jul,
    SUM(MONTH(b.CaseDate) = 8) AS Aug,
    SUM(MONTH(b.CaseDate) = 9) AS Sep,
    SUM(MONTH(b.CaseDate) = 10) AS Oct,
    SUM(MONTH(b.CaseDate) = 11) AS Nov,
    SUM(MONTH(b.CaseDate) = 12) AS Dec
FROM
    tblcontacts a
INNER JOIN
    tblcases b ON a.ContactID = b.ContactAssignedTo
GROUP BY
    a.ContactFullName

编辑:根据您对此答案的评论:要获取每个月的价格总和，您可以执行以下操作:

SELECT
    a.ContactFullName,
    SUM(IF(MONTH(b.CaseDate) = 1, b.price, 0)) AS Jan,
    SUM(IF(MONTH(b.CaseDate) = 2, b.price, 0)) AS Feb,
    SUM(IF(MONTH(b.CaseDate) = 3, b.price, 0)) AS Mar,
    SUM(IF(MONTH(b.CaseDate) = 4, b.price, 0)) AS Apr,
    SUM(IF(MONTH(b.CaseDate) = 5, b.price, 0)) AS May,
    SUM(IF(MONTH(b.CaseDate) = 6, b.price, 0)) AS Jun,
    SUM(IF(MONTH(b.CaseDate) = 7, b.price, 0)) AS Jul,
    SUM(IF(MONTH(b.CaseDate) = 8, b.price, 0)) AS Aug,
    SUM(IF(MONTH(b.CaseDate) = 9, b.price, 0)) AS Sep,
    SUM(IF(MONTH(b.CaseDate) = 10, b.price, 0)) AS Oct,
    SUM(IF(MONTH(b.CaseDate) = 11, b.price, 0)) AS Nov,
    SUM(IF(MONTH(b.CaseDate) = 12, b.price, 0)) AS Dec
FROM
    tblcontacts a
INNER JOIN
    tblcases b ON a.ContactID = b.ContactAssignedTo
GROUP BY
    a.ContactFullName

基本上，对于每一行，如果 casedate 在特定月份，则将 price 列的值传递给 SUM 聚合，否则，只需传递它 0。