SQL技术需求：筛选符合特定城市与日期条件的可补全ID

原SQL仅通过count(distinct City) = 2无法确保两个取值中包含空字符串，也未验证非空城市的唯一性（这是补全操作的必要前提）。以下是满足所有需求的改进语句：

SELECT 
    ID,
    DATEDIFF(day, MIN(date), MAX(date)) AS Days_Delta,
    COUNT(DISTINCT City) AS Unique_City,
    CASE 
        WHEN 
            -- 该ID下存在空字符串的city记录
            SUM(CASE WHEN City = '' THEN 1 ELSE 0 END) > 0
            -- 非空city仅有唯一取值，满足补全条件
            AND COUNT(DISTINCT CASE WHEN City <> '' THEN City END) = 1
            -- 记录日期差小于90天
            AND DATEDIFF(day, MIN(date), MAX(date)) < 90
        THEN 'Transform'
        ELSE ''
    END AS transform_case
FROM My_Table
GROUP BY ID

关键逻辑说明： #

• SUM(CASE WHEN City = '' THEN 1 ELSE 0 END) > 0：精准判断该ID下存在空城市值的记录
• COUNT(DISTINCT CASE WHEN City <> '' THEN City END) = 1：确保同ID的非空城市唯一，避免出现多个非空值无法补全的情况
• 结合日期差判断，最终筛选出完全符合补全要求的ID

如果需要直接提取符合条件的ID（而非返回所有ID的标记结果），可以在外层添加筛选条件：

SELECT *
FROM (
    SELECT 
        ID,
        DATEDIFF(day, MIN(date), MAX(date)) AS Days_Delta,
        COUNT(DISTINCT City) AS Unique_City,
        CASE 
            WHEN 
                SUM(CASE WHEN City = '' THEN 1 ELSE 0 END) > 0
                AND COUNT(DISTINCT CASE WHEN City <> '' THEN City END) = 1
                AND DATEDIFF(day, MIN(date), MAX(date)) < 90
            THEN 'Transform'
            ELSE ''
        END AS transform_case
    FROM My_Table
    GROUP BY ID
) t
WHERE transform_case = 'Transform'

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