表A ID 地区
1 上海
2 北京
3 广州
4 深圳
5 上海
6 北京
7 北京
统计表中[地区]列里每个地区出现的次数,
按上例统计如下 地区 出现次数
北京 3
上海 2
广州 1
深圳 1SELECT 地区,COUNT(*) AS 出现次数
FROM tb
GROUP BY 地区 order by 出现次数
加上order by 出现次数就出错了,要怎么写求出排在前100名城市
1 上海
2 北京
3 广州
4 深圳
5 上海
6 北京
7 北京
统计表中[地区]列里每个地区出现的次数,
按上例统计如下 地区 出现次数
北京 3
上海 2
广州 1
深圳 1SELECT 地区,COUNT(*) AS 出现次数
FROM tb
GROUP BY 地区 order by 出现次数
加上order by 出现次数就出错了,要怎么写求出排在前100名城市
FROM tb
GROUP BY 地区 order by 2
FROM tb
GROUP BY 地区 order by 出现次数 desc
(SELECT 地区,COUNT(*) AS 出现次数
FROM tb
GROUP BY 地区 )AS T order by 出现次数
(SELECT 地区,COUNT(*) AS 出现次数
FROM tb
GROUP BY 地区 )AS T order by 出现次数 DESC
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-29 10:34:40
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[地区] varchar(4))
insert [tb]
select 1,'上海' union all
select 2,'北京' union all
select 3,'广州' union all
select 4,'深圳' union all
select 5,'上海' union all
select 6,'北京' union all
select 7,'北京'
--------------开始查询--------------------------SELECT 地区,COUNT(*) AS 出现次数
FROM tb
GROUP BY 地区 order by 2 desc
----------------结果----------------------------
/* 地区 出现次数
---- -----------
北京 3
上海 2
深圳 1
广州 1(4 行受影响)
*/
FROM tb
GROUP BY 地区
order by 出现次数 desc
1 上海
2 北京
3 广州
4 深圳
5 上海
6 北京
7 北京 select 地区,count(地区) from tb group by 地区
SELECT
top 100 * from
(SELECT 地区,COUNT(*) AS 出现次数
FROM tb
GROUP BY 地区 )AS T
order by
出现次数 DESC
FROM tb
GROUP BY 地区 order by COUNT(地区) DESC搞定了
create table [tb]([ID] int,[地区] varchar(4))
insert [tb]
select 1,'上海' union all
select 2,'北京' union all
select 3,'广州' union all
select 4,'深圳' union all
select 5,'上海' union all
select 6,'北京' union all
select 7,'北京'
select top 100 * from
(
select [地区],count([地区]) cnt from [tb] group by [地区]
)tt
order by [地区]
地区 cnt
---- -----------
北京 3
广州 1
上海 2
深圳 1
SELECT 地区,COUNT(*) AS 出现次数
FROM tb
GROUP BY 地区 order by 出现次数 desc
insert into #tmp
select 1, '上海' union all
select 2, '北京' union all
select 3, '广州' union all
select 4, '深圳' union all
select 5, '上海' union all
select 6, '北京' union all
select 7, '北京'
select area as 地区,count(1) 出现次数 from #tmp group by area order by 出现次数
drop tabel #tmp
drop table tb
go
create table tb(id nvarchar(20),地区 nvarchar(20))
go
insert into tb select
1 ,'上海' union all select
2 ,'北京' union all select
3 ,'广州' union all select
4 ,'深圳' union all select
5 ,'上海' union all select
6 ,'北京' union all select
7 ,'北京' ;with f
as
(
select 地区,COUNT(*) as 出现次数
from tb
GROUP BY 地区
)
select 地区,出现次数
from f t
where (select count(1)+1 from f where 出现次数>t.出现次数)<=100/*地区 出现次数
-------------------- -----------
北京 3
广州 1
上海 2
深圳 1*/
drop table tb
go
create table tb(id nvarchar(20),地区 nvarchar(20))
go
insert into tb select
1 ,'上海' union all select
2 ,'北京' union all select
3 ,'广州' union all select
4 ,'深圳' union all select
5 ,'上海' union all select
6 ,'北京' union all select
7 ,'北京' ;with f
as
(
select 地区,COUNT(*) as 出现次数
from tb
GROUP BY 地区
)
select 地区,出现次数
from f t
where (select count(distinct 出现次数)+1 from f where 出现次数>t.出现次数)<=100/*地区 出现次数
-------------------- -----------
北京 3
广州 1
上海 2
深圳 1*/
在access里必须这样写
SELECT 地区,COUNT(*) AS 出现次数
FROM tb
GROUP BY 地区 order by 2
结贴
FROM tb
GROUP BY 地区 order by 出现次数 desc