mysql - 使用连接表进行分组并计算总和

Question

我有这样的table1:

ID uid jid type val
1   1   1   1   1
2   1   1   1   10
3   1   2   1   100
4   1   3   1   1000
5   1   4   2   2

并加入table2:

ID uid jid stat time
1   1   1   1   100
2   1   1   1   200
3   1   1   4   300
4   1   2   2   400

我尝试从 table1 获取 val 的 SUM，按 uid 和类型对其进行分组，并通过以下查询连接 table2:

SELECT a.uid, a.type, SUM(a.val) as t1, SUM(a.val)*COUNT(distinct(a.id))/COUNT(a.id) as t2, MAX(b.time) as max_time
FROM table1 as a
LEFT JOIN table2 as b on b.uid = a.uid and b.jid = a.jid and b.stat = 1
GROUP BY a.uid, a.type

结果我得到这个值:

uid type t1     t2     max_time
1   1   1122  748.0000  200
1   2      2    2.0000  NULL

但是 type=1 的总数应该是:1111(不是 1122 也不是 748)

请告诉我我做错了什么。

Answer 1

您需要在 select 语句中将 DISTINCT 与 a.uid 一起使用。更正后的查询如下:

选择 DISTINCT(a.uid)、a.type、SUM(a.val) AS t1、SUM(a.val)*COUNT(DISTINCT(a.id))/COUNT(a.id) AS t2、 MAX(b.time) AS max_timeFROM 表 1 AS aLEFT JOIN table2 AS b ON b.uid = a.uid AND b.jid = a.jid AND b.stat = 1GROUP BY a.uid, a.type