最小距离和分组 [英] Minimum distance and group by

问题描述

我有这个表格（表格查询的结果记录在临时表）

  id_Customer | id_Shop |距离
 ------------------------------- 
 1 | 1 | 10 
 1 | 2 | 30 
 1 | 3 | 100 
 2 | 3 | 150 
 2 | 2 | 300 
 2 | 1 | 400 
  

我会得到这个结果（最小距离）

  id_Customer | id_Shop |距离
 ------------------------------- 
 1 | 1 | 10 
 2 | 3 | 150 
  

我该怎么做？

解决方案

这里是官方MySQL文档中的一篇很好的文章：

引用：

行持有某一列的组合最大值

任务：对于每篇文章，找到价格最昂贵的经销商或经销商。

这个问题可以用像这样的子查询来解决：

  SELECT article，dealer，价格
 FROM shop s1 
 WHERE price =（SELECT MAX（s2.price）
 FROM shop s2 
 WHERE s1.article = s2.article）; 
  

前面的示例使用相关的子查询，这可能效率低下（请参见第13.2.10.7节相关子查询）。解决该问题的其他可能性是在FROM子句或LEFT JOIN中使用不相关的子查询。
$ b

不相关的子查询：

  SELECT s1.article，dealer，s1.price 
从shop s1 
 JOIN（
 SELECT文章， MAX（price）AS价格
 FROM shop 
 GROUP BY文章）AS s2 
 ON s1.article = s2.article AND s1.price = s2.price; 
  

左连接：

  SELECT s1.article，s1.dealer，s1.price 
 FROM shop s1 
 LEFT JOIN shop s2 ON s1.article = s2.article AND s1 .price< s2.price 
 WHERE s2.article IS NULL; 
  

LEFT JOIN的工作原理是当s1.price达到其最大值时， s2.price的值更大，s2行值将为NULL。

I'm trying to make a query which give the nearest shop from a city.

I've this table (The result of a query in fac recorded in temporary table)

id_Customer | id_Shop | distance
-------------------------------
1           | 1       | 10
1           | 2       | 30
1           | 3       | 100
2           | 3       | 150
2           | 2       | 300
2           | 1       | 400

I would have this result (The minimal distance)

id_Customer | id_Shop | distance
-------------------------------
1           | 1       | 10
2           | 3       | 150

How can I do this?

解决方案

Here is an excellent article in the official MySQL documentation:

Quote:

The Rows Holding the Group-wise Maximum of a Certain Column

Task: For each article, find the dealer or dealers with the most expensive price.

This problem can be solved with a subquery like this one:

SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article);

The preceding example uses a correlated subquery, which can be inefficient (see Section 13.2.10.7, "Correlated Subqueries"). Other possibilities for solving the problem are to use an uncorrelated subquery in the FROM clause or a LEFT JOIN.

Uncorrelated subquery:

SELECT s1.article, dealer, s1.price
FROM shop s1
JOIN (
  SELECT article, MAX(price) AS price
  FROM shop
  GROUP BY article) AS s2
  ON s1.article = s2.article AND s1.price = s2.price;

LEFT JOIN:

SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
WHERE s2.article IS NULL;

The LEFT JOIN works on the basis that when s1.price is at its maximum value, there is no s2.price with a greater value and the s2 rows values will be NULL.

这篇关于最小距离和分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！