如何在SQL查询中统计每个日期对应的Set总数量

实现方案 #

你要实现的是按日期分组统计每组的Set总条数，再把统计值回填到该日期的每一行，有两种常用实现方式：

方案一：使用窗口函数（推荐，写法更简洁，性能更优） #

直接在SELECT列表里加窗口函数即可，不需要额外关联子查询，修正后SQL如下：

SELECT 
    csm_pat_exe_date.pedate_id, 
    csm_patient_exercise.pat_exe_id,  
    csm_exercise_details.ede_id, 
    csm_exercise.exe_id, 
    ses_pat_id, 
    ses_pat_note, 
    ses_pat_body_weight, 
    ses_pat_blood_pressure, 
    ses_pat_blood_glucose_level,
    exe_name, 
    ede_order, 
    ede_type,  
    ede_unit, 
    weekofyear(csm_pat_exe_date.pedate_date) AS weekNumber, 
    csm_pat_exe_date.pedate_date, 
    peds_id, 
    peds_set, 
    peds_result, 
    pedate_note, 
    -- 按日期分组统计每组行数，就是你要的NoSets
    COUNT(*) OVER (PARTITION BY csm_pat_exe_date.pedate_date) AS NoSets
FROM csm_session_patient, csm_session, csm_exercise, csm_patient_exercise, csm_exercise_details, csm_pat_exe_date_set, csm_pat_exe_date 
WHERE 
    csm_session_patient.pat_id= '1'
    AND csm_session_patient.ses_id = csm_session.ses_id 
    AND csm_session.ses_date = csm_pat_exe_date.pedate_date
    AND exe_archived IS NULL  
    AND csm_exercise.exe_id = csm_patient_exercise.exe_id   
    AND csm_patient_exercise.pat_exe_id = '1'   
    AND csm_patient_exercise.exe_id = csm_patient_exercise.pat_exe_id  
    AND csm_patient_exercise.pat_exe_id = csm_pat_exe_date.pat_exe_id 
    AND csm_pat_exe_date.pedate_date >= '2021-06-01'  
    AND csm_pat_exe_date.pedate_date <= '2021-09-24'  
    AND csm_pat_exe_date.pedate_id = csm_pat_exe_date_set.pedate_id 
    AND csm_pat_exe_date_set.ede_id = csm_exercise_details.ede_id  
ORDER BY csm_pat_exe_date.pedate_date, peds_set, ede_order;

方案二：修正你原有的子查询关联写法 #

你原有写法的问题是子查询的过滤条件和外层不匹配，子查询没有加pat_exe_id = '1'和日期范围的限制，导致统计出来的总条数不符合外层筛选后的结果，修正后SQL如下：

SELECT 
    csm_pat_exe_date.pedate_id, 
    csm_patient_exercise.pat_exe_id,  
    csm_exercise_details.ede_id, 
    csm_exercise.exe_id, 
    ses_pat_id, 
    ses_pat_note, 
    ses_pat_body_weight, 
    ses_pat_blood_pressure, 
    ses_pat_blood_glucose_level,
    exe_name, 
    ede_order, 
    ede_type,  
    ede_unit, 
    weekofyear(csm_pat_exe_date.pedate_date) AS weekNumber, 
    csm_pat_exe_date.pedate_date, 
    peds_id, 
    peds_set, 
    peds_result, 
    pedate_note, 
    t2.noSets  
FROM csm_session_patient, csm_session, csm_exercise, csm_patient_exercise, csm_exercise_details, csm_pat_exe_date_set, csm_pat_exe_date 
INNER JOIN ( 
    SELECT 
        pedate_date, 
        COUNT(*) as noSets 
    FROM csm_patient_exercise, csm_pat_exe_date
    WHERE  
        csm_patient_exercise.pat_id = '1'
        AND csm_patient_exercise.pat_exe_id = '1' -- 和外层过滤条件保持一致
        AND csm_patient_exercise.pat_exe_id = csm_pat_exe_date.pat_exe_id
        AND csm_pat_exe_date.pedate_date >= '2021-06-01' -- 和外层过滤条件保持一致
        AND csm_pat_exe_date.pedate_date <= '2021-09-24' -- 和外层过滤条件保持一致
    GROUP BY pedate_date 
) t2 ON csm_pat_exe_date.pedate_date = t2.pedate_date
WHERE 
    csm_session_patient.pat_id= '1'
    AND csm_session_patient.ses_id = csm_session.ses_id 
    AND csm_session.ses_date = csm_pat_exe_date.pedate_date
    AND exe_archived IS NULL  
    AND csm_exercise.exe_id = csm_patient_exercise.exe_id   
    AND csm_patient_exercise.pat_exe_id = '1'   
    AND csm_patient_exercise.exe_id = csm_patient_exercise.pat_exe_id  
    AND csm_patient_exercise.pat_exe_id = csm_pat_exe_date.pat_exe_id 
    AND csm_pat_exe_date.pedate_date >= '2021-06-01'  
    AND csm_pat_exe_date.pedate_date <= '2021-09-24'  
    AND csm_pat_exe_date.pedate_id = csm_pat_exe_date_set.pedate_id 
    AND csm_pat_exe_date_set.ede_id = csm_exercise_details.ede_id  
ORDER BY csm_pat_exe_date.pedate_date, peds_set, ede_order;

额外优化建议 #

你现在用的是隐式连接（FROM后面跟多个表用逗号分隔），建议改成显式的JOIN写法，可读性更强，也更容易排查关联条件错误。

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