mysql - 按列选择不重复,字段不对应
问题描述
我被告知要创建一个新问题,该问题显然是重复的,并且如果重复没有给我想要的正确答案。
所以我的系统的基础是我有一个填充了“房子”位置/坐标的数据库。我进行高级查询以选择某个坐标附近的所有房屋,如下所示;
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 |
+------+------+------------------------+
我基本上想要做的是抓取距离最短的行,但我不希望基于“类型”列重复,就像这样;
+------+------+------------------------+
| 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 字段不对应,它应该是 id 1953 而是它的 1960。
我将如何继续纠正这个问题?
附言; 如果您对其他主题/问题感兴趣;从选择查询中按列选择不重复项
解决方案
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
)
推荐阅读
- javascript - 检测是否安装了 WhatsApp
- node-red - 节点红色 - 如何使用按钮停止 Bash 节点
- r - 如何在 r 中使用 ggplot2 包进行绘图
- here-api - 航点航向仅适用于第一个航点
- python - 如何强制 Pandas“read_csv”函数保留空白值
- flutter - Flutter 有多个 floatingActionButtons
- pytorch - 试图用另一个相同维度的张量来掩盖张量,得到“索引 1 超出尺寸 1 的维度 0 的范围”
- javascript - 如何在 Sublime Text 3 中使用 JavaScript 增强插件
- node.js - REST url 返回错误或连接失败
- sql - 我从面试中解决这个 SQL 问题的方法正确吗?