select
a.*
from
Man a,
(select ID,NUM=count(*) from Man GROUP BY ID) b
where
a.ID = b.ID
order by
b.NUM,a.ID,a.wife
a.*
from
Man a,
(select ID,NUM=count(*) from Man GROUP BY ID) b
where
a.ID = b.ID
order by
b.NUM,a.ID,a.wife
(
select Id,count(wife) as wifecount from Man
group by Id
) test
order by wifecount
create table man(ID varchar(10),wife varchar(10))
insert into man select '12' ,'Rose'
insert into man select '07' ,'Masa'
insert into man select '07' ,'Jeccia'
insert into man select '12' ,'Linda'
insert into man select '07' ,'Linda'
insert into man select '55' ,'Marry'
insert into man select '07' ,'O’neal'
--执行查询
select
a.*
from
Man a,
(select ID,NUM=count(*) from Man GROUP BY ID) b
where
a.ID = b.ID
order by
b.NUM,a.ID,a.wife
--输出结果
ID wife
---- -------
55 Marry
12 Linda
12 Rose
07 Jeccia
07 Linda
07 Masa
07 O’neal
(id int,wife nvarchar(10))insert into tableA
select 1,'Wife1'
union select 1,'Wife2'
union select 2,'Wife3'
union select 3,'Wife4'
union select 3,'Wife5'
union select 3,'Wife6'select *
from tableAselect top 100 percent a.id,a.wife
from tableA as a,
(
select top 100 percent id,count(*) as count
from tableA
group by id
order by count(*)
) as b
where a.id=b.id
order by b.count
支持 libin_ftsafe(子陌红尘)!
a.*
from
Man a,
(select ID,NUM=count(*) from Man GROUP BY ID) b
where
a.ID = b.ID
order by
b.NUM,a.ID,a.wife
-------------
这样好象不行吧,b.NUM根本没有出现在select子句中,怎么能按它进行排序呢?select
b.ID,a.wife
from
Man a,
(select ID,NUM=count(*) from Man GROUP BY ID ORDER by 2) b
where
a.ID = b.ID 试试。
(select id,count(*) as num from man group by id)b
where a.id = b.id
order by b.num
Create table man(ID Varchar(10),wife Varchar(10))
Insert into man Select '12' ,'Rose'
Insert into man Select '07' ,'Masa'
Insert into man Select '07' ,'Jeccia'
Insert into man Select '12' ,'Linda'
Insert into man Select '07' ,'Linda'
Insert into man Select '55' ,'Marry'
Insert into man Select '07' ,'O’neal'
--测试
Select * from Man A Order By (Select Count(1) from Man Where ID=A.ID),ID,wife
--删除测试环境
Drop Table Man
--结果
/*
ID wife
55 Marry
12 Linda
12 Rose
07 Jeccia
07 Linda
07 Masa
07 O’neal
*/
from man
group by id
order by f
srder By (select Count(*) from man where id=a.id),id,wife