22,206
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('jia') IS NOT NULL DROP TABLE jia
IF OBJECT_ID('yi') IS NOT NULL DROP TABLE yi
GO
CREATE TABLE jia( A INT,B INT )
CREATE TABLE yi ( A INT,B INT )
INSERT INTO jia(A) VALUES (1)
INSERT INTO jia(A) VALUES (1)
INSERT INTO jia(A) VALUES (1)
INSERT INTO jia(A) VALUES (2)
INSERT INTO jia(A) VALUES (2)
INSERT INTO yi(A,B) VALUES (1,1)
INSERT INTO yi(A,B) VALUES (2,1)
INSERT INTO yi(A,B) VALUES (3,1)
INSERT INTO yi(A,B) VALUES (4,1)
INSERT INTO yi(A,B) VALUES (5,1)
INSERT INTO yi(A,B) VALUES (1,2)
INSERT INTO yi(A,B) VALUES (2,2)
INSERT INTO yi(A,B) VALUES (3,2)
------------- 以上为测试数据 --------------
;WITH cteJia AS(
SELECT ROW_NUMBER() OVER (PARTITION BY A ORDER BY B) AS rid, * FROM jia
),cteYi AS(
SELECT ROW_NUMBER() OVER (PARTITION BY B ORDER BY A) AS rid, * FROM yi
)
SELECT cteJia.A,cteYi.A AS [B]
FROM cteJia LEFT JOIN cteYi ON cteJia.A=cteYi.B AND cteJia.rid=cteYi.rid
/*
A B
1 1
1 2
1 3
2 1
2 2
*/