[Challenge] 尋找數列中最接近的號碼

Beyond Relational TSQL Beginners Challenge 5

• 資料來源

Numbers
-------
10
12
19
25
25
34
38 

• 產生結果

Closest Number
--------------
25

• 規則
1. SQL Server 2005 以上的版本
2. 產生結果的欄位名稱，必須是Closest Number
3. 一定要使用次序函數

• 個人解法

DECLARE @T TABLE(Numbers INT)

INSERT INTO @t
SELECT 10 UNION ALL
SELECT 12 UNION ALL
SELECT 19 UNION ALL
SELECT 25 UNION ALL
SELECT 25 UNION ALL
SELECT 34 UNION ALL
SELECT 38

DECLARE @NumberToSearch INT
SET @NumberToSearch = 24

SELECT Numbers AS [Closest Number]
FROM
  (
    SELECT 
      Numbers , 
      ROW_NUMBER() OVER (ORDER BY ABS(Numbers - @NumberToSearch)) AS [ROWNO]
    FROM @T
    GROUP BY Numbers
  ) AS T
WHERE ROWNO = 1

• 其他解法

DECLARE @T TABLE(Numbers INT)

INSERT INTO @t
SELECT 10 UNION ALL
SELECT 12 UNION ALL
SELECT 19 UNION ALL
SELECT 25 UNION ALL
SELECT 25 UNION ALL
SELECT 34 UNION ALL
SELECT 38

DECLARE @NumberToSearch INT
SET @NumberToSearch = 24

-- 在 ORDER BY 使用次序函並配合 TOP 
SELECT TOP 1 Numbers 
FROM @T
ORDER BY ROW_NUMBER() OVER (ORDER BY ABS(Numbers - @NumberToSearch))

-- 不利用次序函數
SELECT TOP 1 Numbers 
FROM @T
ORDER BY ABS(Numbers - @NumberToSearch)

參考資料
• TSQL Beginners Challenge 5 - Search the closest number in the series
• MSDN ABS()