34,591
社区成员
发帖
与我相关
我的任务
分享
declare @tablename table (A varchar(4),B varchar(4))
insert into @tablename
select '1111','1111' union all
select '1111','AAAA' union all
select '1111','1111' union all
select '2222','2222' union all
select '2222','2222' union all
select '2222','2222' union all
select '3333','3333' union all
select '444','444' union all
select '5555','bbb' union all
select '5555','bbb'
select a.* from @tablename a right join @tablename b
on a.A=b.A left join
(select count(distinct B) as c1,A from @tablename group by A ) c
on b.A=c.A where c.c1>1 and b.A<>b.B
/*
A B
---- ----
1111 1111
1111 AAAA
1111 1111
*/
select * from tb t where exists (select 1 from tb where A=t.A and B<>t.B)
----创建测试数据
if object_id('ta') is not null drop table ta
create table ta (A varchar(20),B varchar(20))
insert into ta
select '1111','1111' union all
select '1111','AAAA' union all
select '1111','1111' union all
select '2222','2222' union all
select '2222','2222' union all
select '2222','2222' union all
select '3333','3333' union all
select '444','444'
---查询
(select a ,b from ta)
union
(select a,b from ta)
--结果
/*
a b
1111 1111
1111 AAAA
2222 2222
3333 3333
444 444
*/
----创建测试数据
if object_id('ta') is not null drop table ta
create table ta (A varchar(20),B varchar(20))
insert into ta
select '1111','1111' union all
select '1111','AAAA' union all
select '1111','1111' union all
select '2222','2222' union all
select '2222','2222' union all
select '2222','2222' union all
select '3333','3333' union all
select '444','444'
---查询
(select a ,b from ta)
union
(select a,b from ta)
select * from
ta where a!=b
declare @tablename table (A varchar(4),B varchar(4))
insert into @tablename
select '1111','1111' union all
select '1111','AAAA' union all
select '1111','1111' union all
select '2222','2222' union all
select '2222','2222' union all
select '2222','2222' union all
select '3333','3333' union all
select '444','444'
select a.* from @tablename a right join @tablename b
on a.A=b.A where b.A<>b.B
/*
A B
---- ----
1111 1111
1111 AAAA
1111 1111
*/
--??
Select *
from #t
where [a]!=[B]