MYSQL group by 和内连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14505614/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MYSQL group by and inner join
提问by user1052096
I have an article table which holds the number of articles views for each day. A new record is created to hold the count for each seperate day for each article.
我有一个文章表，其中包含每天的文章查看次数。创建一个新记录来保存每篇文章的每个单独日期的计数。
The query below gets the article id and total views for the top 5 viewed article id for all time :
下面的查询获取文章 id 和所有时间前 5 位查看文章 id 的总浏览量：
SELECT article_id,
SUM(article_count) as cnt
FROM article_views
GROUP BY article_id
ORDER BY cnt DESC
LIMIT 5
I also have a seperate article table which holds all the article fields. I want to ammend the query above to join to the article table and get two fields for each article id. I have tried to do this below but count is comming back incorrectly :
我还有一个单独的文章表，其中包含所有文章字段。我想修改上面的查询以加入文章表并为每个文章 id 获取两个字段。我尝试在下面执行此操作，但计数返回错误：
SELECT article_views.article_id, SUM( article_views.article_count ) AS cnt, articles.article_title, articles.artcile_url
FROM article_views
INNER JOIN articles ON articles.article_id = article_views.article_id
GROUP BY article_views.article_id
ORDER BY cnt DESC
LIMIT 5
Im not sure exactly what im doing wrong. Do I need to do a subquery?
我不确定我到底做错了什么。我需要做一个子查询吗？
回答by Mahmoud Gamal
Add articles.article_title, articles.artcile_urlto the GROUP BYclause:
添加articles.article_title, articles.artcile_url到GROUP BY条款：
SELECT
article_views.article_id,
articles.article_title,
articles.artcile_url,
SUM( article_views.article_count ) AS cnt
FROM article_views
INNER JOIN articles ON articles.article_id = article_views.article_id
GROUP BY article_views.article_id,
articles.article_title,
articles.artcile_url
ORDER BY cnt DESC
LIMIT 5;
The reason you were not getting correct result set, is that when you select rows that are not included in the GROUP BYnor in an aggregate function in the SELECTclause MySQL picks up random value.
您没有获得正确结果集的原因是，当您选择不包含在GROUP BY或不包含在SELECT子句中的聚合函数中的行时，MySQL 会选择随机值。
回答by Gordon Linoff
You are using a MySQL (mis) feature called Hidden Columns, because article title is not in the group by. However, this may or may not be causing your problem.
您正在使用名为 Hidden Columns 的 MySQL (mis) 功能，因为文章标题不在group by. 但是，这可能会也可能不会导致您的问题。
If the counts are wrong, then I think you have duplicate article_idin the article table. You can check this by doing:
如果计数错误，那么我认为您article_id在文章表中有重复。您可以通过执行以下操作来检查：
select article_id, count(*) as cnt
from articles
group by article_id
having cnt > 1
If any appear, then that is your problem. If they all have different titles, then grouping by the title (as suggested by Mahmoud) would fix the problem.
如果出现任何问题，那就是您的问题。如果它们都有不同的标题，那么按标题分组（按照 Mahmoud 的建议）可以解决问题。
If not, one way to fix it is the following:
如果没有，修复它的一种方法如下：
SELECT article_views.article_id, SUM( article_views.article_count ) AS cnt, articles.article_title, articles.artcile_url
FROM article_views INNER JOIN
(select a.* from articles group by article_id) articles
ON articles.article_id = article_views.article_id
GROUP BY article_views.article_id
ORDER BY cnt DESC
LIMIT 5
This chooses an abitrary title for the article.
这为文章选择了一个任意的标题。
回答by spencer7593
Your query looks basically right to me...
您的查询对我来说基本上是正确的...
But the value returned for cntis going to be dependent upon article_idcolumn being UNIQUE in the articlestable. We'd assume that it's the primary key, and absent a schema definition, that's only an assumption.)
但是返回的值cnt将取决于表article_id中的列是唯一的articles。我们假设它是主键，并且没有模式定义，这只是一个假设。）
Also, we're likely to assume there's a foreign key between the tables, that is, there are no values of article_idin the articles_viewtable which don't match a value of article_idon a row from the articlestable.
此外，我们很可能会认为有表之间的外键，也就是，有没有价值article_id的articles_view不匹配的值表article_id从一排articles桌子。
To check for "orphan" article_id values, run a query like:
要检查“孤立”article_id 值，请运行如下查询：
SELECT v.article_id
FROM articles_view v
LEFT
JOIN articles a
ON a.article_id = v.article_id
WHERE a.article_id IS NULL
To check for "duplicate" article_id values in articles, run a query like:
要检查文章中的“重复”article_id 值，请运行如下查询：
SELECT a.article_id
FROM articles a
GROUP BY a.article_id
HAVING COUNT(1) > 1
If either of those queries returns rows, that could be an explanation for the behavior you observe.
如果这些查询中的任何一个返回行，这可能是对您观察到的行为的解释。
