SQL笛卡尔积

时间：2011-05-19 来源：翔如飞飞

笛卡尔积: 　　笛卡尔积在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
*/

相关阅读更多 +