如何在SQL中有条件地筛选查询

Question

我有一个查询，显示每个shop_id和国家的收入，如下所示。

select shop_id,
country,
start_date,
sum(earnings) as earnings

from x
where country IN ('DE', 'IT', 'ES')     
group by 1,2,3

然而，我只想在DE国家有三家商店，在其他国家有所有商店。

shop_id IN ('1', '2', '3')

我该怎么做？

Answer 1

听起来你的标准可以使用一些嵌套，比如：

select shop_id,
country,
start_date,
sum(earnings) as earnings

from x
where country IN ('IT', 'ES') 
OR (country = 'DE' AND sop_id IN ('1','2','3'))
group by 1,2,3

Answer 2

您可以在WHERE子句中添加一个CASE语句，该语句检查country列中的DE，并且只返回指定的三个shop_id，否则返回shop_id。

WHERE
CASE 
WHEN country = 'DE' AND shop_id IN ('1', '2', '3') THEN 1
WHEN country IN ('IT', 'ES') THEN 1
ELSE 0
END = 1