mysql - 将这两个查询组合成一个 JOIN？

Question

这两个查询有效:

SELECT
u.id,
u.email,
COUNT(*) as Current
FROM
users u,
rounds cur
WHERE
cur.user = u.id
AND u.email = 'foo@foo.com'
GROUP BY cur.user;

SELECT
u.id,
u.email,
COUNT(*) as old
FROM
users u,
rounds__20120311_010951 old
WHERE
old.user = u.id
AND u.email = 'foo@foo.com'
GROUP BY old.user;

但我真的很想这样做:

SELECT
u.id,
u.email,
COUNT(old.*) as March11,
COUNT(cur.*) as Current
FROM
users u,
rounds cur,
rounds__20120311_010951 old
WHERE
old.user = u.id
AND cur.user = u.id
AND u.email = 'foo@foo.com'
GROUP BY old.user, cur.user

我认为语法错误来自这个 COUNT(old.*) 但我不确定。

是否可以组合这些查询？

Answer 1

使用 GROUP BY u.id, u.email。然后，在您的选择中，使用 COUNT(old.id) 和 COUNT(cur.id)。