我在mysql数据库中有以下表格
a_ad_display
id | advId |
a_mediatype
id | mediaId
1 | animation
2 | Video
3 | Image
a_advertise
advId | mediatypeId | organizationId
现在我想要的是从a_ad_display获取advId的所有记录，其中包含advId的计数，并在advertise表中按organizationId =“ some specific id”注册并按其媒体类型进行分组。
我已经通过以下查询实现了....
SELECT COUNT(a.advertiseId) as Total ,m.medianame
FROM a_mediatype as m
JOIN a_advertise as a ON a.mediaTypeId = m.mediaId
JOIN a_ad_display as ad ON ad.advId = a.advertiseId
where a.organizationId in(SELECT t.organizationId
FROM organization_ AS t JOIN organization_ AS p ON t.treePath LIKE CONCAT( p.treePath, '%')
where p.organizationId ='37423') GROUP BY m.medianame
但是我的输出像这样
medianame | count
animation | 92
Images | 103
因此，这意味着a_ad_display中没有带有某些特定有机化ID的视频类型advId ...
但是我想要的是如下...
medianame | count
animation | 92
video | 0
Images | 103
这意味着如果没有特定媒体类型的advid，则还会在输出中以0计数显示该advid。
任何人都可以在这个问题上给我一些指导吗？
看到我的SQL小提琴演示
http://sqlfiddle.com/#!2/391a2/11
最佳答案
使用LEFT JOIN代替：
SELECT
m.medianame,
IFNULL(COUNT(ad.id), 0) AS Total
FROM a_mediatype as m
LEFT JOIN a_advertise AS a ON a.mediaTypeId = m.mediaId
LEFT JOIN a_ad_display AS ad ON ad.advId = a.advId
LEFT JOIN organization_ AS o ON a.organizationId = o.organizationId
LEFT JOIN organization_ AS p ON o.organizationId = p.organizationId
AND p.organizationId = '37423'
AND o.treePath LIKE CONCAT( p.treePath, '%')
GROUP BY m.medianame;
SQL Fiddle Demo
这将为您提供：
| MEDIANAME | TOTAL |
---------------------
| animation | 13 |
| image | 2 |
| video | 0 |