我试图修改下面的代码,以包括没有从l_foods表中提供任何东西的供应商。我让它显示他们,如果他们提供食物,但我不知道如何显示剩下的0在食物的数量列。我觉得左连接会有帮助。我不知道该怎么办,如果你能帮助我,我将不胜感激。
SELECT
a.supplier_id ,
b.supplier_name ,
count(a.supplier_id) AS "number of foods"
FROM
l_foods a ,
LEFT JOIN l_suppliers b ON a.supplier_id = b.supplier_id
GROUP BY a.supplier_id ,b.supplier_name
ORDER BY a.supplier_id
它给出了在l_foods表中有食物的供应商的表
Asp A Soup Place 3
Cbc认证牛肉公司2
弗兰克·里德的蔬菜2
Jbr Just Beverages 2
Rgf Really Good Foods 2
Vsb Virginia Street Bakery 1
为了查看所有供应商,您需要从供应商表中进行选择,并左连接到食品表
SELECT
a.supplier_id ,
b.supplier_name ,
count(a.supplier_id) AS "number of foods"
FROM
l_suppliers b ,
LEFT JOIN l_foods a ON a.supplier_id = b.supplier_id
GROUP BY b.supplier_id ,b.supplier_name
ORDER BY b.supplier_id
尝试使用NVL函数(用0代替null,我在这里使用它的方式),并使用表b在选择列表和组
SELECT b.supplier_id,
b.supplier_name,
nvl(count(a.supplier_id), 0) AS "number of foods"
FROM l_foods a
LEFT JOIN l_suppliers b
ON a.supplier_id = b.supplier_id
GROUP BY b.supplier_id, b.supplier_name
ORDER BY b.supplier_id
如果您想要所有供应商即使没有食品,那么您需要从供应商表和left join
食品开始:
SELECT s.supplier_id, s.supplier_name ,
count(f.supplier_id) AS "number of foods"
FROM l_suppliers s LEFT JOIN
l_foods f
ON f.supplier_id = l.supplier_id
GROUP BY s.supplier_id, s.supplier_name
ORDER BY s.supplier_id;
注意以下内容:
- 将表别名更改为表的缩写。这使得查询更容易理解。
- 交换表的顺序来解决问题(您也可以使用
RIGHT JOIN
,但我更喜欢LEFT JOIN
) -
group by
和select
中的关键列均来自供应商表。
试试这个:
SELECT a.supplier_id ,a.supplier_name ,sum(case when b.supplier_id is null then 0 else 1 end)) AS "number of foods"
FROM l_suppliers a
LEFT JOIN l_foods b ON a.supplier_id = b.supplier_id
GROUP BY a.supplier_id ,a.supplier_name
ORDER BY a.supplier_id