有没有更有效的方法来编写这个简单的SQL查询？

Question

这里似乎有很多冗余。有没有更高效的方法，还是需要使用工会？ inner join也在那里，因为players表没有酒店信息，必须从teams中检索。

select players.name 
from players 
inner join teams 
on players.team = teams.name 
where teams.staysat = 'Ambassador' 
and teams.checkin is not null 
and teams.checkin < '18-AUG-07' 
and teams.checkout > '18-AUG-07' 
-- 
union all 
select officials.name 
from officials 
where officials.staysat = 'Ambassador' 
and officials.checkin is not null 
and officials.checkin < '18-AUG-07' 
and officials.checkout > '18-AUG-07' 
-- 
union all 
select fans.name 
from fans 
where fans.staysat = 'Ambassador' 
and fans.checkin is not null 
and fans.checkin < '18-AUG-07' 
and fans.checkout > '18-AUG-07'; 

Answer 1

是的，因为你检索不同的表（球员，球迷，官员）工会需要在这里，我只能想象，你可以缩短这个，如果你将提供拉从表中所需的信息的功能的数据，你会指定为一个字符串，但这会导致建立查询字符串串联或其他headachy东西，所以也许更好地坚持与联盟选择;）

此外，如果表中没有不同的其他列作为那么你可以考虑把它们全部放在一张桌子上，并用旗子或类似的东西来区分它们。

Answer 2

您有正确的方法来做到这一点。工会和内部联合是必要的。

Answer 3

您可以使用子查询或视图（者优先），像这样：

select * from (
select players.name as name, teams.checkin as checkin, teams.checkout as checkount, teams.staysat as staysat 
from players 
inner join teams 
on players.team = teams.name 

union all 
select officials.name as name, officials.checkin as checkin, officials.checkout as checkount, officials.staysat as staysat 
from officials 

union all 

select fans.name as name, fans.checkin as checkin, fans.checkout as checkount, fans.staysat as staysat 
from fans 

) as t 
where t.staysat = 'Ambassador' 
and t.checkin is not null 
and t.checkin < '18-AUG-07' 
and t.checkout > '18-AUG-07'; 

而向外移动的条件，但它可能会更慢，但更简单的