多表合并的问题 请大家帮帮忙
有表四张
表A 如下
djbhspidshlpihao
a001A20123
a001B5223
a001C10456
表 B 如下
djbhspidshlpihao
a002A10123
a002A10789
a002B20224
a002B10223
a002C30456
表 C 如下
djbhspidshlpihao
a003B10223
a003B5662
a003C10456
表 S 如下
spidspmch
A茅台
B五粮液
C古井贡
想实现如下结果
spidspmchpihaohjsldjbh1shl1djbh2shl2djbh3shl3
A茅台12380a00120a00210nullnull
A茅台78910nullnulla00210nullnull
B五粮液22325a0015a00210a00310
B五粮液22420nullnulla00220nullnull
C古井贡45650a00110a00230a00310
小弟初学请大家帮帮忙 谢谢了
[解决办法]
SELECT A.SPID AS SPID,A.SPMCH AS SPMCH,B.HJSL AS HJSL,B.PIHAO AS PIHAO,C.DJBH AS DJBH1，C.SHL AS SHL1，D.DJBH AS DJBH2，D.SHL AS SHL2，E.DJBH AS DJBH3，E.SHL AS SHL3 FROM S A LEFT JOIN (SELECT SUM(SHL) HJSL,SPID,PIHAO FROM(SELECT SHL,SPID,PIHAO FROM A UNION ALL SELECT SHL,SPID,PIHAO FROM B UNION ALL SELECT SHL,SPID,PIHAO FROM C)GROUP BY SPID,PIHAO)B LEFT JOIN A C ON A.SPID=C.SPID LEFT JOIN B D ON A.SPID=D.SPID LEFT JOIN C E ON A.SPID=E.SPID ORDER BY A.SPID,A.SPMCH,B.PIHAO
