COUNT 子句中的 SQL CASE 语句

发布于 2024-07-18 22:20:33 字数 704 浏览 1 评论 0原文

我试图从两个单独的表中获取产品的名称及其销售数量。

我的表格看起来像这样：

BOOK
Book_ID | Book_Title | Book_Author  

SOLD  
Transaction_ID | Book_ID | Customer_ID

我可以从以下查询中获得我想要的大部分结果

SELECT b.Book_Title, COUNT(s.Book_ID) FROM Book b, Sold s 
WHERE b.Book_ID = s.Book_ID 
GROUP BY b.Book_Title;

但是，这仅显示至少有一次销售的产品。我想显示所有产品，如果没有发生销售，则仅显示零。我一直在搞乱这样的事情：

SELECT b.Book_Title, 
       COUNT(CASE WHEN s.Book_ID IS NULL THEN 0 ELSE s.Book_ID END) 
FROM Book b, Sold s WHERE b.Book_ID = s.Book_ID GROUP BY Book_Title;

但是 WHERE 子句将结果限制为具有 1 或更多销售额的结果。

谁能建议解决这个问题的方法吗？我使用的是Oracle 10g。

谢谢

原文

I'm trying to get a product's name and its number of sales from two separate tables.

My tables look something like this:

BOOK
Book_ID | Book_Title | Book_Author  

SOLD  
Transaction_ID | Book_ID | Customer_ID

I can get most of the results I want from the following query

SELECT b.Book_Title, COUNT(s.Book_ID) FROM Book b, Sold s 
WHERE b.Book_ID = s.Book_ID 
GROUP BY b.Book_Title;

However, this only displays products with at least one sale. I would like to display all products, simply showing a zero if no sales have occurred. I've been messing around with something like this:

SELECT b.Book_Title, 
       COUNT(CASE WHEN s.Book_ID IS NULL THEN 0 ELSE s.Book_ID END) 
FROM Book b, Sold s WHERE b.Book_ID = s.Book_ID GROUP BY Book_Title;

But the WHERE clause is limiting the results to the ones with 1 or more sales.

Can anyone suggest a way around this? I am using Oracle 10g.

Thanks

分享到QQ

分享到微博

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

发布评论

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

巨坚强 2024-07-25 22:20:33

使用左外连接：

SELECT b.Book_Title, COUNT(s.Book_ID) 
FROM Book b left outer join Sold s on b.Book_ID = s.Book_ID 
GROUP BY b.Book_Title;

use a left outer join:

SELECT b.Book_Title, COUNT(s.Book_ID) 
FROM Book b left outer join Sold s on b.Book_ID = s.Book_ID 
GROUP BY b.Book_Title;

回复收藏 0 原文

丶情人眼里出诗心の 2024-07-25 22:20:33

您还可以在 select 子句中使用相关子查询：

select b.book_title, (select count(*) from sold s where s.book_id=b.book_id) from book b

它不需要 group by 或 outer join，这可能会很慢对于非常多的行。

You can also use a correlated subquery in the select clause :

select b.book_title, (select count(*) from sold s where s.book_id=b.book_id) from book b

It doesn't need either group by or outer joins, which can be slow for very large number of rows.

回复收藏 0 原文

不醒的梦 2024-07-25 22:20:33

正如@Vincent 所说，你需要一个外部联接。我最近没有太多使用 Oracle，但它专有的外连接语法相当奇怪。（我不知道他们是否在这方面赶上了 ANSI。）

专有语法是：

  SELECT b.Book_Title,
         COUNT(s.Book_ID)
    FROM Book b,
         Sold s
   WHERE b.Book_ID = s.Book_ID (+)
GROUP BY b.Book_Title;

As @Vincent said, you need an outer join. I haven't worked much with Oracle lately, but its proprietary outer join syntax is rather bizarre. (I don't know whether they've caught up with ANSI on that.)

The proprietary syntax is:

  SELECT b.Book_Title,
         COUNT(s.Book_ID)
    FROM Book b,
         Sold s
   WHERE b.Book_ID = s.Book_ID (+)
GROUP BY b.Book_Title;

回复收藏 0 原文

莫多说 2024-07-25 22:20:33

您应该在子查询中获取计数并对其进行左外连接，如下所示：

select b.book_title, 
   case when s.book_id is null then 0 
       else s.salesCount end as Sales
from book b
left outer join 
  (select count(*) as salesCount, book_id from sales group by book_id) s on b.book_id = s.book_id

You should get the count in a subquery and left outer join to it as such:

select b.book_title, 
   case when s.book_id is null then 0 
       else s.salesCount end as Sales
from book b
left outer join 
  (select count(*) as salesCount, book_id from sales group by book_id) s on b.book_id = s.book_id

回复收藏 0 原文