MySQL 使用 UNION 跨多个表进行选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6066197/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Selecting across multiple tables with UNION
提问by cabaret
I reworked my database from one user table to multiple user tables (divided per role): tblStudents, tblTeachers, tblAdmin
我将我的数据库从一个用户表改造成多个用户表（按角色划分）：tblStudents, tblTeachers,tblAdmin
When logging in, I didn't want to run three queries to check if the user exists somewhere in my DB. So what I did was put together the following query with union
登录时，我不想运行三个查询来检查用户是否存在于我的数据库中。所以我所做的是将以下查询放在一起union
select s.id as id, s.email as email, s.password as password, s.role as role from tblStudents s
union
select a.id as id, a.email as email, a.password as password, a.role as role from tblAdmin a
union
select t.id as id, t.email as email, t.password as password, t.role as role from tblTeachers t
This selects the fields that are the same across all tables and outputs the results nicely for me.
这会选择所有表中相同的字段，并为我很好地输出结果。
So, I decided to try this and for some reason, my login form wouldn't work. For my login form, I added a whereclause which checks for the email address. I ran the query in my database app and surprisingly, when I do for example where email = "[email protected]"(this email exists in my database tblAdmin), it also selects a record from my students table.
所以，我决定尝试这个，但出于某种原因，我的登录表单不起作用。对于我的登录表单，我添加了一个where检查电子邮件地址的子句。我在我的数据库应用程序中运行了查询，令人惊讶的是，当我这样做时where email = "[email protected]"（此电子邮件存在于我的数据库中tblAdmin），它还从我的学生表中选择了一条记录。
With the where clause:
使用 where 子句：
select s.id as id, s.email as email, s.password as password, s.role as role from tblStudents s
union
select a.id as id, a.email as email, a.password as password, a.role as role from tblAdmin a
union
select t.id as id, t.email as email, t.password as password, t.role as role from tblTeachers t
where email = "[email protected]"
The records both have id = 1but I don't understand why it would select the student record when I'm filtering on the admin email address. Why is this? Can someone explain and provide me with a better solution to this problem? I basically have one login form and need to select across multiple tables to check if the user exists in my db.
记录两者，have id = 1但我不明白为什么在我过滤管理员电子邮件地址时会选择学生记录。为什么是这样？有人可以解释并为我提供更好的解决方案吗？我基本上有一个登录表单，需要跨多个表进行选择以检查用户是否存在于我的数据库中。
回答by El Yobo
Thanks for updating the query; now we can see that the WHERE condition is only applied to the lastUNIONed query. You need to eitheradd that WHERE clause to each query, or wrap it as a subselect and apply the WHERE clause to that.
感谢您更新查询；现在我们可以看到 WHERE 条件只应用于最后一个UNIONed 查询。你需要要么添加WHERE子句中每个查询，或者把它包装成一个子查询和WHERE子句应用到这一点。
select s.id as id, s.email as email, s.password as password, s.role as role from tblStudents s
where email = "[email protected]"
union
select a.id as id, a.email as email, a.password as password, a.role as role from tblAdmin a
where email = "[email protected]"
union
select t.id as id, t.email as email, t.password as password, t.role as role from tblTeachers t
where email = "[email protected]"
or
或者
SELECT * FROM (
select s.id as id, s.email as email, s.password as password, s.role as role from tblStudents s
union
select a.id as id, a.email as email, a.password as password, a.role as role from tblAdmin a
union
select t.id as id, t.email as email, t.password as password, t.role as role from tblTeachers t
) foo where email = "[email protected]"
回答by sajjad parmar
SELECT
R.co_C,
company3.Statuss
FROM
(
SELECT
company1.co_C
FROM
company1
UNION ALL
SELECT
company2.co_C
FROM
company2
) AS R
LEFT JOIN company3 ON R.co_C = company3.co_A;
