SQL 表的联合查询问题

                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 name,(select score X_score from table2 t2 where t2.id=t1.id and subject ='X'),(select score Y_score from table2 t2 where t2.id=t1.id and subject ='Y') from table1 t1 ;这个可以，试试。

这个问题刚好最近别人问过类似的。要回家。明天有空来回答下

select name,(select score X_score from table2 t2 where t2.id=t1.id and subject ='X'),(select score Y_score from table2 t2 where t2.id=t1.id and subject ='Y') from table1 t1 ;

select t1.name, IFNULL(g2.score, 0) as X_score,IFNULL(g3.score, 0) as Y_score
from t1
left join t2 g2 on t1.id=g2.id and (g2.subject = 'X' or g2.subject='Z')
left join t2 g3 on t1.id = g3.id and g3.subject = 'Y'

两种方法
select name ,max(case subject when 'x' then score else 0 end) X_score,
max(case subject when 'y' then score else 0 end) Y_score from table1 left
join table2 on table1.id = table2.id group by name

select name ,x as X_score,y as Y_score from (select table1.id,table1.name,table2.subject,table2.score from table1 left join table2 on table1.id = table2.id) as S
pivot(max(score) for subject in (x,y)) as table3