sql server 多对多查询

1、需要实现的效果图

 2、建表SQL

DROP TABLE IF EXISTS #TMP_RNO
DROP TABLE IF EXISTS #TMP1
DROP TABLE IF EXISTS #TMP2
DROP TABLE IF EXISTS #TMP3
/*建表脚本*/
CREATE TABLE #TMP1(AID INT,ARNO INT)
CREATE TABLE #TMP2(BID INT,BRNO INT)
CREATE TABLE #TMP3(CID INT,CRNO INT)

SELECT *
INTO #TMP_RNO
FROM(
    SELECT 1 ID,NUMBER RNO 
    FROM(
        SELECT NUMBER+1 NUMBER FROM master..spt_values where type='P' AND NUMBER<5
    )A
    UNION ALL
    SELECT 2,NUMBER RNO 
    FROM(
        SELECT NUMBER+1 NUMBER FROM master..spt_values where type='P' AND NUMBER<5
    )A
)A WHERE 1=1
/*插入数据*/
INSERT INTO #TMP1(AID,ARNO)
SELECT ID,RNO FROM #TMP_RNO WHERE RNO=1

INSERT INTO #TMP2(BID,BRNO)
SELECT ID,RNO FROM #TMP_RNO

INSERT INTO #TMP3(CID,CRNO)
SELECT ID,RNO FROM #TMP_RNO

3、查询数据如下

4、使用FULL OUTER JOIN 连接

4.1、不加任何排序如下（两个表连接的情况） 

    SELECT *
    FROM #TMP1 A FULL OUTER JOIN #TMP2 B ON A.AID=B.BID AND A.ARNO=B.BRNO

 

4.2、不加任何排序如下（三个表连接的情况）

    SELECT *
    FROM #TMP1 A FULL OUTER JOIN #TMP2 B ON A.AID=B.BID AND A.ARNO=B.BRNO
    FULL OUTER JOIN #TMP3 C ON (C.CID=A.AID OR C.CID=B.BID) AND (C.CRNO=A.ARNO OR C.CRNO=B.BRNO)

 

4.3、加任何排序如下（两个表连接的情况）

        SELECT *
        FROM #TMP1 A FULL OUTER JOIN #TMP2 B ON A.AID=B.BID AND A.ARNO=B.BRNO
    ORDER BY ISNULL(A.AID,B.BID),ISNULL(A.ARNO,B.BRNO)    

 

4.4、加任何排序如下（三个表连接的情况）

SELECT *
    FROM #TMP1 A FULL OUTER JOIN #TMP2 B ON A.AID=B.BID AND A.ARNO=B.BRNO
    FULL OUTER JOIN #TMP3 C ON (C.CID=A.AID OR C.CID=B.BID) AND (C.CRNO=A.ARNO OR C.CRNO=B.BRNO)
    ORDER BY ISNULL(ISNULL(A.AID,B.BID),C.CID),ISNULL(ISNULL(A.ARNO,B.BRNO),C.CRNO)

需求是 多对多的查询关系，没有硬性的要求某一个表的行数，可多可少（每个key值对应的数据行数），排序是重点在这个查询结果中，没有排序就实现不了想要的效果