create table tb ( id1 int, id2 int ) insert into tb select 1,2 union all select 1,4 union all select 1,3 union all select 2,2 union all select 2,22 union all select 2,222 select * from tb order by id1,id2 ----------------- id1 id2 1 2 1 3 1 4 2 2 2 22 2 222
create table tb ( id1 int, id2 int ) insert into tb select 1,2 union all select 1,4 union all select 1,3 union all select 2,2 union all select 2,1select * from ( select number = ROW_NUMBER() over(order by id1, id2),* from tb where id1 in (select id1 from tb group by id1 having COUNT(*) = 2) union all select number = ROW_NUMBER() over(order by id1), * from tb where id1 in (select id1 from tb group by id1 having COUNT(*) <> 2) ) A order by id1, number
(
id1 int,
id2 int
)
insert into tb
select 1,2 union all
select 1,4 union all
select 1,3 union all
select 2,2 union all
select 2,22 union all
select 2,222
select * from tb order by id1,id2
-----------------
id1 id2
1 2
1 3
1 4
2 2
2 22
2 222
(
id1 int,
id2 int
)
insert into tb
select 1,2 union all
select 1,4 union all
select 1,3 union all
select 2,2 union all
select 2,1select * from
(
select number = ROW_NUMBER() over(order by id1, id2),* from tb where id1 in (select id1 from tb group by id1 having COUNT(*) = 2)
union all
select number = ROW_NUMBER() over(order by id1), * from tb where id1 in (select id1 from tb group by id1 having COUNT(*) <> 2)
) A
order by id1, number