Select (Case When Exists (Select 1 from tableName Where a=T1.a And b<T1.b) Then '' Else a End) As a, b, c from TableName T1 Where a='x'
--测试使用的数据 create table ta(a char, b char, c char) go insert ta select 'x', 'y', 'z' union all select 'x', 'a', 'u' union all select 'x', 'y', 'a' union all select 'y', 'j', 'q' go select * from ta where a='x'
--我写的,思路就是先用top找出第一条记录.再求出满足条件的记录(除第一条外),并排序 select * from ( select top 1 * from ta union select '' as a, t1.b, t1.c from ta as t1, (select top 1 t2.b, t2.c from ta as t2 where t2.a='x' ) t3 where t1.a='x' and (t1.b<>t3.b or t1.c<>t3.c) ) tt order by tt.a desc
a u
y a返回这样的结果集
a
a u
Select
(Case When Exists (Select 1 from tableName Where a=T1.a And b<T1.b) Then '' Else a End) As a,
b,
c
from TableName T1
Where a='x'
create table ta(a char, b char, c char)
go
insert ta select 'x', 'y', 'z'
union all select 'x', 'a', 'u'
union all select 'x', 'y', 'a'
union all select 'y', 'j', 'q'
go
select *
from ta
where a='x'
select *
from
(
select top 1 *
from ta
union
select '' as a, t1.b, t1.c
from ta as t1, (select top 1 t2.b, t2.c
from ta as t2
where t2.a='x'
) t3
where t1.a='x'
and (t1.b<>t3.b or t1.c<>t3.c)
) tt
order by tt.a desc