Oracle SQL按类别划分的产品总数

Question

我有一个练习，说我必须做这个查询:产品类别的名称(CategoryName)和每个类别的产品总数

我有两个表:第一张表叫做"类别"。其中产品的类别

-第二张表叫做"产品"它包含产物

主键" category "of Categories与Products共享，所以我想要做的是计算每个id有多少个产品，并在左侧显示名称

我将留下两个关于两个表的内容的例子，因为这两个表不能连接，但不包括如何计算每个类别的产品数量

表类:

| Categoryid | Categoryname ||
| --------  | ------------- ||  
| 1         | Beverages     ||  
| 2         | Condiments    ||
| 3         | Confections   ||

表产品:

| Productid | Productname   | Categoryid ||
| --------  | ------------- | ---------- ||
| 1         | Chai          |  1         ||
| 2         | Chang         |  1         ||
| 3         | Tofu          |  5         ||

结果如何:

| CategoryName | TotalProducts||
| --------     | -----------  ||  
| Beverages    | 10           ||  
| Condiments   | 5            ||  
| Confections  | 3            ||

我不知道如何计算每个品类的产品数量

我试试这个:

SELECT Categoryname COUNT(*)
FROM Categories  JOIN  Products  ON Categories.Categoryid=Products.Categoryid;

Answer 1

看起来像外部连接(这样您就可以显示没有任何产品的类别)，计数产品(不是"generally"因为你会得到错误的结果;我来说明我的意思)。

样本数据(你的数据是错误的;如果第5类不存在，你就不能把豆腐归入第5类;外键约束不允许):

SQL> with
2  categories (categoryid, categoryname) as
3    (select 1, 'beverages'   from dual union all
4     select 2, 'condiments'  from dual union all
5     select 3, 'confections' from dual union all
6     select 5, 'category 5'  from dual
7    ),
8  products (productid, productname, categoryid) as
9    (select 1, 'chai' , 1 from dual union all
10     select 2, 'chang', 1 from dual union all
11     select 3, 'tofu' , 5 from dual
12    )

查询:count products (line #14):

13  select c.categoryname,
14         count(p.productid) number_of_products
15  from categories c left join products p on p.categoryid = c.categoryid
16  group by c.categoryname;
CATEGORYNAME         NUMBER_OF_PRODUCTS
-------------------- ------------------
beverages                             2
category 5                            1
condiments                            0
confections                           0
SQL>

---

如果你使用count(*)(第14行)，你会得到错误的结果，因为你会计算类别本身:

13  select c.categoryname,
14         count(*) number_of_products
15  from categories c left join products p on p.categoryid = c.categoryid
16  group by c.categoryname;
CATEGORYNAME         NUMBER_OF_PRODUCTS
-------------------- ------------------
beverages                             2
category 5                            1
condiments                            1     --> no products in
confections                           1     --> these two categories!
SQL>

---

然而，如果你对没有任何产品的类别不感兴趣，内连接和count(*)(以及count(p.productid))可以做:

13  select c.categoryname,
14         count(*) number_of_products
15  from categories c join products p on p.categoryid = c.categoryid
16  group by c.categoryname;
CATEGORYNAME         NUMBER_OF_PRODUCTS
-------------------- ------------------
beverages                             2
category 5                            1
SQL>

Answer 2

对于每个类别，我列出了所有产品及其数量。

CREATE TABLE categories(  
category_id, category_name) AS
SELECT 1, 'Beverages' FROM DUAL UNION ALL
SELECT 2, 'Condiments' FROM DUAL UNION ALL
SELECT 3, 'Confections' FROM DUAL;
CREATE TABLE products(  
product_id, product_name, category_id) AS
SELECT 1, 'Chai',1 FROM DUAL UNION ALL
SELECT 2, 'Chang',1 FROM DUAL UNION ALL
SELECT 3, 'Tofu', 2 FROM DUAL;
select  c.category_id,
c.category_name,
listagg(p.product_name,', ') within group(order by p.product_name) product_list,
count(p.product_name) cnt
from  categories c,
products p
where c.category_id = p.category_id(+)
group by c.category_id,
c.category_name
order by c.category_id,
c.category_name
/
CATEGORY_ID CATEGORY_NA PRODUCT_LIST                CNT
----------- ----------- -------------------- ----------
1 Beverages   Chai, Chang                   2
2 Condiments  Tofu                          1
3 Confections                               0