联接表的SQL别名 [英] SQL Alias of joined tables

问题描述

我有这样的查询:

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)

我只想包含a1.status = 1的所有内容，并且由于我使用的是外部联接，所以我不能只向表1添加where约束，因为我想排除所有来自表2的信息仍会在那里，只是没有名字.我在想这样的事情:

I only want to include everything where a1.status=1 and since I'm using an outer join, I can't just add a where constraint 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:

 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

但我认为这不合法.

如下所述，外部联接实际上对我要执行的操作没有任何意义.例如，如果我想要来自table2的所有数据，其中table1中status！= 1，包括所有没有对应ID的数据都存在于table1中.因此，我将需要对table2中的所有数据进行外部联接，但仍要排除status = 1的那些条目.

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.

等效于此:

 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

推荐答案

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.

这篇关于联接表的SQL别名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！