MySQL:分组依据和最接近的整数

Question

我有下表

位置:

userId |  zip  | 
----------------
   1   | 12383 |
   1   | 10253 |
   2   | 10523 |
   2   | 14856 |
   2   | 10251 |
   3   |  NULL |    

对于给定的整数 X，我想根据 zip 列中最接近 X 的整数以及相应的 zip 数量对用户进行排序>。如果用户在字段 zip 中有值 NULL，那么他应该显示在最后。

例子: 对于 X = 5000，我想获得输出

userId |  zip  | 
----------------
   2   | 10251 |
   1   | 10253 |
   3   |  NULL | 

我设法让 userId 正确排序:

SELECT userId, MIN(ABS(3-zip)) as dist FROM location GROUP BY userId Order by -dist DESC

制作表格

userId |  dist  | 
-----------------
   2   |  5251  |
   1   |  5253  |
   3   |  NULL  | 

但是我怎样才能得到最近的邮政编码呢？

Answer 1

SELECT t1.userId,
       t1.zip
FROM location t1
INNER JOIN
(
    SELECT userId, MIN(ABS(3-zip)) AS dist
    FROM location
    GROUP BY userId
) t2
    ON t1.userId = t2.userId AND
       (ABS(3 - t1.zip) = t2.dist OR t2.dist IS NULL)   -- pay careful attention here
ORDER BY -t2.dist DESC                                  -- join on the absolute difference

此处演示:

SQLFiddle

这个查询有几个技巧:

• 加入 ABS(# - zip) 确保我们选择最近的 zip，无论它是高还是低
• 连接中的 t2.dist IS NULL 条件包括具有 NULL 邮政编码的用户
• 使用 ORDER BY -t2.dist DESC 以升序顺序对邮政编码进行排序，但也将 NULL 放在末尾 结果集。