表1
|
Id |
Name |
|
1 |
张三 |
|
2 |
李四 |
|
3 |
王五 |
表二
|
Id |
Name1 |
Name2 |
|
1 |
1 |
2 |
|
2 |
2 |
1 |
|
3 |
2 |
3 |
|
4 |
1 |
3 |
我现在要查出结果如下：
|
Id |
Name1 |
Name2 |
|
1 |
张三 |
李四 |
|
2 |
李四 |
张三 |
|
3 |
李四 |
王五 |
|
4 |
张三 |
王五 |
这条sql怎么写？
注：SQL Server数据库
第一种比较笨的：
select id,(select top 1 name from t1 as b where b.id=a.name1 ) as name1,
(select top 1 name from t1 as b where b.id=a.name2 ) as name2
from t2 as a
(select top 1 name from t1 as b where b.id=a.name2 ) as name2
from t2 as a
第二中关联的：
select a.id,b.name,C.name from t2 as a left join t1 as b on a.name1=b.id left join t1 as c on a.name2=C.id