mysql - 如何使用 NULL 创建 MySQL 交叉表

Question

我有一个包含职位、公司和薪水的数据库，其中包含每个公司向每个职位的员工支付的金额。每个公司、工作和薪资都与调查相关联。我希望调查的输出如下所示:

|      |Company A|Company B|Company C|
+------+---------+---------+---------+
|Job A |  34000  |  36750  |  41000  |
|Job B |  65880  |         |  67000  |
|Job C |         |  52340  |  49000  |

空单元格是“薪资”表中没有与公司和职位相匹配的条目的单元格。

我无法理解我在网上看到的交叉表示例。我想我必须生成一个数据集，其中包含每个组合的一行，包括空值，如下所示:

Company A | Job A | 34000
Company A | Job B | 65880
Company A | Job C | NULL
Company B | Job A | 36750
Company B | Job B | NULL
Company B | Job C | 52340
Company C | Job A | 41000
Company C | Job B | 67000
Company C | Job C | 49000

我正在运行此查询来生成上述行，并且运行正常:

SELECT C.companyname, J.jobtitle, S.salary FROM Companies C INNER JOIN Jobs J 
  ON (C.surveyid=J.surveyid) LEFT JOIN Salaries S
  ON (J.jobid=S.jobid and C.companyid=S.companyid)
  ORDER BY C.companyname, J.jobtitle

您可以在这里看到:http://sqlfiddle.com/#!2/91e49/12/0

现在我获得了正确的数据，如何将其转换为交叉表？

Answer 1

您可以在将表连接在一起后使用聚合显式创建交叉表。这使用条件聚合(即嵌套在聚合函数中的 case 语句)。

当您知道列的职位名称时，以下操作有效:

SELECT C.companyname,
       max(case when jobtitle = 'Job A' then S.salary end) as JobA,
       max(case when jobtitle = 'Job B' then S.salary end) as JobB,
       max(case when jobtitle = 'Job C' then S.salary end) as JobC
FROM Companies C INNER JOIN
     Jobs J 
     ON C.surveyid = J.surveyid JOIN
     Salaries S
     ON J.jobid = S.jobid and C.companyid = S.companyid
GROUP BY C.companyname;

如果您想编写更通用的内容，从 Jobs 表中读取职位名称，那么您必须将查询构造为字符串并使用准备语句来执行它。