mysql - 带连接的 SQL 条件计数

Question

我在 stackoverflow 上找不到问题的答案。我有一个跨越 3 个表的查询:

newsitem
+------+----------+----------+----------+--------+----------+
| Guid | Supplier | LastEdit | ShowDate |  Title | Contents |
+------+----------+----------+----------+--------+----------+
newsrating
+----+----------+--------+--------+
| Id | NewsGuid | UserId | Rating |
+----+----------+--------+--------+
usernews
+----+----------+--------+----------+
| Id | NewsGuid | UserId | ReadDate |
+----+----------+--------+----------+

Newsitem 显然包含 newsitem，newsrating 包含用户对 newsitem 的评级，而 usernews 包含用户阅读 newsitem 的日期。

在我的查询中，我想获取每个新闻站点，包括该新闻站点的评分数和平均评分，以及该新闻站点已被当前用户阅读了多少次。

我目前拥有的是:

select newsitem.guid, supplier, count(newsrating.id) as numberofratings,
    avg(newsrating.rating) as rating,
    count(case usernews.UserId when 3 then 1 else null end) as numberofreads from newsitem
    left join newsrating on newsitem.guid = newsrating.newsguid
    left join usernews on newsitem.guid = usernews.newsguid
    group by newsitem.guid

我在这里创建了一个 sql fiddle:http://sqlfiddle.com/#!9/c8add/8

两次 count() 调用都没有返回我想要的数字。 numberofratings 应返回该新闻项目的评级总数(所有用户)。 numberofreads 应返回该新闻站点当前用户的阅读次数。

因此，guid d104c330-c319-40e8-8be3-a7c4f549d35c 的 newsitem 对于 userid = 3 的当前用户应该有 2 个评分和 3 个阅读。

我试过条件计数和求和，但还没有成功。如何做到这一点？

Answer 1

我看到的主要问题是您将两个表连接在一起，这意味着您将有效地乘以两个数字，这就是为什么您的计数不会正确的原因。例如，如果 Newsitem 已被用户阅读 3 次并被 8 位用户评分，那么您最终将获得 24 行，因此它看起来像是被评分了 24 次。您可以将 DISTINCT 添加到您的 COUNT 评级 ID，这应该可以解决该问题。平均值应该不受影响，因为 1 和 2 的平均值与 1、1、2 和 2 的平均值相同(例如)。

然后您可以通过将 userid 添加到 JOIN 条件来处理读取(因为它是一个 OUTER JOIN 它不应该导致任何结果丢失)而不是 in COUNT 的 CASE 语句，然后您可以对来自 Usernews 的不同 id 值执行 COUNT。结果查询将是:

SELECT
    I.guid,
    I.supplier,
    COUNT(DISTINCT R.id) AS number_of_ratings,
    AVG(R.rating) AS avg_rating,
    COUNT(DISTINCT UN.id) AS number_of_reads
FROM
    NewsItem I
LEFT OUTER JOIN NewsRating R ON R.newsguid = I.guid
LEFT OUTER JOIN UserNews UN ON
    UN.newsguid = I.guid AND
    UN.userid = @userid
GROUP BY
    I.guid,
    I.supplier

虽然这应该有效，但您可能会从子查询中获得更好的结果，因为上面的代码需要展开结果然后聚合它们，这可能是不必要的。此外，有些人可能会发现以下内容更清楚一些。

SELECT
    I.guid,
    I.supplier,
    R.number_of_ratings,
    R.avg_rating,
    COUNT(*) AS number_of_reads
FROM
    NewsItem I
LEFT OUTER JOIN
(
    SELECT
        newsguid,
        COUNT(*) AS number_of_ratings,
        AVG(rating) AS avg_rating
    FROM
        NewsRating
    GROUP BY
        newsguid
) R ON R.newsguid = I.guid
LEFT OUTER JOIN UserNews UN ON UN.newsguid = I.guid AND UN.userid = @userid
GROUP BY
    I.guid,
    I.supplier,
    R.number_of_ratings,
    R.avg_rating