如何通过条件逻辑生成布尔列验证设备文件归属正确性？

没问题，我来帮你搞定这个需求！你的核心目标是给每条记录标记它是否属于设备应该归属的文件，咱们用窗口函数就能轻松实现，比自连接更简洁清晰。

首先明确规则的优先级：1862和1901是最高优先级（只要设备在这两个文件里，只有对应文件的记录是Yes），1880是次优先级（只有当设备不在前两个文件里时才标记Yes）。

实现SQL #

假设你的数据表名为device_files，可以用下面的SQL语句生成需要的Correct列：

SELECT 
    device,
    file,
    CASE
        -- 规则1：当前记录是1862文件，直接标记Yes
        WHEN file = '1862' THEN 'Yes'
        -- 规则3：当前记录是1901文件，直接标记Yes
        WHEN file = '1901' THEN 'Yes'
        -- 规则2：当前记录是1880，且该设备从未出现在1862或1901中，标记Yes
        WHEN file = '1880' 
             AND MAX(CASE WHEN file IN ('1862', '1901') THEN 1 ELSE 0 END) OVER (PARTITION BY device) = 0
             THEN 'Yes'
        -- 其他所有情况都标记No
        ELSE 'No'
    END AS Correct
FROM device_files
ORDER BY device, file;

逻辑解释 #

这里用到了窗口函数来获取每个设备的全局文件信息：

• MAX(CASE WHEN file IN ('1862', '1901') THEN 1 ELSE 0 END) OVER (PARTITION BY device)：这个函数会给每个设备计算一个标识值——如果该设备有任何一条记录在1862或1901文件中，这个值就是1，否则是0。
• 然后通过CASE WHEN依次匹配规则：
  1. 先处理最高优先级的1862和1901记录，直接标记Yes；
  2. 再处理1880的情况，只有当设备完全没出现在高优先级文件里时才标记Yes；
  3. 剩下的所有记录（比如设备在1862但当前是1880，或者设备在1901但当前是1880）都标记No。

验证结果 #

把你的示例数据代入这个SQL，会得到完全符合你期望的结果：

为什么自连接可能失败？ #

之前用自连接+CASE WHEN没成功，大概率是因为自连接时没有正确聚合每个设备的所有文件情况，导致无法准确判断设备是否存在于高优先级文件中。窗口函数可以直接在每行获取到该设备的全局状态，避免了复杂的连接和过滤逻辑，更不容易出错。

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