SQL 表的联合查询问题

Question

                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 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 ;这个可以，试试。