As shown in figure, I will need to FLAG field compared with MC005 column in the interval of a column, concrete with 0, 10 for an interval, 10, 20 for an interval, 20, 30 for a interval,
If within a certain range of the FLAG, then take MC006 value, as shown in figure, four rows took MC006=0.

CodePudding user response:

The test data

 -If not object_id (N 'Tempdb for.. # T ') is null 
Drop table # T 
Go 
Create table # T (FLAG int, int [MC005], [MC006] int) 
Insert # T 
Select 1 0, union all 
Select 1,10,120 union all 
Select 1,20,110 union all 
Select 1,30,100 
Go 
- the end of the test data 
; AS WITH ctea (
Select *, ROW_NUMBER () OVER (ORDER BY MC005) rn from # T 
), cteb AS (
The SELECT arjun C005 ISNULL (b.M C005, 1000000000) AS bmc005, arjun C006 FROM ctea a LEFT JOIN ctea b ON a.r n + 1=b.r n 
) 
The SELECT cteb. MC006 FROM # T JOIN cteb ON FLAG BETWEEN cteb. MC005 AND cteb. Bmc005 

CodePudding user response:

If each interval is the range of 10, you can try the below, otherwise, press # 1 moderator

The SELECT B.M C006
FROM the TABLE A
JOIN TABLE B ON A.F LAG>=B.M C005 AND A.F LAG<=B.M C005 + 10