SQL组By和min（MySQL） [英] SQL Group By and min (MySQL)

问题描述

我有以下SQL：

 选择代码，距离地点的距离; 
  

输出如下：

<$ p $ > code>代码距离位置
106 386.895834130068纽约州纽约州
80 2116.6747774121华盛顿特区
2117.61925131453弗吉尼亚州亚历山大$ b $ 106 2563.46708627407夏洛特NC

我希望能够获得单个代码和最近的距离。所以我希望它能够返回：

 代码距离位置
 386.895834130068纽约，纽约
 80 2116.6747774121华盛顿特区
  

我原来有这样的事情：

  SELECT代码，min（距离），位置
 GROUP BY代码
 HAVING distance> 0 
 ORDER BY距离ASC 
  

如果我不想要与最小距离相关的正确位置。如何获得最小距离（距离）和正确位置（取决于表格中插入物的排列顺序，有时您可能会得到纽约距离，但夏洛特在位置）。
为了得到正确的关联位置，需要加入一个子查询，它在每个代码中获得最小距离，条件是外部主表中的距离

  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 
  

I have the following SQL:

select code, distance from places;    

The output is below:

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

I originally had something like this:

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

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

这篇关于SQL组By和min（MySQL）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！