sql - 如何使用 sql 查询获取范围落在哪个条件中？

Question

代码：

IF OBJECT_ID('tempdb..#TempMaster','U') IS NOT NULL
DROP TABLE #TempMaster

IF OBJECT_ID('tempdb..#TempTransaction','U') IS NOT NULL
DROP TABLE #TempTransaction

CREATE TABLE #TempMaster
(
Sno INT IDENTITY(1,1),
RangeDesc VARCHAR(100),
RangeFromValue INT,
RangeToValue INT
)

CREATE TABLE #TempTransaction
(
Sno INT IDENTITY(1,1),
[Values] INT
)

INSERT INTO #TempMaster(RangeDesc,RangeFromValue,RangeToValue)
SELECT * FROM (VALUES('Type A',1,10),('Type B',11,20),('Type C',21,30)) AS T(RangeDesc,RangeFromValue,RangeToValue)



INSERT INTO #TempTransaction([Values])
SELECT 1
UNION ALL
SELECT 15
UNION ALL
SELECT 5



SELECT * FROM #TempMaster
SELECT * FROM #TempTransaction

请任何人帮助我根据价值下降的范围获得这种解决方案。我想获取范围描述并写下查询以获取以下输出。提前致谢。

-------------------------------
sno     Values      RangeDesc
-------------------------------
1       1           Type A
2       15          Type B
3       5           Type A
-------------------------------

标签： sqlsql-server

Answer 1

您left join根据范围值进行操作。

select t1.Sno, t1.[Values], t2.RangeDesc
from #TempTransaction t1
left join #TempMaster  t2 on t1.[Values] between t2.RangeFromValue and t2.RangeToValue