向各位高手请教一个SQL语句
表1 (班级表)
id uid_1 uid_2 uid_3 uid_4 uid_5
1 2 1 3 5 7
表2(学生表)
uid name
1 a
2 b
3 c
4 d
5 e
6 f
7 g
要求显示成下列结果:
id uid_1 uid_2 uid_3 uid_4 uid_5
1 b a c e g
用left join 写起来比较复杂，请问还有没有别的简单一些的写法?
[解决办法]
就会left join吧,用子查询还没left join好.
[解决办法]
用Left Join實際更好些
Select
A.id,
B.name As uid_1,
C.name As uid_2,
D.name As uid_3,
E.name As uid_4,
F.name As uid_5
From
班级表 A
Left Join
学生表 B
On A.uid_1 = B.uid
Left Join
学生表 C
On A.uid_2 = C.uid
Left Join
学生表 D
On A.uid_3 = D.uid
Left Join
学生表 E
On A.uid_4 = E.uid
Left Join
学生表 F
On A.uid_5 = F.uid
[解决办法]
select a.id,
max(case when a.uid_1=b.uid then b.name end) as uid_1,
max(case when a.uid_2=b.uid then b.name end) as uid_2,
max(case when a.uid_3=b.uid then b.name end) as uid_3,
max(case when a.uid_4=b.uid then b.name end) as uid_4,
max(case when a.uid_5=b.uid then b.name end) as uid_5
from 表1 a,表2 b
group by a.id
[解决办法]
這是子查詢的方法
Select
A.id,
(Select name From 学生表 Where uid = A.uid_1) As uid_1,
(Select name From 学生表 Where uid = A.uid_2) As uid_2,
(Select name From 学生表 Where uid = A.uid_3) As uid_3,
(Select name From 学生表 Where uid = A.uid_4) As uid_4,
(Select name From 学生表 Where uid = A.uid_5) As uid_5
From
班级表 A
