SQL:选择多个表的计数 [英] SQL: Selecting count of multiple tables

问题描述

我认为这不会太复杂而无法解释，但要让它工作起来肯定很复杂.

I don't think this will be too complicated to explain, but certainly complicated to get it working.

首先，我有几个关于用户评论的表格，每个部分(论坛，文章等)一个表格，如下所示:

First of all, I have a couple of tables regarding users comments, one table for each section (forum, articles etc), as shown below:

site_users (id, username, ...) [保存用户信息的表]

site_users (id, username, ...) [Table that holds user's info]

site_articles_comments (id, user_id, comment, ...) [其中 user_id = site_users.id]

site_articles_comments (id, user_id, comment, ...) [Where user_id = site_users.id]

site_forum_comments (id, user_id, comment, ...) [site_articles_comments 相同]

site_forum_comments (id, user_id, comment, ...) [Same for site_articles_comments]

问题是每一个新行都是一个新评论，用户可以多次评论，这意味着更多的行被添加，因此需要对行数进行排序以获得某种类型的评论数量排名系统.

The thing is that every new row is a new comment and users can comment multiple times, which means that more rows are being added, thus making the need of sorting the number of rows to get the amount of comments in some sort of ranking system.

通过这个简单的查询，我能够获得一个简单的论坛排名:

I was able to make a simple forum rank by doing this simple query:

SELECT u.id, u.username, COUNT(r.id) AS rank FROM site_users AS u LEFT加入 site_forum_comments AS r ON u.id = r.user_id GROUP BY u.username,u.id ORDER BY rank DESC LIMIT :l

SELECT u.id, u.username, COUNT(r.id) AS rank FROM site_users AS u LEFT JOIN site_forum_comments AS r ON u.id = r.user_id GROUP BY u.username, u.id ORDER BY rank DESC LIMIT :l

该查询对数据库中的所有用户进行排序，评论最多的用户总是排在最前面.

This query sorts all users from the database, where the user who has commented the most is always on top.

另一方面，我需要的是有一个全球排名系统，它可以汇总每个部分(文章、论坛等)的评论数量，并相应地显示用户.

What I need, in the other hand, is to have a global ranking system, which sums the amount of comments in each section (articles, forum etc) and displays the users accordingly.

我正在玩弄 sql 来做到这一点，我想出的最后一件事是这个巨大的查询:

I was playing around with the sql to do that and the last thing I came up with was this huge query:

SELECT u.id, u.username, (COUNT(a.id) + COUNT(f.id)) AS rank FROMsite_users u LEFT JOIN site_articles_comments a ON a.user_id = u.id左加入 site_forum_comments f ON f.user_id = u.id GROUP BYu.username, u.id ORDER BY rank DESC LIMIT :l

SELECT u.id, u.username, (COUNT(a.id) + COUNT(f.id)) AS rank FROM site_users u LEFT JOIN site_articles_comments a ON a.user_id = u.id LEFT JOIN site_forum_comments f ON f.user_id = u.id GROUP BY u.username, u.id ORDER BY rank DESC LIMIT :l

然而，这返回空值.我该怎么做才能达到我想要的结果?

This, however, returns null. What could I possibly do to achieve the result I want?

提前致谢，

马特乌斯

抱歉缺少信息，这是关于 MySQL 的.

Sorry for the lack of information, this is regarding MySQL.

推荐答案

问题是使用空值进行数学运算，并使用空值进行排序(检查NULLS LAST"选项以覆盖默认排序，该选项首先返回空值以进行降序命令).

The problem is math with nulls, and ordering with nulls (check into the "NULLS LAST" option for overriding the default ordering which returns the nulls first for a descending order).

在您的情况下，使用外连接，如果用户有大量文章评论但没有论坛评论，那么 Oracle 数学中的 100 + null = null.因此，要使数学起作用，您需要使 null=0.这就是 NVL() 的用武之地(并且还具有从结果集中消除讨厌的空值的良好副作用)！

In your case, with the outer joins, if the user has a ton of article comments but no forum comments, well, 100 + null = null in Oracle math. So to get the math to work you need to make null=0. That's where NVL() comes in (and also has the nice side-effect of eliminating pesky nulls from your result set)!

SELECT u.id, u.username, (NVL(COUNT(a.id),0) + NVL(COUNT(f.id),0)) AS rank 
FROM site_users u 
  LEFT JOIN site_articles_comments a ON a.user_id = u.id 
  LEFT JOIN site_forum_comments f ON f.user_id = u.id 
GROUP BY u.username, u.id ORDER BY rank DESC LIMIT :l

我看到您的标签中有 MySQL 和 Oracle - 以上是针对 Oracle 的.如果对于 MYSQL，请改用 COALESCE(COUNT(),0).

I see you have both MySQL and Oracle in your tags - the above is for Oracle. If for MYSQL use COALESCE(COUNT(),0) instead.

这篇关于SQL:选择多个表的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！