根据特定值顺序对SQL Oracle查询进行分类

发布于 2025-02-07 07:11:52 字数 605 浏览 1 评论 0原文

我的任务是首先显示类别5中的产品数量，然后在类别1中显示。这是我拥有的查询：

SELECT 
      pc.category_name, p.category_id, COUNT(p.category_id) AS num_of_products 
FROM 
     products p 
INNER JOIN 
     product_categories pc 
ON 
     p.category_id = pc.category_id
WHERE 
     p.category_id != 4 AND pc.category_id != 4
GROUP BY  
     pc.category_name, p.category_id;

这是输出：

**CATEGORY_NAME CATEGORY_ID NUM_OF_PRODUCTS**
  CPU           1           70
  Video Card    2           50
  Storage       5           108

如何以必需的方式对其进行排序？也许以后代顺序显示num_of_products列？

提前致谢。

原文

My task is to display first the number of products in category 5, then category 1 and then 2. This is the query I have:

SELECT 
      pc.category_name, p.category_id, COUNT(p.category_id) AS num_of_products 
FROM 
     products p 
INNER JOIN 
     product_categories pc 
ON 
     p.category_id = pc.category_id
WHERE 
     p.category_id != 4 AND pc.category_id != 4
GROUP BY  
     pc.category_name, p.category_id;

And this is the output:

**CATEGORY_NAME CATEGORY_ID NUM_OF_PRODUCTS**
  CPU           1           70
  Video Card    2           50
  Storage       5           108

How can I sort it in the required way? Maybe displaying the num_of_products column in a descendent order?

Thanks in advance.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

我也只是我 2025-02-14 07:11:52

我的任务是首先显示类别5中的产品数，然后显示类别1，然后显示2。
。

使用case case by 条款中的表达式将不同的类别转化为订单优先级：

SELECT pc.category_name,
       p.category_id,
       COUNT(p.category_id) AS num_of_products 
FROM   products p 
       INNER JOIN product_categories pc 
       ON p.category_id = pc.category_id
WHERE  p.category_id != 4
GROUP BY  
       pc.category_name,
       p.category_id
ORDER BY
       CASE p.category_id
       WHEN 5 THEN 1
       WHEN 1 THEN 2
       WHEN 2 THEN 3
       ELSE 4
       END;

如果您确实是指在降序顺序（而不是由恰好与之相对应的类别订购），则只需使用num_of_products num_of_products >在订单中子句：

SELECT pc.category_name,
       p.category_id,
       COUNT(p.category_id) AS num_of_products 
FROM   products p 
       INNER JOIN product_categories pc 
       ON p.category_id = pc.category_id
WHERE  p.category_id != 4
GROUP BY  
       pc.category_name,
       p.category_id
ORDER BY
       num_of_products DESC;

My task is to display first the number of products in category 5, then category 1 and then 2.

Use a CASE expression in the ORDER BY clause to translate different categories into the order priorities:

SELECT pc.category_name,
       p.category_id,
       COUNT(p.category_id) AS num_of_products 
FROM   products p 
       INNER JOIN product_categories pc 
       ON p.category_id = pc.category_id
WHERE  p.category_id != 4
GROUP BY  
       pc.category_name,
       p.category_id
ORDER BY
       CASE p.category_id
       WHEN 5 THEN 1
       WHEN 1 THEN 2
       WHEN 2 THEN 3
       ELSE 4
       END;

If you really mean that you want to order by the num_of_products in descending order (rather than ordering by the categories that happen to correspond with that) then just use the num_of_products in the ORDER BY clause:

SELECT pc.category_name,
       p.category_id,
       COUNT(p.category_id) AS num_of_products 
FROM   products p 
       INNER JOIN product_categories pc 
       ON p.category_id = pc.category_id
WHERE  p.category_id != 4
GROUP BY  
       pc.category_name,
       p.category_id
ORDER BY
       num_of_products DESC;

回复收藏 0 原文

~没有更多了~