在SQL中选择不同的值对 [英] selecting distinct pairs of values in SQL

问题描述

我有一个Access 2010数据库，其中存储源计算机和目标计算机的IP地址.如果我的数据库中有以下条目

I have an Access 2010 database which stores IP addresses of source and destination machines. If I have the following entries in my database

|source           |   destination|
|--------------------------------|
|  A              |     B        |
|  B              |     A        |
|  A              |     B        |
|  C              |     D        |
|  D              |     D        |

是否有任何查询来选择唯一对?也就是说，查询的输出应为

Is there any query to select unique pairs? That is, the output of the query should be

|source           |     destination|
|----------------------------------|
|  A              |          B     |
|  C              |          D     |

推荐答案

您的问题似乎暗示了两件事:

Your question seems to imply two things:

1. 在列出源/目标对时，您只想查看一个方向，例如(A，B)，而不是(B，A).

1. When listing source/destination pairs you only want to see the pairs in one direction, e.g., (A,B) but not (B,A).

列表应省略源和目标相同的对，例如(D，D)

The list should omit pairs where the source and destnation are the same, e.g., (D,D)

在这种情况下，查询...

In that case the query...

SELECT DISTINCT source, destination
FROM
    (
            SELECT source, destination
            FROM SomeTable
        UNION ALL
            SELECT destination, source
            FROM SomeTable
    )
WHERE source < destination

...针对包含...的[SomeTable]运行...

...when run against [SomeTable] containing...

source  destination
------  -----------
A       B          
B       A          
A       B          
C       D          
D       D          
E       D          

...将产生:

source  destination
------  -----------
A       B          
C       D          
D       E          

这篇关于在SQL中选择不同的值对的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！