Mysql显示记录甚至没有结果

Question

我有两个表，即:列表和标签。这些是示例记录:

列表表

标签表

这是我的示例 SQL 查询:

SELECT l.tag,
count(case when l.type = 'Consumer' then l.type = '' end) as consumer,
count(case when l.type = 'Supplier' then l.type = '' end) as supplier
                          FROM listings AS l
                          LEFT JOIN tags AS t
                          ON l.tag = t.tag
                          GROUP BY t.tag, l.type

但是显示是这样的:

我想要这样的东西:

有什么想法可以做吗？我很乐意感谢任何形式的帮助。谢谢。

更新:更改为:

SELECT l.tag,
count(case when l.type = 'Consumer' then l.type = '' end) as consumer,
count(case when l.type = 'Supplier' then l.type = '' end) as supplier
                          FROM listings AS l
                          LEFT JOIN tags AS t
                          ON l.tag = t.tag
                          GROUP BY t.tag, l.type

这就是结果:

OUTER 关键字似乎有效，但我仍然需要为消费者和供应商显示带有 0 和 0 的 AZ。

Answer 1

试试这个..............

SELECT t.tag,
count(case when l.type = 'Consumer' then l.type = '' end) as consumer,
count(case when l.type = 'Supplier' then l.type = '' end) as supplier
                          FROM listings AS l
                          RIGHT JOIN tags AS t
                          ON l.tag = t.tag
                          GROUP BY t.tag