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

毕业一年了 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

...全文
217 4 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
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
*/

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧