计算join语句中不同表中的行数 [英] Count rows from different tables in join statement

问题描述

我有 2 个表 - 评论和评分.评论表包含一列reply，它指示一条评论是否是对另一条评论的回复.评分表以comment_id, user_id, rating

I have 2 tables - comments and ratings. The comments table contains a column reply which indicates whether a comment is a reply to another comment. The ratings table contains ratings for the comments in the form of comment_id, user_id, rating

当我选择要显示的评论时，它有点复杂，所以我会尽量简化

When I am selecting comments to display it is a bit complex so I'll try to simplify as much as I can

SELECT
COALESCE(SUM(cr.vote), 0) AS rating,
COUNT(r.id) AS replies

FROM comments c 
LEFT JOIN comments_ratings cr ON c.id = cr.comment
LEFT JOIN comments r ON c.id = r.reply

WHERE c.id = 1

GROUP BY c.id;

这是测试设置

CREATE TABLE `comments` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `text` text NOT NULL,
  `author` int(10) unsigned NOT NULL,
  `time` datetime DEFAULT NULL,
  `reply` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `reply` (`reply`),
  CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`reply`) REFERENCES `comments` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

CREATE TABLE `comments_ratings` (
  `comment` int(10) unsigned NOT NULL,
  `user` int(10) unsigned NOT NULL,
  `vote` tinyint(4) NOT NULL,
  PRIMARY KEY (`comment`,`user`),
  KEY `user` (`user`),
  CONSTRAINT `comments_ratings_ibfk_1` FOREIGN KEY (`comment`) REFERENCES `comments` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  -- CONSTRAINT `comments_ratings_ibfk_2` FOREIGN KEY (`user`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8

INSERT INTO comments (id, reply, text, author) VALUES (1, null, '', 0), (null, 1, '', 0),(null, 1, '', 0),(null, 1, '', 0);
INSERT INTO comments_ratings (comment, user, vote) VALUES (1, 1, 1);

现在，如果您执行 select 语句，您将看到 rating 变为 3，即使 comments_ratings 中只有 1 条记录的值为 1.如果我添加另一个回复，它将变为 4.如果添加另一个值为 1 的 comments_ratings 记录，它将翻倍并变为 8.这是因为联接中的每一行都在复制它没有的字段中的信息.

Now if you execute the select statement you will see rating becomes 3 even though there is only 1 record in comments_ratings with value 1. If I add another reply it will become 4. If you add another comments_ratings record with value 1 it will double and become 8. That is because each row from the joins is copying information in fields it doesn't have.

你能帮我在 r 上设置连接，这样它不会使评分和回复翻倍.

Can you help me set up the join on r so that it doesn't double the rating and replies.

推荐答案

更新:尽管两个答案都是正确的，但我目前一直在用大量数据测试这个设置，而且性能几乎是糟糕的.经过简短的调查，我确定了原因 - 基本上建议的解决方案在内存中创建一个临时表，其中在每次查询时都会填充该表中的所有数据，随着这次数据量的增加也会增加，并且我在一个相当弱的服务器上''''''''''''''' 我得到了超过 5 秒的几千行查询时间.

Update: Even though both answers were correct, I have currently been testing this set up with large volumes of data and the performance is next to awful. After a short investigation I determined the reason - basically the suggested solutions create a temporary table in memory in which all data from the table is filled on each query, as the amount of data increases this time also increases and on a fairly weak server that I'm running I get a query time of above 5 seconds for a couple thousand rows.

我为这个问题想出了一个解决方案，它仍然使用临时表，但不是将整个表复制到临时表中，而是只复制正在选择的记录范围，这里是:

I have come up with a solution for that problem, it still uses temporary tables but instead of copying the entire table into the temp one it only copies the range of records that are being selected, here it is:

SELECT
    c.*,
  COUNT(r.id) AS replies
FROM
    (
        SELECT
            c.id,
            c.text,
            c.time,
            c.author AS author_id,
            SUM(cr.vote) AS rating,
            crv.vote AS voted
        FROM
            comments c
        LEFT JOIN users u ON u.id = c.author
        LEFT JOIN comments_ratings cr ON cr. COMMENT = c.id
        LEFT JOIN comments_ratings crv ON crv. COMMENT = c.id
        AND crv. USER = ?
        WHERE
            c.item = ?
        AND c.type = ?
        AND c.id < ?
        GROUP BY
            c.id
        ORDER BY
            c.id DESC
        LIMIT 0,
        100
    ) AS c
LEFT JOIN comments r ON c.id = r.reply
GROUP BY
    c.id
ORDER BY
    c.id DESC

我用表中超过 4 万条记录测试了这个方法，查询在一台非常弱的服务器机器上执行不到 10 毫秒.

I tested this method with 4+ million records in the table and the queries were executed in less than 10 milliseconds on a pretty weak server machine.

这篇关于计算join语句中不同表中的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！