不同的值对 SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1330692/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Distinct pair of values SQL
提问by OscarRyz
Consider
考虑
create table pairs ( number a, number b )
Where the data is
数据在哪里
1,1
1,1
1,1
2,4
2,4
3,2
3,2
5,1
Etc.
等等。
What query gives me the distinct values the number column b has So I can see
什么查询给了我数字列 b 的不同值 所以我可以看到
1,1
5,1
2,4
3,2
only
只要
I've tried
我试过了
select distinct ( a ) , b from pairs group by b
but gives me "not a group by expression"
但给了我“不是按表达分组”
回答by Michael Krelin - hacker
What you mean is either
你的意思是要么
SELECT DISTINCT a, b FROM pairs;
or
或者
SELECT a, b FROM pairs GROUP BY a, b;
回答by StuartQ
If you want to want to treat 1,2 and 2,1 as the same pair, then this will give you the unique list on MS-SQL:
如果您想将 1,2 和 2,1 视为同一对，那么这将为您提供 MS-SQL 上的唯一列表：
SELECT DISTINCT
CASE WHEN a > b THEN a ELSE b END as a,
CASE WHEN a > b THEN b ELSE a END as b
FROM pairs
Inspired by @meszias answer above
灵感来自上面@meszias 的回答
回答by Lasse V. Karlsen
This will give you the result you're giving as an example:
这会给你你给出的结果作为例子：
SELECT DISTINCT a, b
FROM pairs
回答by meszias
if you want to filter the tuples you can use on this way:
如果你想过滤元组，你可以这样使用：
select distinct (case a > b then (a,b) else (b,a) end) from pairs
the good stuff is you don't have to use group by.
好东西是你不必使用 group by。
回答by adam
If you just want a countof the distinct pairs.
如果您只想计算不同对的数量。
The simplest way to do that is as follows
SELECT COUNT(DISTINCT a,b) FROM pairs
最简单的方法如下
SELECT COUNT(DISTINCT a,b) FROM pairs
The previous solutions would list all the pairs and then you'd have to do a second query to count them.
以前的解决方案会列出所有对，然后您必须进行第二次查询来计算它们。
