有一个表TA两个字段A和B有几组数据如下 A B
1111 1111
1111 AAAA
1111 1111
2222 2222
2222 2222
2222 2222
3333 3333
444 444
找出1111这组数据,因为第二行的B字段和A字段内容不相同
1111 1111
1111 AAAA
1111 1111
2222 2222
2222 2222
2222 2222
3333 3333
444 444
找出1111这组数据,因为第二行的B字段和A字段内容不相同
Select *
from #t
where [a]!=[B]
1111 AAAA
数据,1111这组数据就得不到呀
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
*/
ta where a!=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)
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
*/
A B
1111 1111
1111 AAAA
1111 1111
2222 2222
2222 2222
2222 2222
3333 3333
444 444
5555 bbb
5555 bbb
1111和5555这两组数据A字段的内容和B字段的内容都有不同,但5555组数据的B字段内容是相同的,1111组数据B字段内容不相同,我想要的结果是1111组数据不要5555组数据。非常感谢
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
*/