table1
id name
1 A
2 B
3 C
table2
id subject score
1 X 10
1 Y 20
2 X 30
table3
X_score Y_score
A 10 20
B 30 0
C 0 0
请问各位大神，如何查找table1及table2得到table3?这是参加滴滴面试的一道数据库的题，至今我也不会回答，麻烦大神帮忙解答一下
select
t1.name,
cast(case when t2.subject='X' then score end) as X_score,
cast(case when t2.subject='Y then score end) as Y_score,
from table1 t1
left join table2 t2 on t1.id=t2.id
大概就是这样子
select t1.name,max(case when t2.subject='X' then score else 0 end) as X_score,max(case when t2.subject='Y' then score else 0 end) as Y_score from table1 t1 left join table2 t2 on t1.id=t2.id GROUP BY name
select u.name,sum(u.X_score)as X_score,sum(u.Y_score)as Y_score from table1 tt1 left join
(select
t1.name,
(case when t2.subject='X' then score else '0' end) as X_score,
(case when t2.subject='Y' then score else '0' end) as Y_score
from TABLE1 t1
left join TABLE2 t2 on t1.id=t2.pid )u on tt1.name=u.name group by u.name
pid就是table2 表的 id