连接表的 SQL 别名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4071909/
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
SQL Alias of joined tables
提问by Lincecum
I have a query like this:
我有一个这样的查询：
select a1.name, b1.info
from (select name, id, status
from table1 a) as a1
right outer join (select id, info
from table2 b) as b1 on (a1.id = b1.id)
I only want to include everything where a1.status=1 and since I'm using an outer join, I can't just add a whereconstraint to table1, because all info from table2 that I want to be excluded will still be there, just without the name. I was thinking something like this:
我只想包含 a1.status=1 的所有内容，并且由于我使用的是外连接，因此我不能只where向 table1添加约束，因为我想要排除的 table2 中的所有信息仍然存在，只是没有名字。我在想这样的事情：
select z1.name, z1.info
from ((select name, id, status
from table1 a) as a1
right outer join (select id, info
from table2 b) as b1 on (a1.id = b1.id)) as z1
where z1.status = 1
but I don't think that's legal.
但我认为这不合法。
EDIT: As described below, an outer join actually doesn't make sense for what I'm trying to do. What if, for example, I want all the data from table2 where status!=1 in table1, inclusive of all data where a corresponding ID does not at all exist in table1. Thus I would need an outer join of all data from table2, but still want to exclude those entries where the status=1.
编辑：如下所述，外连接实际上对我想要做的事情没有意义。例如，如果我想要 table2 中的所有数据，其中 status!=1 在 table1 中，包括 table1 中根本不存在相应 ID 的所有数据，该怎么办。因此，我需要对 table2 中的所有数据进行外连接，但仍想排除那些 status=1 的条目。
Equivalent to this:
相当于：
select z1.name, z1.info
from ((select name, id, status
from table1 a) as a1
right outer join (select id, info
from table2 b) as b1 on (a1.id = b1.id)) as z1
where z1.status != 1
回答by Dustin Laine
SELECT a1.Name, b1.Info
FROM table2 b1
JOIN table2 a1 ON b1.id= a1.id AND a1.status = 1
A right outer join does the exact same thing as a left outer join, with just the tables switched. You can filter on the join and it will still include the data from the initial table.
右外连接与左外连接执行完全相同的操作，只是交换了表。您可以对连接进行过滤，它仍将包含初始表中的数据。
回答by Samuel Neff
Add the whereclause to the subquerylike this:
像这样添加where子句subquery：
select a1.name, b1.info from
(
select name, id
from table1 a
where a.status = 1
) as a1
right outer join
(
select id, info
from table2 b
) as b1 on (a1.id=b1.id)
回答by JNK
select a1.name, b1.info from
(select name, id, status from table1 a WHERE status=1) as a1
right outer join
(select id, info from table2 b) as b1 on (a1.id=b1.id)
EDIT:
编辑：
For your second scenario:
对于您的第二个场景：
select a1.name, b1.info from
(select name, id, status from table1 a) as a1
right outer join
(select id, info from table2 b) as b1 on (a1.id=b1.id)
EXCEPT
select a1.name, b1.info from
(select name, id, status from table1 a WHERE status<>1) as a1
right outer join
(select id, info from table2 b) as b1 on (a1.id=b1.id)
That should work since you will get all the table2 data regardless.
这应该有效，因为无论如何您都会获得所有 table2 数据。
EDIT 2:
编辑2：
OK, to get everything from table2 EXCEPT where there is a status ID in table 1, even if there is not an entry in table1, you need to use the EXCEPTfunction, which will basically exclude a subset from a larger dataset.
好的，要从 table2 中获取所有内容，除了表 1 中有一个状态 ID，即使 table1 中没有条目，您也需要使用该EXCEPT函数，该函数基本上会从更大的数据集中排除一个子集。
