主表A,副表B、C、D。当主表A与副表B、C、D存在一对多关系、切B表2个值、C表3个值、D表3个值时候会出现2*3*3=18条记录。
如果我们想要的结果是：以人员各个子表记录最多的子表作为总的条数（上面的例子显示3条记录）。可按照如下处理：
SQL如下：
select
tablea.AID,
tablea.ANAME,
tablea.BDEPT,
tableB.AID as aidb,
tableB.ANAME as ANAMEb,
tableB.cJIBIE,
tableC.AID as aidc,
tableC.ANAME as ANAMEc,
tableC.DNAME,
concat(CONCAT(tablea.BDEPT,tableB.cJIBIE),tablec.DNAME) AS ZH,
CASE WHEN tablea.AID IS NULL and tableb.AID is null THEN tablec.AID
when tableb.AID is null and tablec.AID is null then tablea.AID
when tablea.AID is null and tablec.AID is null then tableb.AID
ELSE tablea.AID END AS id,
CASE WHEN tablea.ANAME IS NULL and tableb.ANAME IS NULL then tablec.ANAME
WHEN tablea.ANAME IS NULL and tablec.ANAME IS NULL then tableb.ANAME
WHEN tableb.ANAME IS NULL and tablec.ANAME IS NULL then tablea.ANAME
ELSE tablea.ANAME END AS NAME,
tablea.BDEPT as dept,
tableB.cJIBIE as jibie,
tablec.Dname as dname
from (
SELECT
A.ID AID,
A.NAME ANAME,
B.DEPT BDEPT,
B.AID||row_number()over(partition by B.AID order by B.ID) mark
FROM A LEFT JOIN B ON A.ID = B.AID
)tablea
full outer join(
SELECT
A.ID AID,
A.NAME ANAME,
C.JIBIE cJIBIE,
C.AID||row_number()over(partition by C.AID order by C.ID) mark
FROM A
LEFT JOIN C ON A.ID = C.AID
)tableb on tablea.mark=tableb.mark
full outer join(
SELECT
A.ID AID,
A.NAME ANAME,
D.name DNAME,
D.AID||row_number()over(partition by D.AID order by D.ID) mark
FROM A
LEFT JOIN D ON A.ID = D.AID
)tableC on tablea.mark=tablec.mark
WHERE CONCAT(CONCAT(tablea.BDEPT,tableB.CJIBIE),tablec.dname) IS NOT NULL
ORDER BY tableB.ANAME
结果如下：
——感谢罗科指导及互联网上网页提供思路
