Firebird SQL如果在where中

Question

我想从products from (库存) shelf1和货架2中选择数量，但也要列出shelf3中的项目，但列出数量为shelf3 = 0的数量。

SELECT item, amount 
FROM prodcuts 
WHERE kind = 'bike' 
and (stock = 'shelf1' or stock = 'shelf2' )

。

Table products
item    kind    stock   amount
cruiser bike    shelf1  1
cruiser bike    shelf2  2
racing  bike    shelf3  4

结果是

cruiser 3

但我需要的是结果

cruiser 3
racing  0

Answer 1

您的结果似乎是：

select p.item, sum(case when p.stock = 'shelf3' then 0 else p.amount end)
from products p
group by p.item;

然而，我不太确定这与你的解释是否相符。

Answer 2

尝尝这个

SELECT item, CASE WHEN stock = 'shelf3' THEN 0 ELSE amount END AS amount
FROM prodcuts 
WHERE kind = 'bike' 
and (stock = 'shelf1' or stock = 'shelf2'' or stock = 'shelf3' )