时间戳

首页 > 解决方案 > 我如何在 join 和 union 中使用 case？

mysql - 我如何在 join 和 union 中使用 case？

问题描述

我有 2 张桌子：

火柴

CREATE TABLE rounds (
  `roundid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `roundname` VARCHAR(45) NULL);

回合

CREATE TABLE matches (
  `matchid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `matchwinner` VARCHAR(45) NULL,
  `roundid` INT NOT NULL,
  CONSTRAINT `fk_matches_rounds`
    FOREIGN KEY (`roundid`)
    REFERENCES `mydb`.`rounds` (`roundid`)
    ON DELETE CASCADE
    ON UPDATE CASCADE);

插入物

INSERT INTO rounds (`roundid`, `roundname`) VALUES (1, 'Final');
INSERT INTO rounds (`roundid`, `roundname`) VALUES (2, '3rd place');
INSERT INTO matches (`matchid`, `matchwinner`, `roundid`) VALUES (1, 'fnatic', 1);
INSERT INTO matches (`matchid`, `matchwinner`, `roundid`) VALUES (2, 'astralis', 1);
INSERT INTO matches (`matchid`, `matchwinner`, `roundid`) VALUES (3, 'fnatic', 2);
INSERT INTO matches (`matchid`, `matchwinner`, `roundid`) VALUES (4, 'fnatic', 2);
INSERT INTO matches (`matchid`, `matchwinner`, `roundid`) VALUES (5, 'astralis', 1);
INSERT INTO matches (`matchid`, `matchwinner`, `roundid`) VALUES (6, 'astralis', 1);

我的查询：

SELECT 
    m.matchid,
    m.matchwinner Team,
    CASE r.roundname
        WHEN r.roundname = 'Final' THEN '1st'
        ELSE '2'
    END Place
FROM
    matches m
        JOIN rounds r ON m.roundid = r.roundid
WHERE
    r.roundname = 'Final' 
UNION SELECT 
    m.matchid,
    m.matchwinner Team,
    CASE r.roundname
        WHEN r.roundname = '3rd place' THEN '3rd'
        ELSE '4th'
    END Place
FROM
    matches m
        JOIN rounds r ON m.roundid = r.roundid
WHERE
    r.roundname = '3rd place';

我想知道

MatchID - 球队 - 排名
1 - fnatic - 1st
3 - fnatic - 3rd
4 - fnatic - 3rd

MatchID - Team - Place
1 - fnatic - 1st
2 - astralis - 1st
3 - fnatic - 3rd
4 - fnatic - 3rd
依此类推

不明白为什么我的查询不起作用？

标签： mysqlsqljoinselect

解决方案

这看起来像一个连接和条件逻辑：

select 
    m.matchid,
    m.matchwinner team,
    case r.roundname
        when 'Final'     then '1st'
        when '3rd place' then '3rd'
    end place
from rounds r
inner join matches m on m.roundid = r.roundid
where m.matchwinner = 'fnatic' 
order by m.matchid

要为所有团队获得相同的结果，只需删除该where子句。

DB Fiddle 上的演示：

匹配ID | 团队 | 地方
------: | :----- | :----
      1 | fnatic | 第一  
      3 | fnatic | 第三  
      4 | fnatic | 第三

推荐阅读