两个表的数字范围关联的语句，求指导

[毕业一年了]

好久没写sql语句了，脑子锈了，特来求大家指导一下。

一个表 A 字段AID 内容是 1 4 7 9

另一表B 字段 BID 内容是 1 2 3 4 5 6 7 8 9 10

得到两个表的关联，BID的全部内容，对应AID的小于等于的值

BID AID
1 1
2 1
3 1
4 4
5 4
6 4
7 7
8 7
9 9
10 9

[吉普赛的歌]

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
*/

• 打赏
• 举报

回复 赞

[听雨停了]

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

• 打赏
• 举报

回复 赞

[中国风]

e.g.

SELECT  BID ,
        ( SELECT    MAX(AID)
          FROM      A
          WHERE     AID <= B.BID
        ) AS AID
FROM    B;

• 打赏
• 举报

回复 赞

[RINK_1]

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

• 打赏
• 举报

回复 赞