MySQL - 连接表时出现错误 #1055

Question

我有以下表格：

1. 游戏：

   +----+-----------------------------------+
   | id | title                             |
   +----+-----------------------------------+
   |  1 | The Witcher                       |
   |  2 | The Witcher 2: Assassins of Kings |
   |  3 | The Witcher 3: Wild Hunt          |
   +----+-----------------------------------+
   

2. 平台：

   +----+------+
   | id | name |
   +----+------+
   |  1 | PC   |
   |  2 | MAC  |
   |  3 | X360 |
   |  4 | PS3  |
   |  5 | XONE |
   |  6 | PS4  |
   +----+------+
   

3. game_platform

   +----+---------+-------------+
   | id | id_game | id_platform |
   +----+---------+-------------+
   |  1 |       1 |           1 |
   |  2 |       1 |           2 |
   |  3 |       2 |           1 |
   |  4 |       2 |           2 |
   |  5 |       2 |           3 |
   |  6 |       3 |           1 |
   |  7 |       3 |           5 |
   |  8 |       3 |           6 |
   +----+---------+-------------+
   

因此，我想要这样的东西：

    +-----------------------------------+----+-----+------+-----+------+-----+
    | game                              | PC | MAC | X360 | PS3 | XONE | PS4 |
    +-----------------------------------+----+-----+------+-----+------+-----+
    | The Witcher 3: Wild Hunt          |  x |     |      |     |   x  |  x  |
    +-----------------------------------+----+-----+------+-----+------+-----+

我使用的查询是：

SELECT g.title as 'Title',
IF (gp.id_platform = 1, 'x', '') as 'PC',
IF (gp.id_platform = 2, 'x', '') as 'MAC',
IF (gp.id_platform = 3, 'x', '') as 'X360',
IF (gp.id_platform = 4, 'x', '') as 'PS3'
IF (gp.id_platform = 5, 'x', '') as 'XONE'
IF (gp.id_platform = 6, 'x', '') as 'PS4'
FROM game g LEFT JOIN (platform pl, game_platform gp) ON (g.id = gp.id_game and pl.id = gp.id_platform)
WHERE g.id = 1;

一切都很好，除了数据是这样呈现的：

    +-----------------------------------+----+-----+------+-----+------+-----+
    | game                              | PC | MAC | X360 | PS3 | XONE | PS4 |
    +-----------------------------------+----+-----+------+-----+------+-----+
    | The Witcher 3: Wild Hunt          |  x |     |      |     |      |     |
    | The Witcher 3: Wild Hunt          |    |     |      |     |   x  |     |
    | The Witcher 3: Wild Hunt          |    |     |      |     |      |  x  |
    +-----------------------------------+----+-----+------+-----+------+-----+

当我在末尾添加GROUP BY子句时：

SELECT g.title as 'Title',
IF (gp.id_platform = 1, 'x', '') as 'PC',
IF (gp.id_platform = 2, 'x', '') as 'MAC',
IF (gp.id_platform = 3, 'x', '') as 'X360',
IF (gp.id_platform = 4, 'x', '') as 'PS3'
IF (gp.id_platform = 5, 'x', '') as 'XONE'
IF (gp.id_platform = 6, 'x', '') as 'PS4'
FROM game g INNER JOIN (platform pl, game_platform gp) ON (g.id = gp.id_game and pl.id = gp.id_platform)
WHERE g.id = 1
GROUP BY g.title;

我收到错误：

#1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db_klimos.gp.id_platform' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

如果我将gp.id_platform添加到GROUP BY子句中，我将得到初始结果，就好像数据没有分组一样。

有没有一种方法可以在不更改 sql_mode=only_full_group_by 的情况下对表中的数据进行分组？我已经知道这个选项意味着什么以及为什么引入它。我正在使用的数据库不是自托管数据库，因此无论如何我都无法将其关闭。

Answer 1

使用CASE而不是IF并选择MAX值以获得所需的结果

试试这个

SELECT g.title as 'Title',
MAX(CASE WHEN gp.id_platform = 1  then  'x' ELSE '' END) as 'PC',
MAX(CASE WHEN gp.id_platform = 2 then  'x' ELSE '' END) as 'MAC',
MAX(CASE WHEN gp.id_platform = 3 then  'x' ELSE '' END) as 'X360',
MAX(CASE WHEN gp.id_platform = 4 then  'x' ELSE '' END) as 'PS3',
MAX(CASE WHEN gp.id_platform = 5 then  'x' ELSE '' END) as 'XONE',
MAX(CASE WHEN gp.id_platform = 6 then  'x' ELSE '' END) as 'PS4'
FROM game g LEFT JOIN (platform pl, game_platform gp) ON (g.id = gp.id_game and pl.id = gp.id_platform)
WHERE g.id = 1