假设我在同一张表中有以下两列:
Column 1
--------
1
2
3
Column 2
--------
4
5
6
我该如何得到一个能给我以下结果的答案?
Columns
--------
1
2
3
4
5
6
编辑
我真正想要的是确保在不必多次重复表格和where条件的情况下,在同一张表格上键入联合查询的更有效方法。
实际查询看起来更像这样:
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]
使用@Bohemian的外部查询,我可以做到以下操作,但是测试两个查询时,似乎速度变慢了很多。
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]
UNION
更简洁,但如果你经常需要这样做,那很可能是数据库设计不良的迹象。你能否提供更多关于你试图解决的实际问题的信息,也许我们可以建议一种改进基本设计的方法,以便你不需要这种“魔法”非标准SQL? - SingleNegationElimination