为什么此MySQL查询的结果彼此相乘? [英] Why do the results of this MySQL query get multiplied by each other?

问题描述

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和num_comments变量显示正确的结果，而total_points和month_points则显示month_points/total_points，live_submissions和num_comments的乘积.知道为什么会这样吗?

Basically live_submissions and num_comments variable display proper results, while the total_points and month_points display a product of month_points/total_points, live_submissions and num_comments. Any idea why this is happening?

推荐答案

这称为笛卡尔积.将表连接在一起时，默认结果是行的每个排列，其连接条件为真.您使用JOIN条件来限制这些排列.

This is called a Cartesian Product. When you join the tables together, the default result is every permutation of rows for which the join conditions are true. You use JOIN conditions to limit these permutations.

但是，由于您要将多个表联接到users，因此结果包括每个匹配表的每个排列.例如，points中的每个匹配行在comments中的每个匹配行中重复，并且每个都再次相乘，在rants中的每个匹配行中重复.

But since you are joining multiple tables to users, the result includes every permutation of each matching table. For example, each matching row in points is repeated per matching row in comments, and each of these is multiplied again, repeating per matching row in rants.

您可以在执行操作时使用COUNT(DISTINCT c_id)部分补偿此问题，但是DISTINCT是必需的，因为每个c_id有多个行.除非您将其应用于唯一值，否则它将不起作用.此补救措施不适用于SUM()表达式.

You can partially compensate for this with COUNT(DISTINCT c_id) as you are doing, but the DISTINCT is necessary only because you have multiple rows per c_id. And it doesn't work unless you apply it to unique values. This remedy doesn't work for the SUM() expressions.

基本上，您正在尝试在一个查询中进行过多的计算.您需要将其拆分为单独的查询，以确保其可靠性.然后，您也可以摆脱DISTINCT修饰符.

Basically, you're trying to do too many calculations in one query. You need to split it up into separate queries for it to be reliable. And then you can get rid of the DISTINCT modifiers, too.

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;

您不应尝试在单个查询中完成所有这三个操作.

You shouldn't try to do all three of these in a single query.

这篇关于为什么此MySQL查询的结果彼此相乘?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！