MySQL多表关联查询指定周期账单数据结果不符求助

原语句问题分析 #

• 关联逻辑仅判断了用户专属配置和组配置账单类型相等的场景，未覆盖同账单类型下优先级取值的通用逻辑
• 子查询过滤条件与外层GROUP BY搭配不当，MySQL非严格模式下GROUP BY未覆盖所有非聚合字段会取随机行，导致结果不符合预期
• 多余的嵌套JOIN逻辑提升了查询复杂度和出错概率

正确查询语句 #

兼容MySQL 8.0+ 窗口函数版本（逻辑更清晰） #

SET @IDPERIODS:=2;
WITH bill_config AS (
    SELECT 
        u.user_id,
        u.id_user_group,
        s.id_setting_bill,
        s.id_bill_type,
        s.amount_bill,
        -- 配置优先级：用户专属>组通用，数字越小优先级越高
        ROW_NUMBER() OVER (PARTITION BY u.user_id, s.id_bill_type ORDER BY CASE WHEN s.id_user > 0 THEN 1 ELSE 2 END) AS priority
    FROM _t_data_user u
    JOIN _t_setting_bill_user s 
        ON s.id_period = @IDPERIODS
        AND (s.id_user = u.user_id OR (s.id_group_user = u.id_user_group AND s.id_user = 0))
)
SELECT 
    user_id AS iduser,
    id_setting_bill AS idsettingbill,
    id_user_group AS group_user,
    id_bill_type AS idbilltype,
    @IDPERIODS AS period,
    amount_bill AS amount_billing
FROM bill_config
WHERE priority = 1;

兼容MySQL 5.x版本 #

SET @IDPERIODS:=2;
SELECT 
    u.user_id AS iduser,
    COALESCE(a.id_setting_bill, b.id_setting_bill) AS idsettingbill,
    u.id_user_group AS group_user,
    COALESCE(a.id_bill_type, b.id_bill_type) AS idbilltype,
    @IDPERIODS AS period,
    COALESCE(a.amount_bill, b.amount_bill) AS amount_billing
FROM _t_data_user u
-- 关联用户组通用配置
LEFT JOIN _t_setting_bill_user b 
    ON b.id_group_user = u.id_user_group 
    AND b.id_user = 0 
    AND b.id_period = @IDPERIODS
-- 关联用户专属配置
LEFT JOIN _t_setting_bill_user a 
    ON a.id_user = u.user_id 
    AND a.id_period = @IDPERIODS
    AND a.id_bill_type = COALESCE(b.id_bill_type, a.id_bill_type)
-- 过滤有有效配置的记录
WHERE COALESCE(a.id_setting_bill, b.id_setting_bill) IS NOT NULL
GROUP BY u.user_id, idbilltype;

执行结果验证 #

当@IDPERIODS=2时，输出结果如下：

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