来自一个表中多个字段的不同值 ORACLE SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6789010/
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 values from multiple fields within one table ORACLE SQL
提问by Rodriguez
How can I get distinct values from multiple fields within one table with just one request.
如何仅通过一个请求从一张表中的多个字段中获取不同的值。
Option 1
选项1
SELECT WM_CONCAT(DISTINCT(FIELD1)) FIELD1S,WM_CONCAT(DISTINCT(FIELD2)) FIELD2S,..FIELD10S
FROM TABLE;
WM_CONCAT is LIMITED
WM_CONCAT 是有限的
Option 2
选项 2
select DISTINCT(FIELD1) FIELDVALUE, 'FIELD1' FIELDNAME
FROM TABLE
UNION
select DISTINCT(FIELD2) FIELDVALUE, 'FIELD2' FIELDNAME
FROM TABLE
... FIELD 10
is just too slow
太慢了
回答by Brainfreeze
For my problem, I had
对于我的问题，我有
WL1 ... WL2 ... correlation
A B 0.8
B A 0.8
A C 0.9
C A 0.9
how to eliminate the symmetry from this table?
如何消除这张表的对称性？
select WL1, WL2,correlation from
table
where least(WL1,WL2)||greatest(WL1,WL2) = WL1||WL2
order by WL1
this gives
这给
WL1 ... WL2 ... correlation
A B 0.8
A C 0.9
:)
:)
回答by Allan
The best option in the SQL is the UNION, though you may be able to save some performance by taking out the distinctkeywords:
SQL 中的最佳选项是UNION，尽管您可以通过删除distinct关键字来节省一些性能：
select FIELD1 FROM TABLE
UNION
select FIELD2 FROM TABLE
UNIONprovides the unique set from two tables, so distinct is redundant in this case. There simply isn't any way to write this query differently to make it perform faster. There's no magic formula that makes searching 200,000+ rows faster. It's got to search every row of the table twice and sort for uniqueness, which is exactly what UNIONwill do.
UNION提供来自两个表的唯一集合，因此在这种情况下 distinct 是多余的。根本没有任何方法可以以不同的方式编写此查询以使其执行得更快。没有什么神奇的公式可以使搜索 200,000 多行更快。它必须搜索表的每一行两次并排序唯一性，这正是UNION要做的。
The only way you can make it faster is to create separate indexes on the two fields (maybe) or pare down the set of data that you're searching across.
您可以加快速度的唯一方法是在两个字段上创建单独的索引（可能）或减少您正在搜索的数据集。
Alternatively, if you're doing this a lot and adding new fields rarely, you could use a materialized view to store the result and only refresh it periodically.
或者，如果您经常这样做并且很少添加新字段，则可以使用物化视图来存储结果并仅定期刷新它。
Incidentally, your second query doesn't appear to do what you want it to. Distinctalways applies to all of the columns in the selectsection, so your constants with the field names will cause the query to always return separate rows for the two columns.
顺便说一句，您的第二个查询似乎没有按照您的意愿行事。Distinct始终适用于该select部分中的所有列，因此带有字段名称的常量将导致查询始终为两列返回单独的行。
I've come up with another method that, experimentally, seems to be a little faster. In affect, this allows us to trade one full-table scan for a Cartesian join. In most cases, I would still opt to use the unionas it's much more obvious what the query is doing.
我想出了另一种方法，在实验上，它似乎更快一点。实际上，这允许我们用一次全表扫描换取笛卡尔连接。在大多数情况下，我仍然会选择使用 ，union因为查询正在做什么更明显。
SELECT DISTINCT CASE lvl WHEN 1 THEN field1 ELSE field2 END
FROM table
CROSS JOIN (SELECT LEVEL lvl
FROM DUAL
CONNECT BY LEVEL <= 2);
It's also worthwhile to add that I tested both queries on a table without useful indexes containing 800,000 rows and it took roughly 45 seconds (returning 145,000 rows). However, most of that time was spent actually fetching the records, not running the query (the query took 3-7 seconds). If you're getting a sizable number of rows back, it may simply be the number of rows that is causing the performance issue you're seeing.
值得补充的是，我在一个没有包含 800,000 行的有用索引的表上测试了这两个查询，大约需要 45 秒（返回 145,000 行）。但是，大部分时间都花在了实际获取记录上，而不是运行查询上（查询用了 3-7 秒）。如果您返回了大量行，则可能只是导致您看到的性能问题的行数。
回答by Kevin Burton
if you were scanning a small range in the data (not full scanning the whole table) you could use WITHto optimise your query
e.g:
如果您正在扫描数据中的一个小范围（而不是完整扫描整个表），您可以WITH用来优化您的查询，例如：
WITH a AS
(SELECT field1,field2,field3..... FROM TABLE WHERE condition)
SELECT field1 FROM a
UNION
SELECT field2 FROM a
UNION
SELECT field3 FROM a
.....etc
回答by Low Flying Pelican
When you get distinct values from multiple columns, then it won't return a data table. If you think following data
当您从多个列中获取不同的值时，它不会返回数据表。如果您认为以下数据
Column A Column B
10 50
30 50
10 50
when you get the distinct it will be 2 rows from first column and 1 rows from 2nd column. It simply won't work.
当你得到不同的时候，它将是第一列的 2 行和第二列的 1 行。它根本行不通。
回答by Aitor
And something like this?
还有这样的事情？
SELECT 'FIELD1',FIELD1, 'FIELD2',FIELD2,...
FROM TABLE
GROUP BY FIELD1,FIELD2,...
