mysql - 同时查询多个表的特定日期范围

Question

我有三个相同的表:

• gradbusdb.user
• gradedudb.user
• gradtheologydb.user

在每个表中，我都有 YYYY-MM-DD 格式的 email 和 submited_date 。我的最终目标是同时查询所有三个表，但是当我在 email 和 submited_date 列的 3 个表之间执行 UNION 时它忽略我的 WHERE 条件并显示所有结果。

我当前的查询如下:

SELECT email, submited_date FROM gradbusdb.user 
UNION
SELECT email, submited_date FROM gradedudb.user
UNION
SELECT email, submited_date FROM gradtheologydb.user
WHERE submited_date BETWEEN '2016-02-01' AND '2016-02-29'
ORDER BY submited_date ASC

我不确定我在这里做错了什么，但我需要我的查询仅显示日期范围内的结果。

Answer 1

您应该对联合中的每个查询使用 WHERE

SELECT email, submited_date FROM gradbusdb.user 
WHERE submited_date BETWEEN '2016-02-01' AND '2016-02-29'
UNION
SELECT email, submited_date FROM gradedudb.user
WHERE submited_date BETWEEN '2016-02-01' AND '2016-02-29'
UNION
SELECT email, submited_date FROM gradtheologydb.user
WHERE submited_date BETWEEN '2016-02-01' AND '2016-02-29'

ORDER BY submited_date ASC