mysql - 为特定 ID 寻找接近于零的值

Question

我正在为特定 ID 寻找最接近零的值

我的输入是

    tech_Id time_diff
    1000      100
    1000      200
    1000      -50
    1000      -10
    1001      100
    1001      200
    1001       50
    1001       10
    1002      -50
    1002      -10
    1003      130
    1003     -140
    1003      -2

预期输出

tech_Id time_diff
1000     -10
1001      10
1002     -10
1003      -2

提前致谢

Answer 1

你可以找到时间差异的绝对值的最小值。

select t1.*
from your_table t1
join (
    select tech_id, min(abs(time_diff)) as time_diff
    from your_table
    group by tech_id
    ) t2 on t1.tech_id = t2.tech_id and abs(t1.time_diff) = t2.time_diff;