我有以下SQL查询。
SELECT u.username, SUM(p.points) AS points,
SUM(sp.spPoints) AS spPoints,
(SUM(sp.spPoints) - SUM(p.points)) AS Puntos_Restantes
FROM users as u
LEFT JOIN points as p ON (u.userid = p.userid)
LEFT JOIN sppoints AS sp ON (u.userid = sp.userid)
WHERE u.userid = '1'
GROUP BY u.userid
我的目标是对 2 个字段求和,然后减去它们,但是当我执行上述查询时,第二个 SUM 是错误的。
表格如下所示:
points: pointId, userId, points
sppoints: spPointId, userId, spPoints
在点数中,我有这个数量:25,在spPoints:10但是当我运行查询时,我得到:
points spPoints Puntos_Restantes
25 30 5
这里出了什么问题?
表users
与其他两个表具有一对多关系。这会导致 2 个连接生成一个迷你 Carstesian 积,并在points
列中生成具有相同数据的多行 - 然后聚合这些行。
可以使用子查询进行分组,然后联接,以避免此问题:
SELECT
u.username
, COALESCE(p.pPoints,0)
AS pPoints
, COALESCE(sp.spPoints,0)
AS spPoints
, COALESCE(p.pPoints,0) - COALESCE(sp.spPoints,0)
AS Puntos_Restantes
FROM
users as u
LEFT JOIN
( SELECT userid, SUM(points) AS pPoints
FROM points
WHERE userid = 1
GROUP BY userid
) AS p
ON u.userid = p.userid
LEFT JOIN
( SELECT userid, SUM(spPoints) AS spPoints
FROM sppoints
WHERE userid = 1
GROUP BY userid
) AS sp
ON u.userid = sp.userid
WHERE u.userid = 1 ;
如果要为多个用户(或所有用户)提供结果,请替换三个WHERE userid=1
条件(或完全删除它们)。
points(userid, points)
和sppoints(userid, spPoints)
指数将有助于提高效率。