SQL Group By 和 min (MySQL)

2021-12-27 00:00:00 min sql group-by mysql

我有以下 SQL:

select code, distance from places;

输出如下:

CODE    DISTANCE            LOCATION
106     386.895834130068    New York, NY
80      2116.6747774121     Washington, DC
80      2117.61925131453    Alexandria, VA
106     2563.46708627407    Charlotte, NC

我希望能够只获得一个代码和最近的距离.所以我希望它返回这个:

I want to be able to just get a single code and the closest distance. So I want it to return this:

CODE    DISTANCE            LOCATION
106     386.895834130068    New York, NY
80      2116.6747774121     Washington, DC

我最初有这样的事情:

SELECT code, min(distance), location
GROUP BY code
HAVING distance > 0 
ORDER BY distance ASC

如果我不想获得与最小距离相关联的正确位置，则 min 工作正常.我如何获得最小(距离)和正确的位置(取决于表格中插入的顺序，有时您最终可能会得到纽约的距离，但最终会得到夏洛特的位置).

The min worked fine if I didn't want to get the correct location that was associated with the least distance. How do I get the min(distance) and the correct location (depending on the ordering on the inserts in the table, sometimes you could end up with the New York distance but the Charlotte in Location).

推荐答案

要获得正确的关联位置，您需要加入一个子选择，该子选择获取每个代码的最小距离，条件是外部主表中的距离与子选择中导出的最小距离匹配.

To get the correct associated location, you'll need to join a subselect which gets the minimum distance per code on the condition that the distance in the outer main table matches with the minimum distance derived in the subselect.

SELECT a.code, a.distance
FROM   places a
INNER JOIN
(
    SELECT   code, MIN(distance) AS mindistance
    FROM     places
    GROUP BY code
) b ON a.code = b.code AND a.distance = b.mindistance
ORDER BY a.distance

相关文章