表如下(表中含有若干个重复数据,比如下面的1天,2天和4天):字段A 字段B
1天 10个
1天 10个
1天 10个
2天 20个
2天 20个
3天 30个
4天 40个
4天 40个现在的要求是,显示出distinct 数据,但是排序方式要按照重复数据出现次数排列。
比如上面的,1天出现了3次,2天出现了2次,3天出现1次,4天出现2次,应该得到结果如下:字段A 字段B
1天 10个
2天 20个
4天 40个
3天 30个
1天 10个
1天 10个
1天 10个
2天 20个
2天 20个
3天 30个
4天 40个
4天 40个现在的要求是,显示出distinct 数据,但是排序方式要按照重复数据出现次数排列。
比如上面的,1天出现了3次,2天出现了2次,3天出现1次,4天出现2次,应该得到结果如下:字段A 字段B
1天 10个
2天 20个
4天 40个
3天 30个
select 字段A,count(字段B) as 个数 from 表 group by 字段A
declare @t table(字段A varchar(20), 字段B varchar(20))
insert into @t
select '1天' , ' 10个'
union all select '1天' , ' 10个'
union all select '1天' , ' 10个'
union all select '2天' , ' 20个'
union all select '2天' , ' 20个'
union all select '3天' , ' 30个'
union all select '4天' , ' 40个'
union all select '4天' , ' 40个'select 字段A ,count(字段B) as 次数 from @t group by 字段A/*
字段A 次数
----------------------
1天 3
2天 2
3天 1
4天 2
*/
insert into @t
select '1天', '10个' union all
select '1天', '10个' union all
select '1天', '10个' union all
select '2天', '20个' union all
select '2天', '20个' union all
select '3天', '30个' union all
select '4天', '40个' union all
select '4天', '40个'select a,b from @t group by a,b order by count(9) desc
insert into @t
select '1天' , ' 10个'
union all select '1天' , ' 10个'
union all select '1天' , ' 10个'
union all select '2天' , ' 20个'
union all select '2天' , ' 20个'
union all select '3天' , ' 30个'
union all select '4天' , ' 40个'
union all select '4天' , ' 40个'select distinct a.字段A,b.字段B, a.次数 from
(select 字段A ,count(字段B) as 次数 from @t group by 字段A ) a, @t b
where a.字段A=b.字段A
order by a.次数 desc
/*
字段A 字段B A次数
------------------------------------
1天 10个 3
2天 20个 2
4天 40个 2
3天 30个 1*/
group by a,b
order by count(*) desc
group by a
order by count(b) desc
insert into @t
select '1天' , ' 10'
union all select '1天' , ' 10'
union all select '1天' , ' 10'
union all select '2天' , ' 20'
union all select '2天' , ' 20'
union all select '3天' , ' 30'
union all select '4天' , ' 40'
union all select '4天' , ' 40'
SELECT A,B FROM @t group by A,B ORDER BY COUNT(*) DESC
(8 row(s) affected)A B
-------------------- --------------------
1天 10
2天 20
4天 40
3天 30(4 row(s) affected)