急求，一条超难的sql语句，请高手相助,

Question

表的记录如下id   name  province   urlAddress 
--   ---   --------   ------------
1    tom    广东       http://www.xfog.com
2    tim    重庆       http://www.xfog.com
3    john   广东       http://www.xfog.com
4    lil    上海       http://www.xfog.com
5    tom    广东       http://www.xfog.com
6    lil    上海       http://www.xfog.com
7     bb    广东       http://www.xfog.com
8     cc    上海       http://www.xfog.com
9     dd    重庆       http://www.xfog.com
10    lil   上海     http://www.xfog.com
11     dd    重庆       http://www.xfog.com
------------------------------------------------
求一条sql语句，显示每个地区访问数次最多的用户信息,结果如下:id    name  province   num
 1    tom    广东        2
 2    lil    上海        3
 3    dd     重庆        2

Answer 1

if object_id('[tb]') is not null drop table [tb] 
 go 
create table [tb]([id] int,[name] varchar(10),[province] varchar(10),[urlAddress] varchar(50))
insert [tb] select 1,'tom','广东','http://www.xfog.com'
union all select 2,'tim','重庆','http://www.xfog.com'
union all select 3,'john','广东','http://www.xfog.com'
union all select 4,'lil','上海','http://www.xfog.com'
union all select 5,'tom','广东','http://www.xfog.com'
union all select 6,'lil','上海','http://www.xfog.com'
union all select 7,'bb','广东','http://www.xfog.com'
union all select 8,'cc','上海','http://www.xfog.com'
union all select 9,'dd','重庆','http://www.xfog.com'
union all select 10,'lil','上海','http://www.xfog.com'
union all select 11,'dd','重庆','http://www.xfog.com';with t as
(
  select name,province,count(1) num 
  from tb
  group by name,province
) 
select id=row_number() over(order by getdate()),* 
from t a 
where not exists(
  select 1 from t 
  where province=a.province and num>a.num)
/*
id                   name       province   num
-------------------- ---------- ---------- -----------
1                    tom        广东         2
2                    lil        上海         3
3                    dd         重庆         2(3 行受影响)
*/

Answer 2

with
wang as(select name,province ,cnt=count(*) from tb group by name,province ),
wang2 as (select row=row_number() over (order by getdate()),* from wang t where not exists (select 1 from wang where province=t.province and cnt>t.cnt))
select * from wang2
row name province cnt
1 tom 广东 2
2 lil 上海 3
3 dd 重庆 2

Answer 3

SET NOCOUNT ON
declare @t table(id int,  [name] varchar(10),  province nvarchar(10),  urlAddress varchar(50)) 
--  ---  --------  ------------ 
insert @t select 1    ,'tom',    N'广东'      ,'http://www.xfog.com' 
insert @t select 2    ,'tim' ,   N'重庆'      ,'http://www.xfog.com' 
insert @t select 3    ,'john' , N'广东'      ,'http://www.xfog.com' 
insert @t select 4    ,'lil'   , N'上海'      ,'http://www.xfog.com' 
insert @t select 5    ,'tom'    ,N'广东'      ,'http://www.xfog.com' 
insert @t select 6    ,'lil'   , N'上海'     , 'http://www.xfog.com' 
insert @t select 7   , 'bb'    ,N'广东'     , 'http://www.xfog.com' 
insert @t select 8   , 'cc'    ,N'上海'    ,  'http://www.xfog.com' 
insert @t select 9  ,  'dd'    ,N'重庆'   ,   'http://www.xfog.com' 
insert @t select 10 ,   'lil'  ,N'上海'  ,  'http://www.xfog.com' 
insert @t select 11,    'dd'    ,N'重庆',      'http://www.xfog.com'
SELECT * FROM (
SELECT [NAME],province,COUNT(*)num FROM @T GROUP BY [NAME],province)T
WHERE [NAME] IN(SELECT TOP 1 [NAME] FROM (SELECT [NAME],province,COUNT(*)num FROM @T GROUP BY [NAME],province)S
WHERE S.province=T.province ORDER BY S.NUM DESC)
/*NAME       province   num
---------- ---------- -----------
lil        上海         3
tom        广东         2
dd         重庆         2*/