mysql - 在 MySQL 查询中附加来自另一个 JOIN 语句的新字段

Question

所以，我有 3 张 table :

guest : 
id_guest | name
1        | John
2        | Nick
3        | James
4        | Paul
5        | Chris
6        | Karen
7        | Peter

room :  
id_room  | status
1        | Clean
2        | Dirty
3        | Dirty
4        | Clean
5        | Clean
6        | Clean

reservation : 
id_guest | id_room  | date
1        | 1        | 2015-04-15
1        | 1        | 2015-04-16
1        | 1        | 2015-04-17
2        | 3        | 2015-04-15
3        | 4        | 2015-04-15
3        | 4        | 2015-04-16
4        | 2        | 2015-04-16
5        | 2        | 2015-04-17
6        | 2        | 2015-04-18

这就是预期的输出:

id_room  | status   | d04-15    | d04-16    | d04-17    | d04-18
1        | Clean    | John      | John      | John      |
2        | Dirty    |           | Paul      | Chris     | Karen
3        | Dirty    | Nick      |           |           |
4        | Clean    | James     | James     |           |
5        | Clean    |           |           |           |
6        | Clean    |           |           |           |

尽管使用日期作为值，但我已经能够将其显示到第三个字段(d04-15):

SELECT room.id_room,
       room.status,
       reservation.date AS d04-15
FROM room
LEFT JOIN reservation
ON room.id_room = reservation.id_room AND reservation.date = '2015-04-15'
GROUP BY room.id_room

但我不确定如何在那里显示名称并且从另一个 JOIN 语句附加新字段(d04-16、d04-17 和 d04-18)。

如有任何帮助，我们将不胜感激。谢谢。

Answer 1

查询返回的列在运行时无法更改；它们必须在 SQL SELECT 语句中静态声明。

下面是可以实现指定结果的语句示例:

SELECT m.id_room
     , m.status
     , MAX(IF(r.date='2015-04-15',g.name,NULL)) AS `d04-15`
     , MAX(IF(r.date='2015-04-16',g.name,NULL)) AS `d04-16`
     , MAX(IF(r.date='2015-04-17',g.name,NULL)) AS `d04-17`
     , MAX(IF(r.date='2015-04-18',g.name,NULL)) AS `d04-18`
  FROM room m
  LEFT
  JOIN reservation r
    ON r.id_room = m.id_room
   AND r.date IN ('2015-04-15','2015-04-16','2015-04-17','2015-04-18')
  LEFT
  JOIN guest g
    ON g.id_guest = r.id_guest
 GROUP BY m.id_room