mysql - MYSQL将两个结果行合并((失败和获胜的团队)成一列

我有四列，我想按年合并，按年份分组game_dt

  GAME_DT     YEAR    WIN    LOSS
  -------------------------------
  20040804    2004    NYN   MIL
  20040804    2004    MIN   ANA
  20040804    2004    NYA   OAK

输出量

  GAME_DT     YEAR  TEAM   RESULT
  --------------------------------
  20040804    2004    NYN   W
  20040804    2004    MIL   L
  20040804    2004    MIN   W
  20040804    2004    ANA   L
  20040804    2004    NYA   W
  20040804    2004    OAK   L

更新：
我的代码，然后是Nitin：

 **

    SELECT
    GAME_DT,GAME_ID,
    YEAR_ID,
    if(home_score_ct>away_score_ct,home_team_id,away_team_id) as Team,
    'W' as result
FROM
    games
UNION
SELECT
    GAME_DT,game_id,
    YEAR_id,
    if(home_score_ct<away_score_ct,home_team_id,away_team_id) as Team,
    'L' as result
FROM
    games
     group by year_id,game_dt,game_id,result
     order by game_dt,game_id

工作正常！谢谢Nitin让我朝正确的方向前进。

最佳答案

您可以使用UNION获得结果。

SELECT
    GAME_DT,
    YEARS,
    WIN,
    'W'
FROM
    game_dt
UNION
SELECT
    GAME_DT,
    YEARS,
    LOSS,
    'L'
FROM
    game_dt

关于mysql - MYSQL将两个结果行合并((失败和获胜的团队)成一列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/44533337/