从表中的两列中仅选择不同的值

If I have a table such as

如果我有一张桌子如

1 A

1 B

1 A

1 B

2 C

2 C

And I want to select distinct from the two columns so that I would get

我想从两列中选择不同的，以便我得到

How can I word my query? Is the only way to concatenate the columns and wrap them around a distinct function operator?

我该如何说出我的查询？是连接列并将它们包装在一个独特的函数运算符周围的唯一方法吗？

6 个解决方案

#1

You could use a union to create a table of all values from both columns:

您可以使用union来创建两个列中所有值的表：

select  col1 as BothColumns
from    YourTable
union
select  col2
from    YourTable

Unlike union all, union removes duplicates, even if they come from the same side of the union.

与union all不同，union会删除重复项，即使它们来自union的同一侧。

#2

SQL Fiddle

SQL小提琴

Why even distinct in Union, try this :

为什么甚至在联盟中不同，试试这个：

select cast(id  as char(1)) from test
union
select val from test

#3

Please try:

请尝试：

Select Col1 from YourTable
union
Select Col2 from YourTable

UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not. Please check What is the difference between UNION and UNION ALL

UNION删除重复记录（结果中的所有列都相同），UNION ALL不会。请检查UNION和UNION ALL之间的区别

For multiple columns, you can go for UNPIVOT.

对于多列，您可以选择UNPIVOT。

SELECT distinct DistValues
FROM 
   (SELECT Col1, Col2, Col3
   FROM YourTable) p
UNPIVOT
   (DistValues FOR Dist IN 
      (Col1, Col2, Col3)
)AS unpvt;

#4

Try this one -

试试这个 -

DECLARE @temp TABLE
(
      Col1 INT
    , Col2 NVARCHAR(50)
)

INSERT INTO @temp (Col1, Col2)
VALUES (1, 'ab5defg'), (2, 'ae4eii')

SELECT disword = (
    SELECT DISTINCT dt.ch
    FROM (
        SELECT ch = SUBSTRING(t.mtxt, n.number + 1, 1) 
        FROM [master].dbo.spt_values n
        CROSS JOIN (
            SELECT mtxt = (
                SELECT CAST(Col1 AS VARCHAR(10)) + Col2
                FROM @temp
            FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'
            )
        ) t
        WHERE [type] = N'p'
        AND number <= LEN(mtxt) - 1
    ) dt
    FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'
)

Or try this -

或试试这个 -

DECLARE @temp TABLE
(
    a CHAR(1), b CHAR(1)
)

INSERT INTO @temp (a, b)
VALUES 
    ('1', 'A'), ('1', 'B'), ('1', 'A'),
    ('1', 'B'), ('2', 'C'), ('2', 'C')

SELECT a
FROM @temp

UNION

SELECT b
FROM @temp

#5

Because what you want select is in different columns, you can use union like below:

因为您想要选择的是在不同的列中，您可以使用如下所示的union：

select distinct tarCol from  
(select distinct column1 as tarCol from table
 union 
select distinct column2 from table) as tarTab

#6

You can use like this to get multiple distinct column values

您可以像这样使用以获取多个不同的列值

(SELECT DISTINCT `enodeb` as res, 
                 "enodeb" as columnname 
 FROM `raw_metrics`) 
UNION
(SELECT DISTINCT `interval` as res, 
       "interval" as columnname 
 FROM `raw_metrics`)

#1