declare @t table(name varchar(10), dep1 varchar(10), dep2 varchar(10)) insert @t values('fa','b','b') insert @t values('fa','b','c') insert @t values('fa','b','d') insert @t values('fb','a','') insert @t values('fb','a','a') insert @t values('fc','c',null)--查询 select distinct name,dep=dep1 from @t where dep1<>'' and dep1 is not null union select distinct name,dep=dep2 from @t where dep2<>'' and dep2 is not null/*结果 name dep ---------- ---------- fa b fa c fa d fb a fc c(5 行受影响) */
--更正一下,下面的是对的,去掉union all中的all declare @t table (name char(10),dep1 char(2),dep2 char(2)) insert @t select 'fa' , 'b' , 'b' union all select 'fa' , 'b' , 'c' union all select 'fa' , 'b' , 'd' union all select 'fb' , 'a' , '' union all select 'fb' , 'a' , 'a' union all select 'fc' , 'c' , null select distinct name,dep1 from @t where dep1 is not null and dep1<>'' union select distinct name,dep2 from @t where dep2 is not null and dep2<>'' order by name,dep1 /**************** fa b fa c fa d fb a fc c *****************/
declare @t table([name] varchar(10), dep1 varchar(10), dep2 varchar(10)) insert @t values('fa','b','b') insert @t values('fa','b','c') insert @t values('fa','b','d') insert @t values('fb','a','') insert @t values('fb','a','a') insert @t values('fc','c',null) select [name],count(dep) from ( select distinct [name],isnull(dep2,dep1) dep from @t where dep2<>'' union select distinct [name],isnull(dep1,dep2) dep from @t where dep1<>'') a group by [name]
insert @t values('fa','b','b')
insert @t values('fa','b','c')
insert @t values('fa','b','d')
insert @t values('fb','a','')
insert @t values('fb','a','a')
insert @t values('fc','c',null)--查询
select distinct name,dep=dep1 from @t where dep1<>'' and dep1 is not null
union
select distinct name,dep=dep2 from @t where dep2<>'' and dep2 is not null/*结果
name dep
---------- ----------
fa b
fa c
fa d
fb a
fc c(5 行受影响)
*/
--更正一下,下面的是对的,去掉union all中的all
declare @t table (name char(10),dep1 char(2),dep2 char(2))
insert @t
select
'fa' , 'b' , 'b'
union all select
'fa' , 'b' , 'c'
union all select
'fa' , 'b' , 'd'
union all select
'fb' , 'a' , ''
union all select
'fb' , 'a' , 'a'
union all select
'fc' , 'c' , null select distinct name,dep1 from @t where dep1 is not null and dep1<>''
union
select distinct name,dep2 from @t where dep2 is not null and dep2<>''
order by name,dep1
/****************
fa b
fa c
fa d
fb a
fc c
*****************/
insert @t values('fa','b','b')
insert @t values('fa','b','c')
insert @t values('fa','b','d')
insert @t values('fb','a','')
insert @t values('fb','a','a')
insert @t values('fc','c',null)
select [name],count(dep) from (
select distinct [name],isnull(dep2,dep1) dep from @t where dep2<>''
union
select distinct [name],isnull(dep1,dep2) dep from @t where dep1<>'') a
group by [name]