Python MySQL查询合并报错：UNION用法不当，求排查及优化方案

首先，你的查询报错的核心原因有两个：

1. UNION ALL后的WHERE子句无效：tb2x和tb2y是两个子查询内部的表别名，只在各自的子查询范围内生效，UNION ALL合并结果后，外层无法引用这些别名，所以where tb2x.id=tb2y.id这一行完全不符合MySQL的语法规则。
2. ORDER BY字段不存在：你的两个子查询都只返回了id列，合并后的结果集里并没有tb1y.created这个字段，自然无法用它来排序。

从你的写法来看，我猜测你想实现的需求是：找出所有关联到同一个table2 id的table1记录中，同时存在created > CURDATE()和created < CURDATE()的行，然后把这些table1的id按创建时间倒序排列。如果是这样的话，下面是两种更合理的实现方式：

方法一：先筛选符合条件的table2 id，再关联查询 #

这种方式先通过子查询找出那些同时拥有今天前后记录的table2 id，再关联table1获取对应的所有记录，逻辑清晰且性能较好：

SELECT tb1.id
FROM table1 tb1
JOIN table2 tb2 ON tb2.id = tb1.cid
WHERE tb2.id IN (
    -- 找出同时存在今天前后记录的table2 id
    SELECT tb2_inner.id
    FROM table1 tb1_inner
    JOIN table2 tb2_inner ON tb2_inner.id = tb1_inner.cid
    GROUP BY tb2_inner.id
    HAVING 
        -- 至少有一条今天之后的记录
        SUM(CASE WHEN tb1_inner.created > CURDATE() THEN 1 ELSE 0 END) > 0
        -- 至少有一条今天之前的记录
        AND SUM(CASE WHEN tb1_inner.created < CURDATE() THEN 1 ELSE 0 END) > 0
)
ORDER BY tb1.created DESC;

方法二：用窗口函数标记符合条件的记录 #

如果你的MySQL版本支持窗口函数（8.0+），可以用更简洁的方式直接标记每个table2 id对应的记录是否同时包含前后时间的行：

SELECT id
FROM (
    SELECT 
        tb1.id,
        tb1.created,
        -- 标记当前table2 id是否有今天之后的记录
        MAX(CASE WHEN tb1.created > CURDATE() THEN 1 ELSE 0 END) OVER (PARTITION BY tb2.id) AS has_future,
        -- 标记当前table2 id是否有今天之前的记录
        MAX(CASE WHEN tb1.created < CURDATE() THEN 1 ELSE 0 END) OVER (PARTITION BY tb2.id) AS has_past
    FROM table1 tb1
    JOIN table2 tb2 ON tb2.id = tb1.cid
) AS sub
WHERE has_future = 1 AND has_past = 1
ORDER BY created DESC;

如果你的需求只是简单获取table1中关联table2的所有今天前后的记录，不需要限定同一个table2 id同时存在两种记录，那修正后的基础查询应该是这样的（需要保留created字段用于排序）：

SELECT tb1.id, tb1.created
FROM table1 tb1
JOIN table2 tb2 ON tb2.id = tb1.cid
WHERE tb1.created > CURDATE()
UNION ALL
SELECT tb1.id, tb1.created
FROM table1 tb1
JOIN table2 tb2 ON tb2.id = tb1.cid
WHERE tb1.created < CURDATE()
ORDER BY created DESC;

另外，注意CURDATE()返回的是当前日期（不含时间），如果你的created字段包含时间，可能需要用CURRENT_DATE()或者DATE(NOW())来做对比，避免时间部分的干扰。

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