问:找出A表中a字段相同的行,并按照b字段排序?
如:a b
1 2
1 3
2 1
3 3
3 5检索结果:
a b
1 2
1 3
3 3
3 5
a列相同的行,按照b列从小到大排序?
怎么构造此语句?
如:a b
1 2
1 3
2 1
3 3
3 5检索结果:
a b
1 2
1 3
3 3
3 5
a列相同的行,按照b列从小到大排序?
怎么构造此语句?
调试欢乐多
inner join (select a from A group by A having count(1)>1)B
on A.a = B.A
insert into @t select 1 ,2
union all select 1 ,3
union all select 2 ,1
union all select 3 ,3
union all select 3 ,5 select * from @t a,(select a from @t group by a having count(a)>1) b where a.a=b.a
insert into @t select 1 ,2
union all select 1 ,3
union all select 2 ,1
union all select 3 ,3
union all select 3 ,5 select a.a,a.b from @t a,(select a from @t group by a having count(a)>1) b where a.a=b.a order by a.b
where a in (select a from A group by a having count(a) > 1) order by b
--orselect * from A m
where (select count(*) from A where a = m.a)>1
原来:a b
2 1
3 2
1 1
1 2
2 3
3 5
后来:
1 1
1 2
2 1
2 3
3 2
3 5
a相同的行在一起,相同的行之间以b列进行排序
INSERT INTO @T
SELECT 1 , 2 UNION
SELECT 1 , 3 UNION
SELECT 2 , 1 UNION
SELECT 3 , 3 UNION
SELECT 3 , 5
SELECT * FROM @T WHERE A IN
(
SELECT A FROM @T C GROUP BY A HAVING COUNT(A) > 1
)
ORDER BY B
insert into @t select 2 ,1
union all select 3 ,2
union all select 1 ,1
union all select 1 ,2
union all select 2 ,3
union all select 3 ,5
select a.a,a.b from @t a,(select a from @t group by a having count(a)>1) b where a.a=b.a order by a.a,a.b