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',0; insert into feature select 'View content',2,0;
我想查看功能表中的数据,而不是userid,我想要用户名.
这里的问题是如果userkey为0,则从liteuser表获取用户名,否则从premiumuser表获取用户名.
数据应该是这样的
'Upload content','stephen',1 'Create account','apollo',0 'View content','venus',0
解决方法
试试这个：
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
