SQL 使用sql从多个字段中选择不同的字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/546804/
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
Select distinct from multiple fields using sql
提问by Bryan
I have 5 columns corresponding to answers in a trivia game database - right, wrong1, wrong2, wrong3, wrong4
我有 5 列对应于琐事游戏数据库中的答案 - 正确、错误 1、错误 2、错误 3、错误 4
I want to return all possible answers without duplicates. I was hoping to accomplish this without using a temp table. Is it possible to use something similar to this?:
我想返回没有重复的所有可能的答案。我希望在不使用临时表的情况下完成此操作。是否可以使用类似的东西？：
select c1, c2, count(*)
from t
group by c1, c2
But this returns 3 columns. I would like one column of distinct answers.
但这会返回 3 列。我想要一列不同的答案。
Thanks for your time
谢谢你的时间
回答by achinda99
This should give you all distinct values from the table. I presume you'd want to add where clauses to select only for a particular question. However, this solution requires 5 subqueries and can be slow if your table is huge.
这应该为您提供表中所有不同的值。我想您想添加 where 子句以仅针对特定问题进行选择。但是，此解决方案需要 5 个子查询，如果您的表很大，则速度可能会很慢。
SELECT DISTINCT(ans) FROM (
SELECT right AS ans FROM answers
UNION
SELECT wrong1 AS ans FROM answers
UNION
SELECT wrong2 AS ans FROM answers
UNION
SELECT wrong3 AS ans FROM answers
UNION
SELECT wrong4 AS ans FROM answers
) AS Temp
回答by Kristen
SELECT DISTINCT(ans) FROM (
SELECT right AS ans FROM answers
UNION
SELECT wrong1 AS ans FROM answers
UNION
SELECT wrong2 AS ans FROM answers
UNION
SELECT wrong3 AS ans FROM answers
UNION
SELECT wrong4 AS ans FROM answers
) AS Temp
The DISTINCT is superfluous, because the UNION will not return rows that are identical for all columns. (When you want duplicated, or if you know that no duplicates exist, use UNION ALL for faster performance)
DISTINCT 是多余的，因为 UNION 不会返回所有列都相同的行。（当您想要复制，或者如果您知道不存在重复项，请使用 UNION ALL 以获得更快的性能）
This will give you a single list containing all the answers. You'll still have duplicates though if you have multiple copies of the same answer within a single column.
这将为您提供一个包含所有答案的列表。如果您在同一列中有多个相同答案的副本，您仍然会有重复项。
That should not be the case if you use UNION, only if you use UNION ALL
如果您使用 UNION，情况就不应该如此，只有在您使用 UNION ALL 时
SELECT [value] INTO #TEMP
FROM
(
SELECT [value] = 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 1
) AS X
(4 row(s) affected)
SELECT [value]
FROM #TEMP
value
-----------
1
2
3
1
(4 row(s) affected)
SELECT [value]
FROM #TEMP
UNION
SELECT [value]
FROM #TEMP
value
-----------
1
2
3
(3 row(s) affected)
回答by achinda99
I provided one answer above.
我在上面提供了一个答案。
However I figured a much better way to do it using UNPIVOT.
但是我想出了一个更好的方法来使用 UNPIVOT。
SELECT DISTINCT(ans)
FROM (
SELECT [Name], ANS
FROM (
SELECT right, wrong1, wrong2, wrong3, wrong4
FROM answers
) AS PVT
UNPIVOT
(ans FOR [Name] IN (right, wrong1, wrong2, wrong3, wrong4)) AS UNPVT
) AS OUTPUT;
You can provide any WHERE clause in the internal subquery:
您可以在内部子查询中提供任何 WHERE 子句：
SELECT DISTINCT(ans)
FROM (
SELECT [Name], ANS
FROM (
SELECT right, wrong1, wrong2, wrong3, wrong4
FROM answers
WHERE (...)
) AS PVT
UNPIVOT
(ans FOR [Name] IN (right, wrong1, wrong2, wrong3, wrong4)) AS UNPVT
) AS OUTPUT;
回答by scwagner
Well you can use a UNION and run 5 select statements, one for each column in your table. It would look something like this:
那么您可以使用 UNION 并运行 5 个选择语句，一个用于表中的每一列。它看起来像这样：
SELECT right FROM answers
UNION
SELECT wrong1 FROM answers
UNION
SELECT wrong2 FROM answers
UNION
SELECT wrong3 FROM answers
UNION
SELECT wrong4 FROM answers
This will give you a single list containing all the answers. You'll still have duplicates though if you have multiple copies of the same answer within a single column.
这将为您提供一个包含所有答案的列表。如果您在同一列中有多个相同答案的副本，您仍然会有重复项。
回答by Randal Schwartz
Columns of "right, wrong1, wrong2, wrong3, wrong4" mean that you have a mis-designed database. In general, a number or letter suffix on a column name should be a red flag to rethink the problem.
“对，错1，错2，错3，错4”的列表示您的数据库设计错误。一般来说，列名上的数字或字母后缀应该是重新思考问题的危险信号。
As you observed, your design required you to hack around to get a solution to a typical data reduction problem.
正如您所观察到的，您的设计要求您四处寻找解决典型数据缩减问题的方法。
回答by JoshBerke
Is this what you wanted?
这是你想要的吗？
select distinct c1 from t
select distinct c1 from t
回答by Quassnoi
In MySQLAND MS SQL:
在MySQLAND 中MS SQL：
SELECT
CASE
WHEN which = 1 THEN c1
WHEN which = 2 THEN c2
WHEN which = 3 THEN c3
WHEN which = 4 THEN c4
WHEN which = 5 THEN c5
END AS answer,
which
FROM mytable, (
SELECT 1 AS which
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
) w
For Oracle, add FROM DUALto each of the number selects.
对于Oracle，添加FROM DUAL到每个数字选择。
回答by Bryan
This is the exact answer.
这是确切的答案。
SELECT (ans) FROM (
SELECT correct AS ans FROM tGeoQuiz
UNION
SELECT wrong1 AS ans FROM tGeoQuiz
UNION
SELECT wrong2 AS ans FROM tGeoQuiz
UNION
SELECT wrong3 AS ans FROM tGeoQuiz
UNION
SELECT wrong4 AS ans FROM tGeoQuiz
) AS Temp
