显示所有销售代表（含无销售记录）的SQL查询优化需求

我懂你的需求啦——你要确保当月每一天，所有销售代表都出现在查询结果里，哪怕他们当天没有任何订单。原查询的问题在于它是从实际有销售记录的表（simh_file）出发做关联，自然会漏掉那些当天没业绩的代表。咱们得换个思路：先搭好「当月所有日期 × 所有销售代表」的基础架子，再把销售数据左关联上去，这样没销售的地方就可以补0或者空值了。

解决步骤拆解 #

1. 生成当月的所有日期列表

首先需要得到目标月份的每一天，不同数据库生成日期的方式略有不同，这里以MySQL 8.0+为例（用递归CTE），其他数据库的写法我会在后面补充：

WITH dates AS (
    SELECT DATE '2021-07-01' AS date_val
    UNION ALL
    SELECT date_val + INTERVAL 1 DAY
    FROM dates
    WHERE date_val < DATE '2021-07-31'
)

2. 获取所有销售代表的完整列表

从ctl_file_s表中取出所有有效的销售代表（记得去重，避免重复数据）：

WITH sales_reps AS (
    SELECT DISTINCT sm_name, sm_slm
    FROM ctl_file_s
    -- 如果有在职状态筛选，比如只取在职代表，可以加这里
    -- WHERE sm_active = 'Y'
)

3. 构建「日期+销售代表」的完整组合

把上面两个CTE用CROSS JOIN（笛卡尔积）组合，得到当月每一天每个销售代表的配对，这就是我们需要的基础框架：

WITH dates AS (
    SELECT DATE '2021-07-01' AS date_val
    UNION ALL
    SELECT date_val + INTERVAL 1 DAY
    FROM dates
    WHERE date_val < DATE '2021-07-31'
),
sales_reps AS (
    SELECT DISTINCT sm_name, sm_slm
    FROM ctl_file_s
),
date_rep_combo AS (
    SELECT 
        d.date_val,
        sr.sm_name,
        sr.sm_slm
    FROM dates d
    CROSS JOIN sales_reps sr
)

4. 左关联销售数据并聚合

把原查询中的销售相关表左关联到这个基础框架上，同时用COALESCE把聚合结果的NULL转成0，这样无销售的记录就会显示0而不是空值：

WITH dates AS (
    SELECT DATE '2021-07-01' AS date_val
    UNION ALL
    SELECT date_val + INTERVAL 1 DAY
    FROM dates
    WHERE date_val < DATE '2021-07-31'
),
sales_reps AS (
    SELECT DISTINCT sm_name, sm_slm
    FROM ctl_file_s
),
date_rep_combo AS (
    SELECT 
        d.date_val,
        sr.sm_name,
        sr.sm_slm
    FROM dates d
    CROSS JOIN sales_reps sr
)
SELECT 
    DATE_FORMAT(drc.date_val, '%m/%d/%y') AS "Date",
    drc.sm_name AS "Sales Person Name",
    COALESCE(COUNT(DISTINCT md.simd_inv), 0) AS "Orders",
    COALESCE(SUM(CASE 
                    WHEN i.iv_current IN ('0', '1') THEN 
                        IF(mh.simh_inv < 100000, -md.simd1_shipped, md.simd1_shipped) 
                    ELSE 0 
                END), 0) AS "Pieces",
    COALESCE(SUM(md.simd1_extended), 0) AS "Sales Amount"
FROM date_rep_combo drc
LEFT JOIN simh_file mh 
    ON STR_TO_DATE(mh.simh_kdate, '%Y%m%d') = drc.date_val
    AND mh.simh__slm = drc.sm_slm
LEFT JOIN simd_file md 
    ON md.simd_inv = mh.simh_inv 
    AND md.simd_seq = mh.simh_seq
LEFT JOIN c_file c 
    ON c.cm_custno = mh.simh_kcust
LEFT JOIN i_file i 
    ON i.iv_item = md.simd1_item
GROUP BY drc.date_val, drc.sm_name
ORDER BY drc.date_val, drc.sm_name;

关键注意点 #

• 日期匹配：原查询中mh.simh_kdate是数字格式（如20210701），所以需要用STR_TO_DATE把它转成日期类型，和date_val匹配，避免关联错误。
• 关联条件：左关联simh_file时，必须同时匹配日期和销售代表ID（sm_slm），这样才能准确关联到该代表当天的销售数据。
• COALESCE的作用：聚合函数在没有数据时会返回NULL，用COALESCE(..., 0)把这些NULL转成0，更符合报表的展示需求。

其他数据库的日期生成方式 #

如果你的数据库不是MySQL，比如Oracle：

-- Oracle生成当月日期的写法
WITH dates AS (
    SELECT TO_DATE('2021-07-01', 'YYYY-MM-DD') + LEVEL - 1 AS date_val
    FROM dual
    CONNECT BY LEVEL <= TO_NUMBER(TO_CHAR(LAST_DAY(TO_DATE('2021-07-01', 'YYYY-MM-DD')), 'DD'))
)

SQL Server：

-- SQL Server生成当月日期的写法
WITH dates AS (
    SELECT CAST('2021-07-01' AS DATE) AS date_val
    UNION ALL
    SELECT DATEADD(DAY, 1, date_val)
    FROM dates
    WHERE date_val < EOMONTH('2021-07-01')
)

适配Crystal Reports交叉表 #

这个查询返回的结果已经包含了所有日期+销售代表的组合，直接把它作为Crystal Reports交叉表的数据源即可——交叉表会自动将日期转为列头，销售代表作为行，数值字段作为汇总项，完美满足你的需求。

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