在SELECT语句中使用DISTINCT时出现错误

Question

我有这样的查询:

SELECT
CASE 
WHEN SUBSTR(fact.delivery_time,1,10) = from_timestamp(now(), 'yyyy-MM-dd') THEN from_timestamp(to_timestamp(SUBSTR(fact.delivery_time,1,10), 'yyyy-MM-dd'),'dd.MM.yyyy')
WHEN SUBSTR(fact.delivery_time,1,10) = from_timestamp(DAYS_ADD(now(),-1), 'yyyy-MM-dd') THEN from_timestamp(DAYS_ADD(now(),-1),'dd.MM.yyyy')
WHEN SUBSTR(fact.delivery_time,1,10) <= from_timestamp(DAYS_ADD(now(),-2), 'yyyy-MM-dd') THEN 'Older'
END AS delivery_day,
DISTINCT fact.order_id AS ORDER_ID
FROM fact
JOIN article art ON art.article_id = fact.article_id
WHERE fact.status IN ('OPEN', 'RECEIVING', 'REGISTERED')
AND fact.type !='RETURN'
AND art.classification_name LIKE '15deg%'
GROUP BY
fact.delivery_time;

，我得到这个错误:

ParseException:第605行语法错误:undefined: DISTINCT fact。期望:CASE, CAST, DEFAULT, EXISTS, FALSE, IF, INTERVAL, LEFT, NOT, NULL, REPLACE, RIGHT, TRUNCATE, TRUE，由以下原因引起的标识符:Exception:语法错误

我只需要ORDER_ID一次，尽管它可以在表FACT中存在多次，因为我需要在之后对它进行计数。

谢谢你的帮助。

Answer 1

DISTINCT考虑SELECT语句中的所有列，而不仅仅是指定的列;这就是误差。因此，要过滤DISTINCTorder_id;你可以使用group by ORDER_ID:

SELECT
CASE 
WHEN SUBSTR(fact.delivery_time,1,10) = from_timestamp(now(), 'yyyy-MM-dd') THEN from_timestamp(to_timestamp(SUBSTR(fact.delivery_time,1,10), 'yyyy-MM-dd'),'dd.MM.yyyy')
WHEN SUBSTR(fact.delivery_time,1,10) = from_timestamp(DAYS_ADD(now(),-1), 'yyyy-MM-dd') THEN from_timestamp(DAYS_ADD(now(),-1),'dd.MM.yyyy')
WHEN SUBSTR(fact.delivery_time,1,10) <= from_timestamp(DAYS_ADD(now(),-2), 'yyyy-MM-dd') THEN 'Older'
END AS delivery_day,
fact.order_id AS ORDER_ID
FROM fact
JOIN article art ON art.article_id = fact.article_id
WHERE fact.status IN ('OPEN', 'RECEIVING', 'REGISTERED')
AND fact.type !='RETURN'
AND art.classification_name LIKE '15deg%'
GROUP BY
delivery_day,
ORDER_ID;