问个SQL语句

Question

前两天考阿里，遇到个让写SQL的语句，想了半天竟 没一点思路。哪位看能不能写出来。大概如下（我把条件简化了一下）：表 log ：  
           id               省份          关键字
           1                河南           手机
           2                湖北           长江
           3                河南           手机
           4                河南           大河报
           5                湖北           东湖
           6                南京           地铁
           7                湖北           长江
           8                湖北           东湖
           9                河南           大河报
           10               河南           东开发区
           11               南京           长江
           12               湖北           武大
              ...........................
 记录了每次查询时用户省份和用户搜索关键字。
现用一条SQL语句查询出每个省份用户查询最多的2个关键字。

Answer 1

 select *
 from(
 select  省份,关键字,row_number(partition by 省份,关键字 order by r) as rn 
 from (select 省份,关键字,count(*) as r from log group by 省份,关键字) k)
 where rn=1 or rn=2 

Answer 2

declare @table table (id int,省份 varchar(4),关键字 varchar(8))
insert into @table
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,'湖北','长江' union all
select 8,'湖北','东湖' union all
select 9,'河南','大河报' union all
select 10,'河南','东开发区' union all
select 11,'南京','长江' union all
select 12,'湖北','武大'select * from 
(
select 省份,关键字,row_number() over(partition by 省份 order by ycount desc) RowNumber from
(
select 省份,关键字,COUNT(关键字) as ycount from @table group by 省份,关键字
) 
a
)
b
where RowNumber in (1,2)
/*
省份   关键字      RowNumber
---- -------- --------------------
河南   大河报      1
河南   手机       2
湖北   东湖       1
湖北   长江       2
南京   长江       1
南京   地铁       2
*/

Answer 3

发现rownumber()在这个例子里面挺实用的
2000的话估计是不是得用到临时表啊 各位大大

Answer 4

declare @table table (id int,省份 varchar(4),关键字 varchar(8))
insert into @table
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,'湖北','长江' union all
select 8,'湖北','东湖' union all
select 9,'河南','大河报' union all
select 10,'河南','东开发区' union all
select 11,'南京','长江' union all
select 12,'湖北','武大'select * from 
(
select 省份,关键字,ycount as 搜索次数,row_number() over(partition by 省份 order by ycount desc) RowNumber from
(
select 省份,关键字,COUNT(关键字) as ycount from @table group by 省份,关键字
) 
a
)
b
where RowNumber in (1,2)
/*
省份   关键字      搜索次数        RowNumber
---- -------- ----------- --------------------
河南   大河报      2           1
河南   手机       2           2
湖北   东湖       2           1
湖北   长江       2           2
南京   长江       1           1
南京   地铁       1           2
*/

Answer 5

--Ken Wong
--测试数据
declare @table table (id int,省份 varchar(4),关键字 varchar(8))
insert into @table
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,'湖北','长江' union all
select 8,'湖北','东湖' union all
select 9,'河南','大河报' union all
select 10,'河南','东开发区' union all
select 11,'南京','长江' union all
select 12,'湖北','武大'
--查询
select * from (select 省份, 关键字,count(1) as 次数 from @table
group by 省份, 关键字)t where not exists 
(select 1 from (select 省份, 关键字,count(1) as 次数 from @table
group by 省份, 关键字) h where h.省份 = t.省份 and h.次数 > t.次数)
--结果
-----------------------
河南 大河报 2
河南 手机 2
湖北 长江 2
湖北 东湖 2
南京 长江 1
南京 地铁 1

Answer 6

create table #t(id    int,          省份   varchar(6),      关键字  varchar(16))
insert #t 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    ,            '湖北'    ,      '长江' union all select 
          8    ,            '湖北'   ,       '东湖' union all select 
          9    ,            '河南'  ,        '大河报' union all select 
          10   ,           '河南'  ,        '东开发区' union all select 
          11   ,           '南京' ,         '长江' union all select 
          12   ,           '湖北',          '武大' select  省份, 关键字,num=count(*) into #t1
from #t t
group by 省份, 关键字
order by 省份,num descselect * from #t1 t
where (select count(*) from #t1 where 省份=t.省份 and  num<=t.num)>=2
省份     关键字              num
------ ---------------- -----------
河南     大河报              2
河南     手机               2
湖北     东湖               2
湖北     长江               2
南京     长江               1
南京     地铁               1(6 行受影响)drop table #t  
drop table #t1

Answer 7

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2009-10-26 20:39: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.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[log]
if object_id('[log]') is not null drop table [log]
go 
create table [log]([id] int,[省份] varchar(4),[关键字] varchar(8))
insert [log]
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,'湖北','长江' union all
select 8,'湖北','东湖' union all
select 9,'河南','大河报' union all
select 10,'河南','东开发区' union all
select 11,'南京','长江' union all
select 12,'湖北','武大'
--------------开始查询--------------------------
;with f as
(
select [省份],[关键字],count(1) as num from [log] group by [省份],[关键字] 
)
SELECT * FROM f AS T WHERE 2>(SELECT COUNT(*) FROM f WHERE 省份=T.省份 AND num>T.num) order by 1
----------------结果----------------------------
/* 省份   关键字      num
---- -------- -----------
河南   大河报      2
河南   手机       2
湖北   长江       2
湖北   东湖       2
南京   长江       1
南京   地铁       1(6 行受影响)*/

Answer 8

2000的话把CTE里面的语句嵌套进去就OK了

Answer 9

select  省份, 关键字,count(*) num 
into #taoistong
from 表
group by 省份, 关键字
order by 省份,num descselect * from #taoistong t
where (select count(*) from 表 where 省份=t.省份 and  num<=t.num)>=2

Answer 10

if object_id('a') is not null
drop table a
go
create  table a(id int,省份 varchar(4),关键字 varchar(8))
insert into a
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,'湖北','长江' union all
select 8,'湖北','东湖' union all
select 9,'河南','大河报' union all
select 10,'河南','东开发区' union all
select 11,'南京','长江' union all
select 12,'湖北','武大'
go
select 次数=count(*) , 省份 , 关键字 , row_number()over(order by 省份) as id  into #t from a
group by 省份 , 关键字
order by 省份 , 次数 desc
select * from #t a
where id in
(select top 2 id from #t where a.省份 = 省份)
-------------------------------------
次数          省份   关键字      id
----------- ---- -------- --------------------
2           河南   手机       3
2           河南   大河报      1
2           湖北   长江       4
2           湖北   东湖       5
1           南京   长江       7
1           南京   地铁       8(6 行受影响)

Answer 11

F哥好像你这个有点问题if object_id('[log]') is not null drop table [log]
go 
create table [log]([id] int,[省份] varchar(4),[关键字] varchar(8))
insert [log]
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,'湖北','长江' union all
select 8,'湖北','东湖' union all
select 9,'河南','大河报' union all
select 10,'河南','东开发区' union all
select 11,'南京','长江' union all
select 12,'湖北','武大' union all
select 13,'南京','长江' union all
select 14,'南京','夫子庙' union all
select 15,'南京','夫子庙' union all
select 16,'南京','秦淮河' union all
select 17,'南京','秦淮河' 
--------------开始查询--------------------------
;with f as
(
select [省份],[关键字],count(1) as num from [log] group by [省份],[关键字] 
)
SELECT * FROM f AS T WHERE 2>(SELECT COUNT(*) FROM f WHERE 省份=T.省份 AND num>T.num) order by 1
-----------------------------
省份   关键字      num
---- -------- -----------
河南   大河报      2
河南   手机       2
湖北   长江       2
湖北   东湖       2
南京   长江       2
南京   夫子庙      2
南京   秦淮河      2(7 行受影响)这个结果和lz要求的好像不太一样