SQL 根据条件连接不同的表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16270203/
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
Join different tables based on condition
提问by Null Head
create table [premiumuser] (user_id int, name nvarchar(50));
create table [liteuser] (user_id int, name nvarchar(50));
create table [feature] (id nvarchar(50), user_id int, userkey int);
insert into [premiumuser] select 1, 'stephen';
insert into [premiumuser] select 2, 'roger';
insert into [liteuser] select 1, 'apollo';
insert into [liteuser] select 2, 'venus';
insert into feature select 'Upload content', 1, 1;
insert into feature select 'Create account', 1, 0;
insert into feature select 'View content', 2, 0;
I would like to see data from featuretable and instead of useridi want the username.
The catch here is if userkeyis 0, get the usernamefrom liteusertable, else from premiumusertable.
Data should be like
我想查看特征表中的数据，而不是userid我想要username.
这里的问题是如果userkey为 0，username则从liteuser表中获取，否则从premiumuser表中获取。
数据应该像
'Upload content', 'stephen', 1
'Create account', 'apollo', 0
'View content', 'venus', 0
回答by Pieter Geerkens
Try this:
尝试这个：
select
f.id,
case when userkey=0 then l.name else p.name end as username
from [feature] f
left join [liteuser] l on l.user_id = f.user_id
left join [premium user] p on p.user_id = f.user_id
回答by Esoteric Screen Name
SELECT f.id
, (CASE WHEN f.userkey = 0 THEN l.name ELSE p.name END) AS name
, f.userkey
FROM feature f
LEFT JOIN liteuser l on f.user_id = l.user_id
LEFT JOIN premiumuser p on p.user_id = l.user_id
I suggest using left joins over inner joins, as you seem to be asking about all users associated with a given feature. This way, features with no associated users will have NULL as the name. Additionally, using two inner joins against the user tables will only return only those features which have an entry for the same user in both the premium and lite tables.
我建议使用左连接而不是内连接，因为您似乎在询问与给定功能相关联的所有用户。这样，没有关联用户的功能将使用 NULL 作为名称。此外，对用户表使用两个内部联接将仅返回那些在高级表和精简表中具有相同用户条目的功能。
回答by Mudassir Hasan
You need to know about JOINSand CASEcondition.
SELECT f.id , CASE userkey WHEN 0 then l.name ELSE p.name END,f.userkey
FROM feature f
INNER JOIN liteuser
ON f.user_id=l.user_id
INNER JOIN premiumuser p
ON f.user_id=p.user_id
EDIT :
编辑 ：
As suggested by Pieter Geerkens , a LEFT JOIN may be required by you. LEFT JOIN results in retrieving matching rows from both tables PLUS non matching rows from table situated on leftside of join . The rows from rightside table are filled with NULL value.
根据 Pieter Geerkens 的建议，您可能需要 LEFT JOIN。LEFT JOIN 导致从两个表中检索匹配的行以及从位于 join 左侧的表中检索不匹配的行。右侧表中的行填充为 NULL 值。
INNER JOIN only gives rows having matching value in joining field.
INNER JOIN 只给出在连接字段中具有匹配值的行。
