Oracle SQL多表关联分组查询报错：获取最高总费用预约记录

原SQL的错误点 #

• 主查询使用了SUM等聚合函数，但未添加GROUP BY子句，违反SQL分组规则：所有未参与聚合的字段必须出现在GROUP BY中。
• WHERE子句不能直接使用聚合函数，聚合条件的过滤需用HAVING子句，或提前计算聚合结果后再做匹配。
• 子查询中直接在MAX内嵌套SUM并GROUP BY，会返回多行聚合结果，MAX无法直接处理，需先将每个预约的总费用作为临时数据集，再取最大值。

修正后的SQL写法（两种方案） #

方案1：分组+子查询匹配最高值 #

SELECT 
    A.APPOINT_NO, 
    A.APPOINT_DATETIME, 
    P.PATIENT_NO, 
    CASE 
        WHEN NVL(P.PATIENT_FNAME, '') != '' THEN P.PATIENT_FNAME || ' ' 
        ELSE '' 
    END || P.PATIENT_LNAME AS PATIENT_FULL_NAME,
    NVL(SUM(AS.APPTSERV_FEE), 0) + NVL(SUM(AS.APPTSERV_ITEMCOST), 0) + NVL(SUM(S.SERVICE_STDFEE), 0) AS TOTAL_COST
FROM APPOINTMENT A
JOIN PATIENT P ON A.PATIENT_NO = P.PATIENT_NO
JOIN APPT_SERV AS ON A.APPOINT_NO = AS.APPOINT_NO
JOIN SERVICE S ON AS.SERVICE_CODE = S.SERVICE_CODE
GROUP BY A.APPOINT_NO, A.APPOINT_DATETIME, P.PATIENT_NO, P.PATIENT_FNAME, P.PATIENT_LNAME
HAVING (NVL(SUM(AS.APPTSERV_FEE), 0) + NVL(SUM(AS.APPTSERV_ITEMCOST), 0) + NVL(SUM(S.SERVICE_STDFEE), 0)) = (
    SELECT MAX(total)
    FROM (
        SELECT NVL(SUM(AS_SUB.APPTSERV_FEE), 0) + NVL(SUM(AS_SUB.APPTSERV_ITEMCOST), 0) + NVL(SUM(S_SUB.SERVICE_STDFEE), 0) AS total
        FROM APPOINTMENT A_SUB
        JOIN APPT_SERV AS_SUB ON A_SUB.APPOINT_NO = AS_SUB.APPOINT_NO
        JOIN SERVICE S_SUB ON AS_SUB.SERVICE_CODE = S_SUB.SERVICE_CODE
        GROUP BY A_SUB.APPOINT_NO
    )
);

方案2：窗口函数（更简洁） #

WITH AppointmentCosts AS (
    SELECT 
        A.APPOINT_NO, 
        A.APPOINT_DATETIME, 
        P.PATIENT_NO, 
        CASE 
            WHEN NVL(P.PATIENT_FNAME, '') != '' THEN P.PATIENT_FNAME || ' ' 
            ELSE '' 
        END || P.PATIENT_LNAME AS PATIENT_FULL_NAME,
        NVL(SUM(AS.APPTSERV_FEE), 0) + NVL(SUM(AS.APPTSERV_ITEMCOST), 0) + NVL(SUM(S.SERVICE_STDFEE), 0) AS TOTAL_COST,
        MAX(NVL(SUM(AS.APPTSERV_FEE), 0) + NVL(SUM(AS.APPTSERV_ITEMCOST), 0) + NVL(SUM(S.SERVICE_STDFEE), 0)) OVER () AS MAX_TOTAL_COST
    FROM APPOINTMENT A
    JOIN PATIENT P ON A.PATIENT_NO = P.PATIENT_NO
    JOIN APPT_SERV AS ON A.APPOINT_NO = AS.APPOINT_NO
    JOIN SERVICE S ON AS.SERVICE_CODE = S.SERVICE_CODE
    GROUP BY A.APPOINT_NO, A.APPOINT_DATETIME, P.PATIENT_NO, P.PATIENT_FNAME, P.PATIENT_LNAME
)
SELECT APPOINT_NO, APPOINT_DATETIME, PATIENT_NO, PATIENT_FULL_NAME, TOTAL_COST
FROM AppointmentCosts
WHERE TOTAL_COST = MAX_TOTAL_COST;

额外优化点 #

原SQL中用NVL(PATIENT_FNAME, 'NULL') != 'NULL'判断姓名是否为空，容易混淆字符串'NULL'与实际NULL值，改为NVL(PATIENT_FNAME, '') != ''更严谨。

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