如何通过查询在mySQL组中显示计数为零的行? [英] How to show rows with zero counts in mySQL group by query?
本文介绍了如何通过查询在mySQL组中显示计数为零的行?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我尝试寻找类似的解决方案,但对我来说似乎并没有太大帮助.我有这样一张桌子:
I tried looking around for similar solutions but it didn't seem to help much in my case. I have a table as such:
locations => |longname|name|locations|
topics => |longname|name|tag|
,我正在尝试获取特定位置具有特定atag
的长名称的数量.到目前为止,我所查询的内容也尝试强制重新排序,这就是为什么您看到location IN
和FIELD
的原因.但是,如果某个位置没有人要使用特定标签,则会完全从响应中删除该标签.我希望它返回0.我该怎么做?
and I am trying to get the count of longnames with a specific atag
for a particular location. The query I have so far also tries to force reordering, which is why you see the location IN
and FIELD
. However, if there aren't any people in a location for a particular tag, it omits it entirely from the response. I'd like it to return 0. How can I do that?
select count(b.longname) as count, a.location
from `topics` b, locations a
where tag = 'atag' and a.longname = b.longname
and location IN ('Japan', 'France', 'Italy')
group by location
order by FIELD(location, 'Japan', 'France', 'Italy')
推荐答案
也许是这样吗? :
SELECT COUNT(t.longname) as cnt, l.location
FROM locations l
LEFT JOIN topics t
ON t.longname = l.longname
AND t.tag = 'atag'
WHERE l.location IN ('Japan', 'France', 'Italy')
GROUP BY l.location
ORDER BY FIELD(l.location, 'Japan', 'France', 'Italy')
这篇关于如何通过查询在mySQL组中显示计数为零的行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文