sql select - 将列中的数据合并为一个并对其运行不同的数据

Question

我有一张表：

id    coach1    coach2    department    date
1       1         0           txt1       8/11/2010
2       3         0           txt2       1/11/2010
3       1         2           txt1       8/11/2010
4       3         1           txt1       8/11/2010

我想要做的是从两位教练那里获取一列，忽略零并且不重复教练的数字，所以这个例子的输出将是：

coach    department    date
  1          txt1       8/11/2010
  2          txt1       8/11/2010
  3          txt1       1/11/2010

所以我使用：

SELECT id,coach1 as coach,department,date FROM tblWinnings where coach1>0 UNION SELECT id,coach2 as coach,department,date FROM tblWinnings where coach2>0

但现在我需要它与教练栏不同，

我该怎么做？

谢谢！

Answer 1

select distinct coach1 as coach from tblName
union 
select coach2 from tblName
order by coach

如果您只想要不同的值，请不要使用 Union All （没有 All 子句的联合可以防止重复）

因为您不断更新您的问题：如果您想要其他字段中的值，您将遇到问题，因为根据您使用的不同，它们可能会有所不同。 （最新？最早？随机？）

Answer 2

我不记得访问的语法，但类似

select distinct coach from (
select coach1 as coach from tableX 
union all 
select coach2 as coach from tableX) 
where coach <> 0

Answer 3

create view coaches as
  select coach1 as coach
    from table
   where coach1 <> 0
union all
  select coach2 as coach
    from table
   where coach1 <> 0;

select distinct coach
  from coaches;