如何在MySQL中结合UNION ALL与LEFT JOIN实现指定查询

完整SQL查询语句 #

SELECT
    t.id AS `id erogation`,
    u.FullName AS `User FullName`,
    p.ProductName AS `ProductName (1,2,3)`,
    t.DateErogation
FROM (
    SELECT id, UserId, DateErogation, ProductId1 AS ProductId FROM tblerogation
    UNION ALL
    SELECT id, UserId, DateErogation, ProductId2 AS ProductId FROM tblerogation
    UNION ALL
    SELECT id, UserId, DateErogation, ProductId3 AS ProductId FROM tblerogation
) t
LEFT JOIN tbluser u ON t.UserId = u.id
LEFT JOIN tblproduct p ON t.ProductId = p.id
WHERE
    -- 修正字段名拼写错误，同时改用更高效的当月过滤方式
    t.DateErogation >= DATE_FORMAT(NOW(), '%Y-%m-01')
    AND t.DateErogation < DATE_FORMAT(NOW() + INTERVAL 1 MONTH, '%Y-%m-01')
ORDER BY t.UserId;

关键说明 #

1. 子查询处理：通过UNION ALL将tblerogation中三个ProductId字段拆分为独立行，让每个产品发放记录单独成一行，匹配目标结果的结构要求。
2. 关联表逻辑：
   • 用LEFT JOIN tbluser关联用户表，通过UserId匹配tbluser.id，获取对应用户全名。
   • 用LEFT JOIN tblproduct关联产品表，通过拆分后的ProductId匹配tblproduct.id，获取对应产品名称。
3. 过滤条件优化：原代码的MONTH/YEAR函数调用无法利用DateErogation字段的索引，改用日期范围查询既提升效率，又避免跨年度的月份匹配错误（比如12月和次年1月的月份值相同问题）。
4. 字段别名设置：通过AS给字段设置别名，完全匹配你需要的结果列名。

注意事项 #

• 如果tblerogation表中ProductId1/2/3存在空值，LEFT JOIN会保留这些记录，对应ProductName显示为NULL；若要过滤无产品的记录，可将LEFT JOIN改为INNER JOIN。
• 修正了你原代码中的字段名拼写错误：DataERogazione应为DateErogation，避免查询报错。

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