mysql - 子查询还是加入 MySQL？

Question

table 上联赛

team_id name        wins    losses  played  recorded    created
1       dodgers     10      4       14      1364790000  1353215830
2       angels      9       6       15      1364790000  1353661376
3       pirates     12      3       15      1364790000  1353543466

team_id name        wins    losses  played  recorded    created
1       dodgers     22      9       31      1367274480  1353215830
2       angels      14      17      31      1367274480  1353661376
3       pirates     19      13      32      1367274480  1353543466
4       yankees     10      9       19      1367274480  1365577298
5       brewers     7       11      18      1367274480  1365394448

希望结果为:

team_id name        wins    losses  played
1       dodgers     12      5       17      
2       angels      5       11      16
3       pirates     7       10      17
4       yankees     10      9       19
5       brewers     7       11      18

我已经尝试了几个连接查询，但都没有成功。每天球队、胜利、失败和比赛都会被记录下来，并在记录栏上加盖时间戳。该团队是在创建的列上创建的。 (所有 unix 时间戳)我正在尝试的 2 个日期之间有几行，但我不需要它们用于此查询。

我想做的是为现有和新团队获取四月的赢/输/玩，我尝试了几个查询，这里有几个没有给我想要的结果:

SELECT a.name as name, a.wins-b.wins as wins, a.losses-b.losses as losses, a.played-b.played as played from league a join league b on a.id=b.id where a.recorded= 1367274480 and b.recorded= 1364790000

和

SELECT new.*, new.wins-old.wins as newwins, new.losses-old.losses as newlosses FROM league new LEFT JOIN league old ON new.id=old.id WHERE (new.recorded=1367274480 and old.recorded=1364790000) or (new.created > 1364790000 and new.recorded=1367274480) GROUP BY new.id

Answer 1

您希望后面的记录的每一行和前面的记录的行数相同，因此您需要使用 LEFT JOIN 为两个日期之间创建的团队获取 NULL，但是 WHERE recorded 条件应将较小的表移动为连接的 ON 条件。

还要记住 2-NULL = NULL，因此您需要使用 coalesce() 将 NULL 更改为 0。

SELECT a.name AS name, 
       a.wins - COALESCE( b.wins, 0 ) AS wins, 
       a.losses - COALESCE( b.losses, 0 ) AS losses, 
       a.played - COALESCE( b.played, 0 ) AS played
FROM league a LEFT JOIN league b 
ON a.team_id = b.team_id AND b.recorded =1364790000
WHERE a.recorded =1367274480