按列选择非重复项，字段不对应

kx7yvsdv 于 2021-06-17 发布在 Mysql

关注(0)|答案(2)|浏览(327)

我被告知创建一个新的问题，关于一个显然是重复的，如果重复没有给我正确的答案，我想要的。
因此，我的系统的基础是，我有一个数据库填充了“房子”的位置/坐标。我做了一个高级查询来选择某个坐标附近的所有房子，就像这样；

SELECT id, type, distance FROM (SELECT b.id, b.type, b.x, b.y, b.z, 
    SQRT(POWER(ABS(1654.5413 - b.x), 2) + POWER(ABS(-2293.7571 - b.y), 2) + POWER(ABS(-1.1996 - b.z), 2)) AS "distance" 
        FROM businesses b ORDER BY distance ASC) as T;

这将导致：；

+------+------+------------------------+
| id   | type | distance               |
+------+------+------------------------+
| 1953 |    2 | 0.00004489639611771451 |
|    2 |  100 |      8.757256937390904 |
| 1959 |    2 |      8.999959765646956 |
| 1960 |    2 |     10.499959765643807 |
| 1961 |    2 |     11.999959765641446 |
| 1962 |    2 |     13.499959765639607 |
| 1963 |    2 |     14.999959765638138 |
| 1964 |    2 |     16.499959765636934 |
| 2055 |    3 |      17.11486010149676 |
| 2054 |    1 |     17.751048488860313 |
| 1965 |    2 |     17.999959765635932 |
| 1966 |    2 |     19.499959765635083 |
| 1967 |    2 |     20.999959765634358 |
| 2056 |    5 |      22.26658275782834 |
| 1968 |    2 |     22.499959765633726 |
| 1969 |    2 |     23.999959765633175 |
| 2057 |    5 |     24.054132659013334 |
| 1970 |    2 |      25.49995976563269 |
| 2058 |    5 |     26.001138245767084 |
| 2061 |    4 |     26.853239370669378 |
| 1971 |    2 |      26.99995976563226 |
| 1972 |    2 |      28.49995976563187 |
| 2060 |    5 |      28.55999771765475 |
| 1973 |    2 |     29.999959765631523 |
| 2059 |    5 |     31.414688663981224 |
| 1974 |    2 |     31.499959765631207 |
|    1 |  100 |      121468.4587678613 |
+------+------+------------------------+

我基本上想做的是抓取距离最小的行，但我不希望基于“type”列的重复，就像这样；

+------+------+------------------------+
| id   | type | distance               |
+------+------+------------------------+
| 1953 |    2 | 0.00004489639611771451 |
|    2 |  100 |      8.757256937390904 |
| 2055 |    3 |      17.11486010149676 |
| 2054 |    1 |     17.751048488860313 |
| 2056 |    5 |      22.26658275782834 |
| 2061 |    4 |     26.853239370669378 |
+------+------+------------------------+

我收到两个答案（都是错的）在我的前一个问题，他们的一半是正确的，其中一个答案是；
“嵌套查询更简单：

SELECT id,type, MIN(distance) AS 'minDistance'
FROM(
SELECT id, type, distance FROM (SELECT b.id, b.type, b.x, b.y, b.z, 
    SQRT(POWER(ABS(1654.5413 - b.x), 2) + POWER(ABS(-2293.7571 - b.y), 2) + POWER(ABS(-1.1996 - b.z), 2)) AS "distance" 
        FROM businesses b ORDER BY distance ASC) as T) AS TABLE
GROUP BY type 
ORDER BY 'minDistance'

"
^上面的答案确实选择了我期望的正确距离。但是它没有做的是匹配id字段和我想选择的所有其他字段，基本上不匹配具有不同值的行。不确定我是否正确解释了，但这里是基本上发生了什么。
如果我的表中填充了这些行；

+------+------+------------------------+
| id   | type | distance               |
+------+------+------------------------+
| 1960 |    2 |     65.757256937390904 |
| 1953 |    2 | 0.00004489639611771451 |
| 2055 |    2 |      17.11486010149676 |
+------+------+------------------------+

我运行我从答案得到的查询，我会得到这个；

+------+------+------------------------+
| id   | type | distance               |
+------+------+------------------------+
| 1960 |    2 | 0.00004489639611771451 |
+------+------+------------------------+

但正如你所见，这个距离与正确的id字段不符，它应该是id1953，而不是它的1960。
我该怎么纠正这个问题呢？
聚苯乙烯；如果你对另一个主题/问题感兴趣；从选择查询中按列选择非重复项

sql mysql

来源：https://stackoverflow.com/questions/53998742/select-non-duplicates-by-column-fields-dont-correspond

2条答案

按热度按时间

cuxqih211#

mysql 8兼容

WITH dist as (
SELECT b.id, b.type, b.x, b.y, b.z, 
    SQRT(POWER(ABS(1654.5413 - b.x), 2) + POWER(ABS(-2293.7571 - b.y), 2) + POWER(ABS(-1.1996 - b.z), 2)) AS "distance" 
        FROM businesses b 
)
select * from dist d1
where distance = (
select  min(distance)
from dist d2
  where d1.type = d2.type
)

对于mysql 8<，您可以考虑为您的查询创建视图，如

CREATE VIEW DIST AS (
  SELECT b.id, b.type, b.x, b.y, b.z, 
    SQRT(POWER(ABS(1654.5413 - b.x), 2) + POWER(ABS(-2293.7571 - b.y), 2) + POWER(ABS(-1.1996 - b.z), 2)) AS "distance" 
        FROM businesses b 
)

然后

select * from dist d1
where distance = (
select  min(distance)
from dist d2
  where d1.type = d2.type
)

赞(0）回复(0）举报 2021-06-17

o75abkj42#

只是你需要一个having子句

SELECT id,type, distance AS 'minDistance'
FROM(
SELECT id, type, distance FROM (SELECT b.id, b.type, b.x, b.y, b.z, 
    SQRT(POWER(ABS(1654.5413 - b.x), 2) + POWER(ABS(-2293.7571 - b.y), 2) + POWER(ABS(-1.1996 - b.z), 2)) AS "distance" 
        FROM businesses b ORDER BY distance ASC) as T) AS TABLE
GROUP BY type having distance=min(distance) -- added
ORDER BY 'minDistance'

赞(0）回复(0）举报 2021-06-17

我来回答

按列选择非重复项，字段不对应

2条答案

相关问题

热门标签

最新问答