34,838
社区成员




IF OBJECT_ID('tempdb..#T1') IS NOT NULL
DROP TABLE #T1
CREATE TABLE #T1(
AID INT
)
INSERT INTO #T1
SELECT 1 UNION ALL
SELECT 4 UNION ALL
SELECT 7 UNION ALL
SELECT 9
IF OBJECT_ID('tempdb..#T2') IS NOT NULL
DROP TABLE #T2
CREATE TABLE #T2(
BID INT
)
INSERT INTO #T2
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10
SELECT * FROM #T2 A
OUTER APPLY (SELECT TOP 1 AID FROM #T1 WHERE AID<=A.BID ORDER BY AID DESC) AS B
SELECT BID ,
( SELECT MAX(AID)
FROM A
WHERE AID <= B.BID
) AS AID
FROM B;
IF OBJECT_ID('tempdb..#tabA') IS NOT NULL
DROP TABLE #tabA
CREATE TABLE #tabA(
Aid INT
)
INSERT INTO #tabA
SELECT 1 UNION ALL
SELECT 4 UNION ALL
SELECT 7 UNION ALL
SELECT 9
IF OBJECT_ID('tempdb..#tabB') IS NOT NULL
DROP TABLE #tabB
CREATE TABLE #tabB(
Bid INT
)
INSERT INTO #tabB
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10
--测试数据结束
WITH cte AS (
SELECT * FROM #tabB b
INNER JOIN #tabA a ON b.Bid>=a.Aid
)
SELECT bid,MAX(aid) AS aid FROM cte
GROUP BY bid
USE tempdb
GO
IF OBJECT_ID('A') IS NOT NULL DROP TABLE A
IF OBJECT_ID('B') IS NOT NULL DROP TABLE B
CREATE TABLE A(aId INT)
CREATE TABLE B(bId INT)
INSERT INTO A SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
INSERT INTO B SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
SELECT bid,aid FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY bid ORDER BY aid DESC) AS rid, bid,aid
FROM A INNER JOIN B ON aId IN (1,4,7,9) AND aid<=bId
GROUP BY bid,aid
)AS t
WHERE rid=1
/*
bid aid
1 1
2 1
3 1
4 4
5 4
6 4
7 7
8 7
9 9
10 9
*/