mySQL 复杂的 Join 和 evenually pivot

Question

我正在研究轮类安排解决方案，需要您的帮助。数据库布局如下所示:database-layout

我获取一个月的所有日程表的查询如下所示:

SELECT
    ca.date,
    ss.num,
    ma.name
FROM dp_dienstplan  AS dp
JOIN ma_mitarbeiter AS ma ON ma.id=dp.dp_mitarbeiter_id
JOIN ss_schienen    AS ss ON ss.id=dp.dp_schienen_id
JOIN sc_schicht     AS sc ON sc.id=ss.ss_schicht_id
RIGHT JOIN calendar AS ca ON ca.date=dp.dp_datum
WHERE
  month(ca.date)=9 AND year(ca.date)=2013

日历表是一个辅助表，其中包含 future 几年的日期，以便在没有待定计划的结果中获得偶数天数。

查询结果如下所示:

date       |num        |name
----------------------------
2013-09-01  NULL        NULL
2013-09-02  2           foo
2013-09-02  3           bar
2013-09-03  2           uncle
2013-09-03  1           mac
2013-09-03  7           super
2013-09-04  NULL        NULL
2013-09-04  2           master
.
.
.
2013-09-30  NULL        NULL

我梦寐以求的结果是这样的:

date       |1num |1name |2num |2name |3num |3name. . .|7name|7num 
-----------------------------------------------------------------
2013-09-01  NULL  NULL   NULL  NULL   NULL  NULL . . . NULL  NULL
2013-09-02  NULL  NULL   2     foo    3     bar  . . . NULL  NULL
2013-09-03  1     mac    2     uncle  NULL  NULL . . . 7     super
2013-09-04  NULL  NULL   2     master NULL  NULL . . . NULL  NULL
.           .     .      .     .      .     .          .     . 
.           .     .      .     .      .     .          .     . 
.           .     .      .     .      .     .          .     . 
2013-09-30  NULL  NULL   NULL  NULL   NULL  NULL . . . NULL  NULL

每天应该有一行，其中包含名称并按“num”值的顺序排列。我希望你明白我在说什么，对不起我的英语。

我有另一个帮助表，其中包含从 1 到 200 的值，我尝试右连接它，但没有成功。

提前致谢，如果这是一个重复的问题，我很抱歉，我没有在谷歌或论坛上找到任何结果，可能是因为我不知道要搜索什么。

干杯，拉尔夫。

Answer 1

首先不要将函数应用到用于在 WHERE 子句中过滤数据的列。它阻止 MySQL 使用您可能在该列上有效地导致对表进行全面扫描的任何索引。话虽如此

WHERE month(ca.date)=9 AND year(ca.date)=2013

你最好做类似的事情

WHERE ca.date BETWEEN '2013-09-01' AND LAST_DAY('2013-09-01')

并确保您在 calendar.date 和 dp_dienstplan.dp_datum 上有索引。

---

现在如果预先知道ss.num 的所有值，您可以通过在静态查询中使用条件聚合来获取数据透视

SELECT ca.date,
       MAX(CASE WHEN ss.num = 1 THEN ss.num  END) `1num`,
       MAX(CASE WHEN ss.num = 1 THEN ss.name END) `1name`,
       MAX(CASE WHEN ss.num = 2 THEN ss.num  END) `2num`,
       MAX(CASE WHEN ss.num = 2 THEN ss.name END) `2name`,
       MAX(CASE WHEN ss.num = 3 THEN ss.num  END) `3num`,
       MAX(CASE WHEN ss.num = 3 THEN ss.name END) `3name`,
       MAX(CASE WHEN ss.num = 4 THEN ss.num  END) `4num`,
       MAX(CASE WHEN ss.num = 4 THEN ss.name END) `4name`,
       MAX(CASE WHEN ss.num = 5 THEN ss.num  END) `5num`,
       MAX(CASE WHEN ss.num = 5 THEN ss.name END) `5name`,
       MAX(CASE WHEN ss.num = 6 THEN ss.num  END) `6num`,
       MAX(CASE WHEN ss.num = 6 THEN ss.name END) `6name`,
       MAX(CASE WHEN ss.num = 7 THEN ss.num  END) `7num`,
       MAX(CASE WHEN ss.num = 7 THEN ss.name END) `7name`
  FROM dp_dienstplan  AS dp
   JOIN ma_mitarbeiter AS ma ON ma.id=dp.dp_mitarbeiter_id
   JOIN ss_schienen    AS ss ON ss.id=dp.dp_schienen_id
   JOIN sc_schicht     AS sc ON sc.id=ss.ss_schicht_id
   RIGHT JOIN calendar AS ca ON ca.date=dp.dp_datum
WHERE ca.date BETWEEN '2013-09-01' AND LAST_DAY('2013-09-01')
GROUP BY ca.date

输出:

|       DATE |   1NUM |  1NAME |   2NUM |  2NAME |   3NUM |  3NAME |   4NUM |  4NAME |   5NUM |  5NAME |   6NUM |  6NAME |   7NUM |  7NAME ||------------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|| 2013-09-01 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) || 2013-09-02 | (null) | (null) |      2 |    foo |      3 |    bar | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) || 2013-09-03 |      1 |    mac |      2 |  uncle | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |      7 |  super || 2013-09-04 | (null) | (null) |      2 | master | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) || 2013-09-30 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |

这是 SQLFiddle 演示 在此演示中，您所有的连接表都由一个表模拟，该表包含您作为查询结果显示的示例数据