代码之家 › 专栏 › 技术社区 › Kavvson Empcraft

将列转换为单行并选择唯一条目

unpivot mariadb sql

Kavvson Empcraft · 技术社区 · 7 年前

当前,表如下所示。这基本上是一个排行榜条目列表,在这一点上一切看起来都很好。

+-------+-----------------+-------+--------------+--------------+--------------+--------------+----------------------+----------+
| entry | challenge_level | proof | player1_name | player2_name | player3_name | player4_name |   submission_date    | approved |
+-------+-----------------+-------+--------------+--------------+--------------+--------------+----------------------+----------+
|     8 |              52 | url   | PLAYER_A     | PLAYER_D     | PLAYER_B     | PLAYER_C     | 2018-07-16T16:14:01Z | true     |
|     9 |              60 | url   | PLAYER_C     | PLAYER_K     | PLAYER_X     | PLAYER_Y     | 2018-07-16T16:33:10Z | true     |
|    11 |              51 | url   | PLAYER_A     | PLAYER_B     | PLAYER_C     | PLAYER_D     | 2018-07-16T17:42:13Z | true     |
|    13 |              45 | url   | PLAYER_C     | PLAYER_H     | PLAYER_J     | PLAYER_D     | 2018-07-16T18:26:59Z | true     |
|    14 |              61 | url   | PLAYER_A     | PLAYER_C     | PLAYER_I     | PLAYER_B     | 2018-07-16T21:09:55Z | true     |
|    15 |              36 | url   | PLAYER_A     | PLAYER_C     | PLAYER_D     | PLAYER_B     | 2018-07-17T08:24:37Z | true     |
|    16 |              62 | url   | PLAYER_E     | PLAYER_C     | PLAYER_F     | PLAYER_G     | 2018-07-17T09:37:13Z | true     |
+-------+-----------------+-------+--------------+--------------+--------------+--------------+----------------------+----------+

当我想要将我想要的结果格式化为以下格式时,问题就开始了, 预期结果

+-------+-----------------+-------+--------------+----------------------+----------+
| entry | challenge_level | proof | PLAYER_NAME  |   submission_date    | approved |
+-------+-----------------+-------+--------------+--------------+--------------+---+
|     14 |              61  | url   | PLAYER_A     | 2018-07-16T16:14:01Z | true     |
|     14 |              61  | url   | PLAYER_B     | 2018-07-16T16:33:10Z | true     |
|   16   |              62  | url   | PLAYER_C     | 2018-07-16T17:42:13Z | true     |
|    8  |              52  | url   | PLAYER_D     | 2018-07-16T18:26:59Z | true     |
|    16  |              62  | url   | PLAYER_E     | 2018-07-16T21:09:55Z | true     |
|    16  |              62  | url   | PLAYER_F     | 2018-07-17T08:24:37Z | true     |
|    16  |              62  | url   | PLAYER_G     | 2018-07-17T09:37:13Z | true     |
+-------+-----------------+-------+--------------+--------------+--------------+---+

除此之外,还有一个重要因素需要考虑,玩家可以出现多次,例如, 玩家在多个条目中列出,但结果应为 最高挑战等级 他必须是唯一的球员的名字,不能出现多次在预期的结果。

我不知道从哪里开始,任何提示都是有价值的。

SqlFiddle >> Table Schema >>

1 回复 | 直到 7 年前

Alex 7 年前

http://sqlfiddle.com/#!9/869812/21

SELECT l.`entry`,
  l.`challenge_level`,
  l.`proof`,
  l_max.player,
  l.`submission_date`,
  l.`approved`
FROM leaderboards l
INNER JOIN
(SELECT player, MAX(challenge_level) as `level`
FROM (SELECT l1.`entry`,
  l1.`challenge_level`,
  l1.`proof`,
  l1.`player1_name` as player,
  l1.`submission_date`,
  l1.`approved`
FROM leaderboards l1
UNION ALL
SELECT l2.`entry`,
  l2.`challenge_level`,
  l2.`proof`,
  l2.`player2_name`,
  l2.`submission_date`,
  l2.`approved`
FROM leaderboards l2
UNION ALL
SELECT l3.`entry`,
  l3.`challenge_level`,
  l3.`proof`,
  l3.`player3_name`,
  l3.`submission_date`,
  l3.`approved`
FROM leaderboards l3
UNION ALL
SELECT l4.`entry`,
  l4.`challenge_level`,
  l4.`proof`,
  l4.`player4_name`,
  l4.`submission_date`,
  l4.`approved`
FROM leaderboards l4
) l_all
GROUP BY l_all.player) l_max
ON l.challenge_level = l_max.level
  and (l.player1_name = l_max.player
       OR l.player2_name = l_max.player
       OR l.player3_name = l_max.player
       OR l.player4_name = l_max.player)