问近似获取最接近的行

EN

select top 1 col1,col2,col3,col4
from yourtable
order by abs(col1-4)+abs(col2-5.75)+abs(col3-7.2)+abs(col4-6) asc

DECLARE @table TABLE
(
    Col1 DECIMAL(10,2),
    Col2 DECIMAL(10,2),
    Col3 DECIMAL(10,2),
    Col4 DECIMAL(10,2)
);

INSERT INTO @table (Col1, Col2, Col3, Col4)
VALUES
(5, 6, 7, 8),
(12, 6, 5, 6),
(2, 3.5, 6, 1);

WITH AbsoluteDiff(RowID, Col1, Col2, Col3, Col4, Col1Diff, Col2Diff, Col3Diff, Col4Diff, DiffSum)
AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) 
    ,Col1, Col2, Col3, Col4, abs(Col1-4), abs(Col2-5.75), abs(Col3-7.2), abs(Col4-6), abs(Col1-4)+abs(Col2-5.75)+abs(Col3-7.2)+abs(Col4-6)
    FROM @table
)
SELECT TOP 1 RowID
FROM AbsoluteDiff
ORDER BY DiffSum ASC, CASE
                        WHEN Col1Diff >= Col2Diff AND Col1Diff >= Col3Diff AND Col1Diff >= Col4Diff THEN Col1Diff
                        WHEN Col2Diff >= Col1Diff AND Col2Diff >= Col3Diff AND Col2Diff >= Col4Diff THEN Col2Diff
                        WHEN Col3Diff >= Col1Diff AND Col3Diff >= Col2Diff AND Col3Diff >= Col4Diff THEN Col3Diff
                        ELSE Col4Diff
                    END ASC