SQL 表的联合查询问题

Question

               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?这是参加滴滴面试的一道数据库的题，至今我也不会回答，麻烦大神帮忙解答一下

Answer 1

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
大概就是这样子