php - 如何在数据库搜索中获取单行数据

Question

这个问题已经有答案了:

How can I return pivot table output in MySQL? (10 个回答)

已关闭 7 年前。

我想从我的数据库桌面游戏中获取数据。我搜索为

SELECT team_id,result from games where team_id=17;

在游戏表中，我有所有比赛的结果，例如

id     team_id team    result
21       17    India   w
22       17    India   l
23       17    India   l
24       17    India   w

我想将这些数据获取为

team_id  result1 result2 result3 result4
  17      w      l      l       w

如果有人知道我该怎么做？

Answer 1

对于有限数量的结果，例如最多 4 个，您可以执行以下操作:

SELECT team_id, 
       MAX(CASE WHEN rn = 1 THEN result END) AS result1, 
       MAX(CASE WHEN rn = 2 THEN result END) AS result2, 
       MAX(CASE WHEN rn = 3 THEN result END) AS result3, 
       MAX(CASE WHEN rn = 4 THEN result END) AS result4 
FROM (
  SELECT team_id, result,
         @row_number := @row_number + 1 AS rn
  FROM games 
  CROSS JOIN (SELECT @row_number := 0) AS vars
  WHERE team_id = 17
  ORDER BY id) AS t