SELECT语句中使用DISTINCT报错，求正确去重order_id的写法

错误原因 #

• DISTINCT语法错误：DISTINCT关键字只能放在SELECT之后，用于去除结果集中的重复行，不能直接修饰单个字段（如DISTINCT fact.order_id这种写法不合法）。
• GROUP BY不符合规范：原查询的GROUP BY仅包含fact.delivery_time，但SELECT中返回了delivery_day和order_id两个非聚合列，这在标准SQL中会报错，且无法保证order_id的唯一性。

解决方案 #

方案一：使用SELECT DISTINCT去重 #

将DISTINCT移至SELECT之后，直接去除结果中的重复行，确保每个ORDER_ID仅出现一次：

SELECT DISTINCT
    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，明确保证每个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, fact.order_id

优化版：简化日期处理逻辑 #

可以用更简洁的日期函数替代SUBSTR操作，提升可读性和性能：

SELECT DISTINCT
    CASE 
        WHEN date(fact.delivery_time) = date(now()) THEN date_format(date(now()), 'dd.MM.yyyy')
        WHEN date(fact.delivery_time) = date(DAYS_ADD(now(), -1)) THEN date_format(date(DAYS_ADD(now(), -1)), 'dd.MM.yyyy')
        WHEN date(fact.delivery_time) <= date(DAYS_ADD(now(), -2)) 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%'

内容的提问来源于stack exchange，提问作者BiSaM