MySQL中CASE语句关联多表查询无结果问题求助

问题描述 #

编写MySQL查询时遇到问题，现有orders、users、referrals三张表，需通过关联查询识别贡献度更高的用户，期望得到指定格式结果，但尝试带CASE语句的JOIN查询返回空结果，请求解决。

表结构 #

orders表 #

users表 #

referrals表 #

期望结果 #

尝试的SQL语句 #

select u.name, u.referal_code, r.model_type from refferal r join orders o on r.model_id = case
when r.model_type = 'model\order' then o.id
end
join users u on u.id = case
when r.model_type = 'model\user' then r.model_id
end

问题分析 #

1. 表名拼写错误：将referrals误写为refferal，导致无法正确关联表。
2. JOIN类型错误：使用INNER JOIN时，CASE语句不满足条件会返回NULL，导致关联失败，整条记录被过滤。例如：
   • 对于model_type='model\order'的记录，关联users表的CASE条件返回NULL，u.id=NULL不成立，记录被过滤。
   • 对于model_type='model\user'的记录，关联orders表的CASE条件返回NULL，o.id=NULL不成立，记录被过滤。
3. 关联逻辑不清晰：CASE语句在JOIN条件中无法同时适配两种关联场景，导致部分记录无法匹配。

修正后的SQL语句 #

方案1：使用UNION ALL拆分两种场景（推荐，逻辑清晰） #

-- 处理model\user类型的推荐记录
SELECT
    u.name AS `users.name`,
    u.referal_code AS `users.referal_code`,
    r.model_type AS `referrals.model_type`
FROM referrals r
INNER JOIN users u ON r.model_type = 'model\\user' AND r.model_id = u.id

UNION ALL

-- 处理model\order类型的推荐记录（假设订单id关联到对应用户，此处需根据实际业务调整关联逻辑）
SELECT
    u.name AS `users.name`,
    u.referal_code AS `users.referal_code`,
    r.model_type AS `referrals.model_type`
FROM referrals r
INNER JOIN orders o ON r.model_type = 'model\\order' AND r.model_id = o.id
INNER JOIN users u ON u.id = 1; -- 示例：假设订单id=1属于用户joe（id=1），实际场景应替换为o.user_id = u.id

方案2：使用LEFT JOIN+条件判断 #

SELECT
    COALESCE(u_user.name, u_order.name) AS `users.name`,
    COALESCE(u_user.referal_code, u_order.referal_code) AS `users.referal_code`,
    r.model_type AS `referrals.model_type`
FROM referrals r
-- 关联model\user对应的用户
LEFT JOIN users u_user ON r.model_type = 'model\\user' AND r.model_id = u_user.id
-- 关联model\order对应的用户（假设订单关联用户逻辑）
LEFT JOIN orders o ON r.model_type = 'model\\order' AND r.model_id = o.id
LEFT JOIN users u_order ON u_order.id = 1 -- 实际场景替换为o.user_id = u_order.id
WHERE COALESCE(u_user.id, u_order.id) IS NOT NULL;

说明 #

若实际业务中orders表存在关联用户的字段（如user_id），需将上述SQL中硬编码的用户关联逻辑替换为o.user_id = u.id，确保查询的通用性。UNION ALL方案更易维护，适合拆分不同类型的业务逻辑；LEFT JOIN方案适合需要统一处理的场景。

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