34,594
社区成员
发帖
与我相关
我的任务
分享
create table tb(a int,b int,c int)
insert into tb
select 1,2,3 union all
select 1,3,4
select m.a ,m.b,m.c, n.b,n.c from
(
select * , px = (select count(1) from tb where a = t.a and (b<t.b or (b=t.b and c<t.c))) + 1 from tb t
) m ,
(
select * , px = (select count(1) from tb where a = t.a and (b<t.b or (b=t.b and c<t.c))) + 1 from tb t
) n
where m.a = n.a and m.px =1 and n.px = 2
drop table tb
/*
a b c b c
----------- ----------- ----------- ----------- -----------
1 2 3 3 4
(所影响的行数为 1 行)
*/
select * from tb t
where exists(select 1 from tb where a=t.a and (b!=t.b and c!=t.c))
order by t.a
select
case when a.a=b.a then 0 else b.a end,
case when a.b=b.b then 0 else b.b end,
case when a.c=b.c then 0 else b.c end
from tb as a,tb as b
where a.a=1 and b.b=2
select * from tb
where a in
(select a from tb group by a having count(1) >= 2)
select
*
from
tb t
where
exists(select 1 from tb where (a=t.a and b<>t.b) or (a=t.a and b<>t.b and c<>t.c))
select * from tb t
where exists(select 1 from tb where a=t.a and (b!=t.b or c!=t.c))