区别在两列（单独）和MIN在另一列上

I've tried to strip this problem down the the bare bones; I hope I've still captured the essence of what I'm trying to achieve in the original query!

我试图将这个问题从骨头上剥下来;我希望我仍然抓住了我在原始查询中想要实现的内容!

Code to generate the tables and data can be found here.

可以在此处找到生成表和数据的代码。

SQL flavour is Microsoft SQL Server 2000 (although I've been running this stripped down test case on MySQL)

SQL风格是Microsoft SQL Server 2000(虽然我一直在MySQL上运行这个剥离的测试用例)

The original table

原表

+-----------+----------+----------+
| master_id | slave_id | distance |
+-----------+----------+----------+
|         1 |        1 | 0.1      |
|         1 |        3 | 10       |
|         2 |        2 | 3        |
|         3 |        2 | 2        |
+-----------+----------+----------+

Description of what is required

所需内容的描述

I would like to select slave_id master_id pairs with MIN(distance) with no duplicates of either master_id or slave_id.

我想选择具有MIN(距离)的slave_id master_id对,没有master_id或slave_id的重复。

The desired results table

所需的结果表

+-----------+----------+----------+
| master_id | slave_id | distance |
+-----------+----------+----------+
|         1 |        1 | 0.1      |
|         3 |        2 | 2        |
+-----------+----------+----------+

My Attempt

SELECT
        join_table.master_id,
        join_table.slave_id,
        join_table.distance
    FROM join_table
    INNER JOIN
        (
            SELECT
                slave_id,
                MIN(distance) AS distance
            FROM join_table
            GROUP BY slave_id
        ) AS self_join
    ON self_join.slave_id = join_table.slave_id
    AND self_join.distance = join_table.distance

What's wrong with my attempt

我的尝试出了什么问题

This query produces duplicates of master_id

此查询生成master_id的重复项

Any help will be very much appreciated.

任何帮助将非常感谢。

2 个解决方案

#1

This should give the correct result:

这应该给出正确的结果:

select distinct t.master_id,
  t.slave_id,
  t.distance
from join_table t
inner join
(
  SELECT ID, min(Distance) dist
  FROM 
  (
    SELECT master_ID ID, MIN(distance) AS Distance
    FROM join_table
    GROUP BY master_ID
    UNION
    SELECT slave_ID ID, MIN(distance) AS Distance
    FROM join_table
    GROUP BY slave_ID
  ) src
  GROUP BY ID
) md
  on t.distance = md.dist
  and (t.master_id = md.id or t.slave_id = md.id)

See SQL Fiddle with Demo

请参阅SQL Fiddle with Demo

#2

If I got you right, here is what I would do:

如果我找对你,我会这样做:

SELECT DISTINCT t.master_id
   ,t.slave_id
   ,t.distance
FROM your_table t
   INNER JOIN 
   (
      SELECT master_id id, min(distance) distance
      FROM your_table
      GROUP BY master_id

      UNION

      SELECT slave_id id, min(distance) distance
      FROM your_table
      GROUP BY slave_id

   ) sub
   ON (sub.id = t.master_id AND sub.distance = t.distance)
     OR (sub.id = t.slave_id AND sub.distance = t.distance)

#1