计数子句中的 SQL CASE 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/795341/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL CASE STATEMENT in COUNT CLAUSE
提问by Vincent Buck
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 WHEREclause is limiting the results to the ones with 1 or more sales.
但是该WHERE条款将结果限制为具有 1 个或多个销售额的结果。
Can anyone suggest a way around this? I am using Oracle 10g.
任何人都可以提出解决这个问题的方法吗？我正在使用 Oracle 10g。
Thanks
谢谢
回答by Vincent Buck
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;
回答by Lluis Martinez
You can also use a correlated subquery in the selectclause :
您还可以在select子句中使用相关子查询：
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 byor outer joins, which can be slow for very large number of rows.
它不需要group byor 或outer joins，这对于非常多的行来说可能很慢。
回答by Jeffrey Hantin
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.)
正如@Vincent 所说，您需要一个外部联接。我最近很少使用 Oracle，但它专有的外连接语法相当奇怪。（我不知道他们是否在这方面赶上了 ANSI。）
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;
回答by Jon Masters
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
回答by Sumit
make an other join from book to sold. you may still get a null for the count, but you resolve that by adding a NVL on top of that...
从书籍到售出进行另一个连接。您可能仍然会得到一个空值，但是您可以通过在其上添加一个 NVL 来解决这个问题...
