mysql 在SQL中对UNION使用单个公共WHERE条件

okxuctiv 于 2023-01-01 发布在 Mysql

关注(0)|答案(3)|浏览(144)

我正在尝试做这样的事情：

SELECT a.date AS EnrollDate, a.id, a.name, b.address FROM student a JOIN Location b ON a.id=b.id  
UNION  
SELECT a.date AS EnrollDate, a.id, a.name, b.address FROM teacher a JOIN Location b ON a.id=b.id  
WHERE a.date>'2010-01-01'  
ORDER BY EnrollDate

但是WHERE条件只应用于第二个SELECT语句。我需要以某种方式应用于两个SELECT语句。我现在唯一的选择是单独应用WHERE条件。但是我正在使用几个UNION，在所有地方都包含WHERE有点乏味。我想知道是否有一个简单的方法。
顺便说一下，我正在使用MySQL。

mysql

来源：https://stackoverflow.com/questions/3156908/using-a-single-common-where-condition-for-union-in-sql

3条答案

按热度按时间

i1icjdpr1#

SELECT * FROM (
    SELECT a.date AS EnrollDate, a.id, a.name, b.address FROM student a 
    JOIN Location b ON a.id=b.id  
    UNION  
    SELECT a.date AS EnrollDate, a.id, a.name, b.address FROM teacher a 
    JOIN Location b ON a.id=b.id  
) A
WHERE EnrollDate > '2010-01-01'  
ORDER BY EnrollDate

与单个ORDER BY相比，这也有一个优点，即整个结果的顺序正确。

赞(0）回复(0）举报 2023-01-01

xytpbqjk2#

您是否尝试过以下方法：

SELECT * FROM 
(
    SELECT a.date AS EnrollDate, a.id, a.name, b.address FROM student a JOIN Location b ON a.id=b.id  
    UNION  
    SELECT a.date AS EnrollDate, a.id, a.name, b.address FROM teacher a JOIN Location b ON a.id=b.id 
) A
    WHERE a.date>'2010-01-01'  
    ORDER BY EnrollDate

赞(0）回复(0）举报 2023-01-01

xyhw6mcr3#

这是没有办法的，您必须为每个单独的select子句重复WHERE。

赞(0）回复(0）举报 2023-01-01