新手求sql解决办法，

[hq2008]

id uid name province clickcount
1 1001 aa 广东 100
2 1002 bb 广西 120
3 1003 cc 广东 20
4 1004 dd 湖南 60
5 1005 ee 湖南 50
6 1006 ff 湖南 70
. . . . .
. . . . .
怎么样通过按省份分组然后按点击数得到各省份各自的排名以下的结构(表变量或临时表可以)

uid name province clickcount rankByProvince
1001 aa 广东 100 1
1003 cc 广东 20 2
1006 ff 湖南 70 1
1004 dd 湖南 60 2
1005 ee 湖南 50 3

[hq2008]

要求TotalclickCount相同的时候，rankbyprovince的排名不会重复，各位大狭们怎么实现,

[Hinco]

要求TotalclickCount相同的时候，rankbyprovince的排名不会重复,只须把计算排名的where语句修改一下
where province=a.province and (clickcount>a.clickcount or (clickcount=a.clickcount and id<a.id))

[子陌红尘]

declare @t table(id int,uid int,name varchar(4),province varchar(4),clickcount int)
insert into @t select 1,1001,'aa','广东',100
insert into @t select 2,1002,'bb','广西',120
insert into @t select 3,1003,'cc','广东',20
insert into @t select 4,1004,'dd','湖南',60
insert into @t select 5,1005,'ee','湖南',60
insert into @t select 6,1006,'ff','湖南',60

select
t.uid,
t.name,
t.province,
t.clickcount,
isnull((select
count(*)
from @t
where
province=t.province and (clickcount>t.clickcount or (clickcount=t.clickcount and uid<t.uid))),0)+1 as rankByProvince
from
@t t
order by
province,rankByProvince

/*
uid name province clickcount rankByProvince
----------- ---- -------- ----------- --------------
1001 aa 广东 100 1
1003 cc 广东 20 2
1002 bb 广西 120 1
1004 dd 湖南 60 1
1005 ee 湖南 60 2
1006 ff 湖南 60 3
*/

[hq2008]

15万条记录用表变量好吗？

[hq2008]

libin_ftsafe(子陌红尘：当libin告别ftsafe) ( ) 接分 谢谢你对这个问题的帮助

[子陌红尘]

select
t.uid,t.name,t.province,t.clickcount,
isnull((select count(*) from 表 where province=t.province and clickcount>t.clickcount),0)+1 as rankByProvince
from
表 t

[子陌红尘]

declare @t table(id int,uid int,name varchar(4),province varchar(4),clickcount int)
insert into @t select 1,1001,'aa','广东',100
insert into @t select 2,1002,'bb','广西',120
insert into @t select 3,1003,'cc','广东',20
insert into @t select 4,1004,'dd','湖南',60
insert into @t select 5,1005,'ee','湖南',50
insert into @t select 6,1006,'ff','湖南',70

select
t.uid,t.name,t.province,t.clickcount,
isnull((select count(*) from @t where province=t.province and clickcount>t.clickcount),0)+1 as rankByProvince
from
@t t
order by
province,rankByProvince

/*
uid name province clickcount rankByProvince
----------- ---- -------- ----------- --------------
1001 aa 广东 100 1
1003 cc 广东 20 2
1002 bb 广西 120 1
1006 ff 湖南 70 1
1004 dd 湖南 60 2
1005 ee 湖南 50 3
*/

[zjcxc]

-- sql 2005

select
t.uid,t.name,t.province,t.clickcount,
rankByProvince = RANK() OVER(PARTITION BY province ORDER BY clickcount DESC)
from
@t t
order by
province,rankByProvince

[点点星灯]

declare @tab table(id int,uid varchar(20),name varchar(20),province varchar(20),clickcount int)
insert @tab values(1,'1001','aa','广东',100)
insert @tab values(2,'1002','bb','广西',120)
insert @tab values(3,'1003','cc','广东',20)
insert @tab values(4,'1004','dd','湖南',60)
insert @tab values(5,'1005','ee','湖南',50)
insert @tab values(6,'1006','ff','湖南',70)

select a.uid,a.name,a.province,a.clickcount,
[rankByProvince]=(select count(1) from @tab where province=a.province and a.clickcount<=clickcount)
from @tab a order by province,clickcount desc

[dawugui]

declare @t1 table(id int, uid int, name varchar(10),province varchar(10),clickcount int)

insert into @t1 select 1, 1001, 'aa', '广东', 100
insert into @t1 select 2, 1002, 'bb', '广西', 120
insert into @t1 select 3, 1003, 'cc', '广东', 20
insert into @t1 select 4, 1004, 'dd', '湖南', 60
insert into @t1 select 5, 1005, 'ee', '湖南', 50
insert into @t1 select 6, 1006, 'ff', '湖南', 70

select * , rankByProvince=(select count(1) from @t1 where province=a.province and clickcount>a.clickcount)+1 from @t1 a
order by province,rankByProvince

id uid name province clickcount rankByProvince
----------- ----------- ---------- ---------- ----------- --------------
1 1001 aa 广东 100 1
3 1003 cc 广东 20 2
2 1002 bb 广西 120 1
6 1006 ff 湖南 70 1
4 1004 dd 湖南 60 2
5 1005 ee 湖南 50 3

（所影响的行数为 6 行）

[jackeyabc]

declare @tab table(id int,uid varchar(20),name varchar(20),province varchar(20),clickcount int)
insert @tab values(1,'1001','aa','广东',100)
insert @tab values(2,'1002','bb','广西',120)
insert @tab values(3,'1003','cc','广东',20)
insert @tab values(4,'1004','dd','湖南',60)
insert @tab values(5,'1005','ee','湖南',50)
insert @tab values(6,'1006','ff','湖南',70)

select *,排名=(select count(*)+1 from @tab a where a.province=b.province and a.clickcount>b.clickcount) from @tab b
order by province,排名

-------------------------------------------
id uid name province clickcount 排名
1 1001 aa 广东 100 1
3 1003 cc 广东 20 2
2 1002 bb 广西 120 1
6 1006 ff 湖南 70 1
4 1004 dd 湖南 60 2
5 1005 ee 湖南 50 3