在SQL select语句中使用多个where条件

Question

我有一个SQL查询如下(这是错误的)，我需要根据共享的条件调整where条件。

select 
'app' as appkey,'arival' as layer,
'fmcg' as sets,ID as HOME_ID,
INVO_DATE as INVOICE_DATE,
count(distinct(P_DATE||TP_ID)) 
where 
ART_ID != "1" as dispatch,
sum(SALES_product) 
where 
ART_ID = "1" as product_sum, sum(SALES_area) 
where  
ART_ID != "1" as area_SALES 
from 
sales group by HOME_ID, INVO_DATE 

我请求请帮助使查询可执行。

Answer 1

WHERE条件逻辑根据要求转换为CASE语句

SELECT 'app' as appkey
,'arival' as layer
, 'fmcg' as sets
, ID as HOME_ID
, INVO_DATE as INVOICE_DATE
, COUNT(DISTINCT CASE WHEN ART_ID != '1' THEN (P_DATE||TP_ID) END) dispatch
, SUM(CASE WHEN ART_ID = '1' THEN SALES_product END) product_sum
, SUM(CASE WHEN ART_ID != '1' THEN SALES_area END) area_SALES 
FROM sales 
GROUP BY ID, INVO_DATE