mysql - 将两个 python mysql 查询与条件组合起来

Question

我想抓取两个表在某个时间之前和之后的一些特定行，这是我的代码:

mysql_query = """
select tb1x.id
from table1 as tb1x 
join table2 as tb2x ON tb2x.id = tb1x.cid
where tb1x.created > CURDATE()
UNION ALL
(select tb1y.id
from table1 as tb1y 
join table2 as tb2y ON tb2y.id = tb1y.cid
where tb1y.created < CURDATE()
)
where tb2x.id=tb2y.id
ORDER BY tb1y.created DESC;"""

报错，表明UNION没有正确使用。

我猜问题可能出在这里:AND tb2x.id=tb2y.id您能否帮忙看看问题出在哪里，或者比较表的两个不同部分是否是一种明智的方法？

Answer 1

UNION 不允许列之间进行任何比较，因此 UNION 之后的 where tb2x.id=tb2y.id 是错误

https://en.wikipedia.org/wiki/Set_operations_(SQL)#UNION_operator

要比较表的两个不同部分，您必须JOIN本身:

SELECT *
FROM (
       SELECT
         tb1x.id,
         tb1x.created
       FROM table1 AS tb1x
         JOIN table2 AS tb2x
           ON tb2x.id = tb1x.cid
       WHERE tb1x.created > CURDATE()
     ) AS tb3x
  JOIN
  (
    SELECT
      tb1y.id,
      tb1y.created
    FROM table1 AS tb1y
      JOIN table2 AS tb2y
        ON tb2y.id = tb1y.cid
    WHERE tb1y.created < CURDATE()
  ) AS tb3y

    ON tb3x.id = tb3y.id
ORDER BY tb3y.created DESC;

另一件事是收集一个表中某个时间之前和之后的所有行。您需要使用两个子查询表的UNION，每个子查询表都是输入表的过滤器:

SELECT *
FROM (
  (
    SELECT
      tb1x.id,
      tb1x.created
    FROM table1 AS tb1x
      JOIN table2 AS tb2x
        ON tb2x.id = tb1x.cid
    WHERE tb1x.created > CURDATE()
  )
  UNION ALL
  (
    SELECT
      tb1y.id,
      tb1y.created
    FROM table1 AS tb1y
      JOIN table2 AS tb2y
        ON tb2y.id = tb1y.cid
    WHERE tb1y.created < CURDATE()
  )
)
ORDER BY created DESC;