mysql - 对列进行计数并内连接另一个表时出错

Question

我正在尝试统计评论最多的新闻。我有两个表:评论和新闻，包含同一列:News_ID。这是查询

select *,count(News_ID) as count from comments inner join news where comments.News_ID = news.News_ID group by comments.News_ID order by count DESC limit 4

我收到此错误

#1052 - Column 'News_ID' in field list is ambiguous

这是否意味着我要计算的列？

Answer 1

您需要告诉您在选择中使用其列的表的名称有两个 News_ID 列，因此在计数中它对于查询选择哪个表的列是不明确的，也使用正确的语法进行连接on 子句

select n.*,c.*,count(n.News_ID) as count 
from comments c
inner join news n ON(c.News_ID = n.News_ID)
 group by c.News_ID 
order by count DESC limit 4