SQL 同一个表中两列的并集
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14168135/
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
Union of two columns in the same table
提问by edezzie
Say I have the following two columns in the same table
假设我在同一个表中有以下两列
Column 1
--------
1
2
3
Column 2
--------
4
5
6
How do I get a result which gives me:
我如何得到一个结果，它给了我：
Columns
--------
1
2
3
4
5
6
Edit
编辑
What I'm really looking for is to make sure there is not a more efficient way of typing a union query on multiple columns in the same table without having to repeat which table it is multiple times and repeating a where condition for each union multiple times.
我真正想要的是确保没有更有效的方法可以在同一个表中的多个列上键入联合查询，而不必多次重复哪个表并多次重复每个联合的 where 条件.
The actual query looks more like this:
实际查询看起来更像这样：
WITH T1 AS
( SELECT [Col1] FROM [Table1]
)
SELECT * FROM (
SELECT [Cols1-100], COUNT(*) as "Count" FROM (
SELECT [Col-1] FROM [Table2] AS [Cols1-100], [T1]
WHERE [Table2].[Col-1] = [T1].[Col-1]
AND [Col-1] != '2' AND [Col-2] != '2' ..... etc ..... AND [Col-100] != '2'
UNION ALL
SELECT [Col-2] FROM [Table2] AS [Cols1-100], [T1]
WHERE [Table2].[Col-1] = [T1].[Col-1]
AND [Col-1] != '2' AND [Col-2] != '2' ..... etc ..... AND [Col-100] != '2'
UNION ALL
....................... etc
....................... etc
....................... etc
.... etc
SELECT [Col-100] FROM [Table2] AS [Cols1-100], [T1]
WHERE [Table2].[Col-1] = [T1].[Col-1]
AND [Col-1] != '2' AND [Col-2] != '2' ...... etc .... AND [Col-100] != '2'
) as [Temp1]
GROUP BY [Cols1-100]
) as [Temp2]
Using @Bohemian Outer Query I can do the following but testing the two query's, it seems a lot slower.
使用@Bohemian Outer Query 我可以执行以下操作，但测试这两个查询时，它似乎慢了很多。
WITH T1 AS
( SELECT [Col1] FROM [Table1]
)
SELECT * FROM (
SELECT [Cols1-100], COUNT(*) as "Count" FROM (
SELECT * FROM (
SELECT [Col-1] AS [Cols1-100], [Col-1], [Col-2], ..etc.. [Col-100] FROM [Table2]
UNION ALL
SELECT [Col-2] AS [Cols1-100], [Col-1], [Col-2], ..etc.. [Col-100] FROM [Table2]
UNION ALL
....................... etc
.... etc
SELECT [Col-100] AS [Cols1-100], [Col-1], [Col-2], ..... etc ..... [Col-100] FROM [Table2]
) AS SUBQUERY WHERE [Col-1] IN (SELECT [Col1] FROM [T1])
AND [Col-1] != '2' AND [Col-2] != '2' ..... etc ..... AND [Col-100] != '2'
) as [Temp1]
GROUP BY [Cols1-100]
) as [Temp2]
回答by Bohemian
select column1 as columns from mytable
union
select column2 from mytable
Using unionremoves duplicates (and on some databases also sorts).
If you want to preserveduplicates, use union all:
使用union删除重复项（并且在某些数据库上也排序）。
如果要保留重复项，请使用union all：
select column1 as columns from mytable
union all
select column2 from mytable
Edit:
编辑：
To add a where clause, the easy but inefficient execution way is to add it as an outer query:
要添加 where 子句，简单但效率低下的执行方式是将其添加为外部查询：
select * from (
select column1 as columns from mytable
union
select column2 from mytable ) x
where columns ...
The more efficient execution way, but a painfully long query, is to put it on each subquery:
更有效的执行方式，但一个痛苦的长查询，是把它放在每个子查询上：
select column1 as columns from mytable
where ....
union
select column2 from mytable
where ...
回答by beder
If you don't want to use union because you would have to repeat the same where clause multiple times, there is a [very bad] work around for that:
如果您不想使用 union 因为您必须多次重复相同的 where 子句，则有一个 [非常糟糕] 解决方法：
select decode(j.col, 1, column1, 2, column2)
from table t
join (select 1 as col from dual union select 2 from dual) j
on 1 = 1
where (your where clause)
This example is from oracle, on SQLServer you wouldn't need the "from dual"
这个例子来自oracle，在SQLServer上你不需要“来自双重”
Also, if you have a LOT of columns to join (shouldn't happen, really) you could use a hierarchical query inside the "join" to avoid a ton of "unions"
此外，如果您有很多列要加入（不应该发生，真的），您可以在“加入”中使用分层查询来避免大量“联合”
回答by Stuart Ainsworth
Have you tried UNPIVOT? It depends on your version of SQL Server, but the following example will work on SQL 2008:
你试过 UNPIVOT 吗？这取决于您的 SQL Server 版本，但以下示例适用于 SQL 2008：
DECLARE @t TABLE (Col1 INT, col2 INT, col3 INT)
INSERT INTO @t
( Col1, col2, col3 )
VALUES ( 1, -- Col1 - int
2, -- col2 - int
3 -- col3 - int
),
( 4, -- Col1 - int
5, -- col2 - int
6 -- col3 - int
)
SELECT cols
FROM (SELECT col1, col2, col3 FROM @t) pvt
UNPIVOT
(Cols FOR ID IN (col1, col2, col3)) unpvt
WHERE cols <> 2
