SELECT user_id,
SUM(COALESCE(point_points, 0)) AS total_points,
SUM(
CASE
WHEN point_date > '$this_month'
THEN point_points
ELSE 0
END) AS month_points,
COUNT(DISTINCT c_id) AS num_comments,
COUNT(DISTINCT rant_id) AS live_submissions
FROM users
LEFT JOIN points
ON users.user_id = points.point_userid
LEFT JOIN comments
ON
(
c_userid = user_id
)
LEFT JOIN rants
ON
(
rant_poster = user_id
AND rant_status = 1
)
WHERE user_id = $id
GROUP BY user_id
基本上live_submissions
and num_comments
变量显示正确的结果,而total_points
and month_points
显示产品month_points/total_points
, live_submissions
and num_comments
。知道为什么会发生这种情况吗?
这被称为笛卡尔积 http://en.wikipedia.org/wiki/Cartesian_product。当您将表连接在一起时,默认结果是每个行的排列其连接条件为真。你用JOIN
限制这些排列的条件。
但是由于您要将多个表连接到users
,结果包括每个匹配表的每个排列。例如,每个匹配行points
中的每个匹配行重复comments
,并且每一个都再次相乘,对每个匹配行重复rants
.
您可以通过以下方式部分补偿COUNT(DISTINCT c_id)
正如你所做的那样,但是DISTINCT
是必要的,只是因为每个有多个行c_id
。除非您将其应用于独特的值,否则它不会起作用。这个补救措施对以下情况不起作用SUM()
表达式。
基本上,您试图在一个查询中执行太多计算。您需要将其拆分为单独的查询才能保证其可靠性。然后你就可以摆脱DISTINCT
修饰符也是如此。
SELECT u.user_id, SUM(COALESCE(p.point_points, 0)) AS total_points,
SUM( CASE WHEN p.point_date > '$this_month' THEN p.point_points ELSE 0 END ) AS month_points
FROM users u LEFT JOIN points p
ON u.user_id = p.point_userid
WHERE u.user_id = $id
GROUP BY u.user_id;
SELECT user_id, COUNT(c.c_id) as num_comments,
FROM users u LEFT JOIN comments c
ON (c.c_userid = u.user_id)
WHERE u.user_id = $id
GROUP BY u.user_id;
SELECT u.user_id, COUNT(r.rant_id) as live_submissions
FROM users u LEFT JOIN rants r
ON (r.rant_poster = u.user_id AND r.rant_status = 1)
WHERE u.user_id = $id
GROUP BY u.user_id;
您不应该尝试在单个查询中完成所有这三个操作。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)