SQL 在最终结果之上使用不同的 union 多次

Using union multiple times with distinct on top of the final results

提问by Jerry Dodge

I cannot figure out how to merge 4 different columns from 2 different tables together into just one single column of all possible results, with duplicates removed. The actual names are different, but suppose I have the following two tables

我无法弄清楚如何将 2 个不同表中的 4 个不同列合并到所有可能结果的一列中，并删除重复项。实际名称不同，但假设我有以下两个表

Table1

表格1

1. Field1
2. Field2
3. SomethingElse

1. 字段 1
2. 场 2
3. 别的东西

Table2

表2

1. Field1
2. Field2
3. SomethingElse

1. 字段 1
2. 场 2
3. 别的东西

Now in the end, I would like to merge all 4 of these fields together into one large field, and then use distincton top of that to eliminate any duplicates, as there are many of them. The final result should be just one single column containing every possible value found in all 4 columns, without any duplicated.

现在最后，我想将所有 4 个字段合并为一个大字段，然后在其上使用distinct以消除任何重复项，因为它们中有很多。最终结果应该只是包含在所有 4 列中找到的所有可能值的单个列，没有任何重复。

When I was working with just one single table with two fields, I had it working fine:

当我只处理一个包含两个字段的单个表时，它工作正常：

select distinct(Field1) from Table1 where SomethingElse = SomeVal
union
(select distinct(Field2) from Table1 where SomethingElse = SomeVal)
order by 1

(of course I wished to have run distinctaround the final result rather than each field)

（当然我希望distinct围绕最终结果而不是每个领域）

Now I needed to add 2 more fields from another table. Nothing I have tried has even run, can't get the syntax right.

现在我需要从另一个表中添加 2 个字段。我试过的任何东西都没有运行，无法获得正确的语法。

回答by Gordon Linoff

Here is one way:

这是一种方法：

select distinct val
from ((select field1 as val from table1 where somethingelse = someval) union all
      (select field2 from table1 where somethingelse = someval) union all
      (select field1 from table2 where somethingelse = someval) union all
      (select field2 from table2 where somethingelse = someval)
     ) t

I combine the subqueries using union alland then only do the distinctonce on the outer level.

我使用组合子查询union all，然后只distinct在外层执行一次。

Since the next thing I would want to know is where these values are being used, here is the query for that:

由于我想知道的下一件事是在哪里使用这些值，这里是查询：

select val, SUM(t1f1), SUM(t1f2), SUM(t2f1), SUM(t2f2)
from ((select field1 as val, 1 as t1f1, 0 as t1f2, 0 as t2f1, 0 as t2f2 from table1 where somethingelse = someval) union all
      (select field2, 0 as t1f1, 1 as t1f2, 0 as t2f1, 0 as t2f2 from table1 where somethingelse = someval) union all
      (select field1, 0 as t1f1, 0 as t1f2, 1 as t2f1, 0 as t2f2 from table2 where somethingelse = someval) union all
      (select field2, 0 as t1f1, 0 as t1f2, 0 as t2f1, 1 as t2f2 from table2 where somethingelse = someval)
     ) t
group by val

回答by Brian White

select distinct(Field1) from 
(
select Field1 [Field1] from Table1 where SomethingElse = SomeVal
union all
select Field2 [Field1] from Table1 where SomethingElse = SomeVal
) rawResults
order by 1

Something like that should work. Just union together your selects and put that in an in-line view and put your distinct and order by on the outside. I used union all since you are going to do a distinct once on the outside anyway

这样的事情应该有效。只需将您的选择结合在一起，并将其放在一个在线视图中，然后将您的独特和排序放在外面。我使用了 union all 因为无论如何你都要在外面做一次独特的

回答by Bogdan Sahlean

SQL Server >= 2008

SQL Server >= 2008

The first solution:

第一个解决方案：

SELECT DISTINCT y.Field
FROM (
    SELECT  x.Field
    FROM    Table1 t
    CROSS APPLY ( VALUES 
        (t.Field1),
        (t.Field2)
    ) AS x(Field)
    UNION ALL
    SELECT  x.Field
    FROM    Table2 t
    CROSS APPLY ( VALUES 
        (t.Field1),
        (t.Field2)
    ) AS x(Field)
) y

The second solution:

第二种解决方案：

SELECT  x.Field
FROM    Table1 t
CROSS APPLY ( VALUES 
    (t.Field1),
    (t.Field2)
) AS x(Field)
UNION 
SELECT  x.Field
FROM    Table2 t
CROSS APPLY ( VALUES 
    (t.Field1),
    (t.Field2)
) AS x(Field)

SQL Server >= 2005

SQL Server >= 2005

The third solution:

第三种解决方案：

SELECT DISTINCT y.Field
FROM (
    SELECT  x.Field
    FROM    Table1 t
    CROSS APPLY ( 
        SELECT t.Field1 AS Field
        UNION ALL 
        SELECT t.Field2 AS Field
    ) AS x
    UNION ALL
    SELECT  x.Field
    FROM    Table2 t
    CROSS APPLY (
        SELECT t.Field1 AS Field
        UNION ALL 
        SELECT t.Field2 AS Field
    ) AS x
) y

The fourth solution:

第四个解决方案：

SELECT  x.Field
FROM    Table1 t
CROSS APPLY ( 
    SELECT t.Field1 AS Field
    UNION ALL 
    SELECT t.Field2 AS Field
) AS x
UNION 
SELECT  x.Field
FROM    Table2 t
CROSS APPLY (
    SELECT t.Field1 AS Field
    UNION ALL 
    SELECT t.Field2 AS Field
) AS x