sql - 如何在SQLite中减去2列？

Question

如果给出表：

games_played(ID,home_team,away_team,homescore,awayscore)

输入：

（“ 1”，“美国”，“ CAN”，“ 0”，“ 2”）

（“ 2”，“美国”，“ CAN”，“ 1”，“ 2”）

（“ 3”，“美国”，“ CAN”，“ 3”，“ 0”）

（“ 4”，“ SWE”，“ CAN”，“ 3”，“ 2”）

（“ 5”，“ CAN”，“ RUS”，“ 3”，“ 2”）

（“ 6”，“ DE”，“ RUS”，“ 0”，“ 2”）

输出应为团队：SWE，CAN

我要怎么做才能发现主场胜利多于损失的球队？我尝试这样做：

select home_team, count(*)
  from games_played
  where homescore > awayscore
  group by home_team

--

select home_team, count(*)
  from games_played
  where awayscore > homescore
  group by home_team

第一个查询应返回球队名称和他们在主场赢得的比赛数量。第二个应该是他们在主场输掉的比赛数量。此查询不起作用，我正在寻找另一种方法。我知道我不能在'where'中使用count

Answer 1

使用条件聚合：

SELECT home_team,
COUNT(CASE WHEN homescore > awayscore THEN 1 END) AS gamesWon,
COUNT(CASE WHEN awayscore > homescore THEN 1 END) AS gamesLost
FROM games_played
GROUP BY home_team

结果：





现在进一步返回仅将 gamesWon > gamesLost放入 common table expression的团队：

WITH cte AS (
SELECT home_team,
COUNT(CASE WHEN homescore > awayscore THEN 1 END) AS gamesWon,
COUNT(CASE WHEN awayscore > homescore THEN 1 END) AS gamesLost
FROM games_played
GROUP BY home_team)

SELECT * FROM cte
WHERE gamesWon > gamesLost

结果：





复制：

[DEMO HERE]

CREATE TABLE games_played (ID INT,home_team VARCHAR(20),away_team VARCHAR(20),homescore INT,awayscore INT);

/* Create few records in this table */
INSERT INTO games_played VALUES(1,'USA','CAN',0,2);
INSERT INTO games_played VALUES(2,'USA','CAN',1,2);
INSERT INTO games_played VALUES(3,'USA','CAN',3,0);
INSERT INTO games_played VALUES(4,'SWE','CAN',3,2);
INSERT INTO games_played VALUES(5,'CAN','RUS',3,2);
INSERT INTO games_played VALUES(6,'DE','RUS',0,2);


/* Verify */
-- SELECT * FROM games_played;

WITH cte AS (
SELECT home_team,
COUNT(CASE WHEN homescore > awayscore THEN 1 END) AS gamesWon,
COUNT(CASE WHEN awayscore > homescore THEN 1 END) AS gamesLost
FROM games_played
GROUP BY home_team)

SELECT * FROM cte
WHERE gamesWon > gamesLost