笛卡尔积:
笛卡尔积在SQL中的实现方式既是
交叉连接(Cross Join)。所有连接方式都会先生成临时笛卡尔积表,笛卡尔积是关系代数里的一个概念,表示
第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。
参见下面的示例:
DECLARE
@Temp
TABLE
(GroupID INT ,
GroupName VARCHAR ( 25 ),
ItemNumber varchar ( 25 )
)
INSERT INTO @Temp
SELECT 1 , ' 5805 ' , ' 27-196-018 '
UNION
SELECT 1 , ' 5805 ' , ' 27-196-019 '
UNION
SELECT 2 , ' 5805 ' , ' 27-196-020 '
UNION
SELECT 2 , ' 5805 ' , ' 27-196-021 '
UNION
SELECT 3 , ' 5805 ' , ' 27-196-022 '
UNION
SELECT 3 , ' 5805 ' , ' 27-196-023 '
SELECT
G1_GroupID
,G1_ItemNumber
,G2_GroupID
,G2_ItemNumber
FROM (
SELECT
GroupID AS G1_GroupID
,ItemNumber AS G1_ItemNumber
FROM @Temp
WHERE
GroupID IN ( 1 )
) AS A CROSS JOIN (
SELECT
GroupID AS G2_GroupID
,ItemNumber AS G2_ItemNumber
FROM @Temp
WHERE
GroupID NOT IN ( 1 )
) AS B
ORDER BY A.G1_GroupID,A.G1_ItemNumber
/* Result
* 1 27-196-018 2 27-196-020
* 1 27-196-018 2 27-196-021
* 1 27-196-018 3 27-196-022
* 1 27-196-018 3 27-196-023
* 1 27-196-019 2 27-196-020
* 1 27-196-019 2 27-196-021
* 1 27-196-019 3 27-196-022
* 1 27-196-019 3 27-196-023
*/
(GroupID INT ,
GroupName VARCHAR ( 25 ),
ItemNumber varchar ( 25 )
)
INSERT INTO @Temp
SELECT 1 , ' 5805 ' , ' 27-196-018 '
UNION
SELECT 1 , ' 5805 ' , ' 27-196-019 '
UNION
SELECT 2 , ' 5805 ' , ' 27-196-020 '
UNION
SELECT 2 , ' 5805 ' , ' 27-196-021 '
UNION
SELECT 3 , ' 5805 ' , ' 27-196-022 '
UNION
SELECT 3 , ' 5805 ' , ' 27-196-023 '
SELECT
G1_GroupID
,G1_ItemNumber
,G2_GroupID
,G2_ItemNumber
FROM (
SELECT
GroupID AS G1_GroupID
,ItemNumber AS G1_ItemNumber
FROM @Temp
WHERE
GroupID IN ( 1 )
) AS A CROSS JOIN (
SELECT
GroupID AS G2_GroupID
,ItemNumber AS G2_ItemNumber
FROM @Temp
WHERE
GroupID NOT IN ( 1 )
) AS B
ORDER BY A.G1_GroupID,A.G1_ItemNumber
/* Result
* 1 27-196-018 2 27-196-020
* 1 27-196-018 2 27-196-021
* 1 27-196-018 3 27-196-022
* 1 27-196-018 3 27-196-023
* 1 27-196-019 2 27-196-020
* 1 27-196-019 2 27-196-021
* 1 27-196-019 3 27-196-022
* 1 27-196-019 3 27-196-023
*/