在SQL中选择不同的值对

本文介绍了在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:

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

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