Oracle SQL中处理重复行数超3行的字段修改需求

需求说明 #

当YIP.YOUTH_POLICY（别名A）表中，同一POLICY_NO或POLICY_TITLE对应的关联记录行数超过3行时，需将查询结果中YIP.YOUTH_SMALL_CATEGORY（别名C）表的SMALL_CATEGORY_TITLE字段值改为"Z"，同时将YIP.YOUTH_POLICY_AREA（别名B）表的SMALL_CATEGORY_SID字段值设为null。

涉及表与关联关系 #

• 关联表：YIP.YOUTH_POLICY（A）、YIP.YOUTH_POLICY_AREA（B）、YIP.YOUTH_SMALL_CATEGORY（C）
• 关联键：POLICY_NO作为主键/外键关联三张表

示例数据 #

期望查询结果 #

现有代码 #

已实现的筛选重复行数少于3的查询 #

SELECT
 A.POLICY_NO
 , B.SMALL_CATEGORY_SID
 , C.SMALL_CATEGORY_TITLE
 , A.POLICY_TITLE
 , COUNT(*) OVER() AS TOTAL_COUNT
FROM
 YIP.YOUTH_POLICY A
 LEFT JOIN
 YIP.YOUTH_POLICY_AREA B
 ON A.POLICY_NO = B.POLICY_NO
 LEFT JOIN
 YIP.YOUTH_SMALL_CATEGORY C
 ON B.SMALL_CATEGORY_SID = C.SMALL_CATEGORY_SID
 WHERE A.POLICY_NO IN (SELECT 
                        F.POLICY_NO 
                        FROM YIP.YOUTH_POLICY F 
                        LEFT JOIN
                         YIP.YOUTH_POLICY_AREA G
                         ON F.POLICY_NO = G.POLICY_NO
                         LEFT JOIN
                         YIP.YOUTH_SMALL_CATEGORY H
                         ON G.SMALL_CATEGORY_SID = H.SMALL_CATEGORY_SID
                        GROUP BY F.POLICY_NO 
                        HAVING COUNT(*) < 3)
ORDER BY A.POLICY_NO;

尝试修改字段的错误查询语句 #

SELECT
 A.POLICY_NO
 --, B.SMALL_CATEGORY_SID
 , SUM(CASE WHEN C.SMALL_CATEGORY_TITLE IN (SELECT 
                                        F.POLICY_NO 
                                        FROM YIP.YOUTH_POLICY F 
                                        LEFT JOIN
                                         YIP.YOUTH_POLICY_AREA G
                                         ON F.POLICY_NO = G.POLICY_NO
                                         LEFT JOIN
                                         YIP.YOUTH_SMALL_CATEGORY H
                                         ON G.SMALL_CATEGORY_SID = H.SMALL_CATEGORY_SID
                                        GROUP BY F.POLICY_NO 
                                        HAVING COUNT(*) > 2) THEN 1 ELSE NULL END) AS 'Z'
 , A.POLICY_TITLE
 , COUNT(*) OVER() AS TOTAL_COUNT
FROM
 YIP.YOUTH_POLICY A
 LEFT JOIN
 YIP.YOUTH_POLICY_AREA B
 ON A.POLICY_NO = B.POLICY_NO
 LEFT JOIN
 YIP.YOUTH_SMALL_CATEGORY C
 ON B.SMALL_CATEGORY_SID = C.SMALL_CATEGORY_SID
ORDER BY A.POLICY_NO;

错误信息 #

SQL Error [42000]: JDBC-8006:Missing FROM keyword. 错误位置在第17行第59列。

解决方案 #

错误原因分析 #

1. 错误语句中CASE WHEN的条件逻辑错误：用字符串类型的C.SMALL_CATEGORY_TITLE匹配子查询返回的数值类型F.POLICY_NO，类型不匹配且逻辑无关。
2. 使用SUM聚合函数但未添加GROUP BY分组，违反SQL语法规则。

正确实现SQL #

仅基于POLICY_NO行数判断的版本

SELECT
    A.POLICY_NO,
    -- 行数超过3时设为NULL，否则保留原字段值
    CASE WHEN row_counts.policy_row_count > 3 THEN NULL ELSE B.SMALL_CATEGORY_SID END AS SMALL_CATEGORY_SID,
    -- 行数超过3时改为'Z'，否则保留原字段值
    CASE WHEN row_counts.policy_row_count > 3 THEN 'Z' ELSE C.SMALL_CATEGORY_TITLE END AS SMALL_CATEGORY_TITLE,
    A.POLICY_TITLE,
    COUNT(*) OVER() AS TOTAL_COUNT
FROM
    YIP.YOUTH_POLICY A
LEFT JOIN
    YIP.YOUTH_POLICY_AREA B ON A.POLICY_NO = B.POLICY_NO
LEFT JOIN
    YIP.YOUTH_SMALL_CATEGORY C ON B.SMALL_CATEGORY_SID = C.SMALL_CATEGORY_SID
-- 子查询计算每个POLICY_NO对应的关联记录总数
LEFT JOIN (
    SELECT
        F.POLICY_NO,
        COUNT(*) AS policy_row_count
    FROM
        YIP.YOUTH_POLICY F
    LEFT JOIN
        YIP.YOUTH_POLICY_AREA G ON F.POLICY_NO = G.POLICY_NO
    LEFT JOIN
        YIP.YOUTH_SMALL_CATEGORY H ON G.SMALL_CATEGORY_SID = H.SMALL_CATEGORY_SID
    GROUP BY
        F.POLICY_NO
) row_counts ON A.POLICY_NO = row_counts.POLICY_NO
ORDER BY
    A.POLICY_NO;

同时基于POLICY_NO或POLICY_TITLE行数判断的版本

如果需要同时满足POLICY_NO或POLICY_TITLE对应的行数超过3就修改字段，可改用窗口函数计算两种维度的行数：

SELECT
    A.POLICY_NO,
    CASE WHEN row_counts.policy_no_count > 3 OR row_counts.policy_title_count > 3 THEN NULL ELSE B.SMALL_CATEGORY_SID END AS SMALL_CATEGORY_SID,
    CASE WHEN row_counts.policy_no_count > 3 OR row_counts.policy_title_count > 3 THEN 'Z' ELSE C.SMALL_CATEGORY_TITLE END AS SMALL_CATEGORY_TITLE,
    A.POLICY_TITLE,
    COUNT(*) OVER() AS TOTAL_COUNT
FROM
    YIP.YOUTH_POLICY A
LEFT JOIN
    YIP.YOUTH_POLICY_AREA B ON A.POLICY_NO = B.POLICY_NO
LEFT JOIN
    YIP.YOUTH_SMALL_CATEGORY C ON B.SMALL_CATEGORY_SID = C.SMALL_CATEGORY_SID
LEFT JOIN (
    SELECT
        F.POLICY_NO,
        F.POLICY_TITLE,
        -- 按POLICY_NO分组计算行数
        COUNT(*) OVER(PARTITION BY F.POLICY_NO) AS policy_no_count,
        -- 按POLICY_TITLE分组计算行数
        COUNT(*) OVER(PARTITION BY F.POLICY_TITLE) AS policy_title_count
    FROM
        YIP.YOUTH_POLICY F
    LEFT JOIN
        YIP.YOUTH_POLICY_AREA G ON F.POLICY_NO = G.POLICY_NO
    LEFT JOIN
        YIP.YOUTH_SMALL_CATEGORY H ON G.SMALL_CATEGORY_SID = H.SMALL_CATEGORY_SID
) row_counts ON A.POLICY_NO = row_counts.POLICY_NO AND A.POLICY_TITLE = row_counts.POLICY_TITLE
ORDER BY
    A.POLICY_NO;

代码说明 #

1. 先通过子查询/窗口函数提前计算每个POLICY_NO或POLICY_TITLE对应的关联记录行数，避免主查询中重复关联计算。
2. 用CASE语句根据行数判断结果，动态修改目标字段值，逻辑清晰且符合SQL语法规范。

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