怎么通过不同的值查找不同的表

[navisl]

我现在有3个表:User表 Expert表 Enterprise表

怎么通过User表中的UserType来确定是查找Expert表还是查找Enterprise表

并且能生成一个视图

[navisl]

[Quote=引用 2 楼 的回复:]
SQL code


--> 测试数据：@User
declare @User table([ID] int,[Name] varchar(1),[UserType] int)
insert @User
select 1,'a',1 union all
select 2,'b',1 union all
select 3,'c',2 union all
select 4,'d',2……
[/Quote]
请问如果我还要关联一个和User表中的ID有关的表 该怎么加呢

[--小F--]

if exists(select * from user where usetype=1)

select * from expert

else

select * from enterprise

[叶子]

--> 测试数据：@User
declare @User table([ID] int,[Name] varchar(1),[UserType] int)
insert @User
select 1,'a',1 union all
select 2,'b',1 union all
select 3,'c',2 union all
select 4,'d',2

--> 测试数据：@Expert
declare @Expert table([ID] int,[c1] varchar(1))
insert @Expert
select 1,'a' union all
select 2,'b' union all
select 3,'c' union all
select 4,'d'

--> 测试数据：@Enterprise
declare @Enterprise table([ID] int,[c1] varchar(1))
insert @Enterprise
select 5,'a' union all
select 6,'b' union all
select 7,'c' union all
select 8,'d'

select * from @User a
LEFT JOIN @Expert b ON a.[UserType]=1 AND a.name=b.[c1]
LEFT JOIN @Enterprise c ON a.[UserType]=2 AND a.name=c.[c1] 

/*
ID          Name UserType    ID          c1   ID          c1
----------- ---- ----------- ----------- ---- ----------- ----
1           a    1           1           a    NULL        NULL
2           b    1           2           b    NULL        NULL
3           c    2           NULL        NULL 7           c
4           d    2           NULL        NULL 8           d
*/

[问苍天人生几何]

select a.UserType,
	case when b.UserType is not null and c.UserType is null then '只存在于Expert表中'
	when b.UserType is not null and c.UserType is not null then 'Expert和Enterprise表都有'
	when b.UserType is null and c.UserType is not null then '只存在于Enterprise表中' end Flag
from User a
left join Expert b
on a.UserType=b.UserType
left join Enterprise c
on a.UserType=c.UserType

[筱筱澄]

[Quote=引用 1 楼 的回复:]

SQL code
if exists(select * from user where usetype=1)

select * from expert

else

select * from enterprise
[/Quote]
+1