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

毕业一年了 2017-12-06 01:48:33
好久没写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

...全文
192 4 点赞 打赏 收藏 举报
写回复
4 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
RINK_1 2017-12-06

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
  • 打赏
  • 举报
回复
中国风 2017-12-06
e.g.

SELECT  BID ,
        ( SELECT    MAX(AID)
          FROM      A
          WHERE     AID <= B.BID
        ) AS AID
FROM    B;
  • 打赏
  • 举报
回复
听雨停了 2017-12-06

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

  • 打赏
  • 举报
回复
吉普赛的歌 版主 2017-12-06
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
*/
  • 打赏
  • 举报
回复
相关推荐
发帖
MS-SQL Server
加入

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2017-12-06 01:48
社区公告
暂无公告