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','湖南',70select
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
*/
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','湖南',70select
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
*/
解决方案 »
- 如何获取某一周是从几号到几号呢?
- 不安装SQL2005等数据库,只安装VS2005可以用数据库吗?
- .MDF是备用恢复数据库的文件吗?为什么2005不能restore这个后缀的数据库?
- 1千万条数据全文检索,如何高效得到命中的记录条数!期待高手出现.....
- 有没有方法在指定时间内执行一次存储过程。
- 请教——SQL server 数据库是做什么的?怎么往里面写数据?up有分(接分人请进)up有分(邹建 水影注意接分)
- 菜鸟询问个sql查询命令
- 在线等待,请问如何为以有数据的字段加check约束(用sql语句实现)
- 高分求教:这个触发器错在那?
- 数据库疑问 求高手帮助
- SQL语句更改关键字段的数据类型
- 触发器初学者请教!
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
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', '湖南', 70select * , rankByProvince=(select count(1) from @t1 where province=a.province and clickcount>a.clickcount)+1 from @t1 a
order by province,rankByProvinceid 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 行)
where province=a.province and (clickcount>a.clickcount or (clickcount=a.clickcount and id<a.id)
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 a.* from @t a
inner join(select province,sum(clickcount) sc from @t group by province) b
on a.province=b.province
order by sc,a.province,clickcount desc--若只按同组内记录排
select a.* from @t a order by province,clickcount desc,id
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','湖南',60select
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
*/
insert tb select 1, 1001, 'aa', '广东', 100
union all select 2, 1002, 'bb', '广西', 120
union all select 3, 1003, 'cc', '广东', 20
union all select 4, 1004, 'dd', '湖南', 60
union all select 5, 1005, 'ee', '湖南', 50
union all select 6, 1006, 'ff', '湖南', 70select A.* , rankByProvince=(select count(1) from tb where province=A.province and clickcount>=A.clickcount)
from tb as A
order by province, clickcount desc--result
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 row(s) affected)
insert into @t select 1,1001,'aa','广东',120
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 8,1006,'ff','湖南',110
insert into @t select 5,1005,'ee','湖南',60
insert into @t select 6,1006,'ff','湖南',60
insert into @t select 7,1003,'cc','广东',120select a.*,1+(select count(*) from @t b where b.province=a.province and
b.uid<
case when b.clickcount=a.clickcount then
a.uid
else 1000000
end
and
b.clickcount>
case when b.clickcount=a.clickcount then
b.clickcount-1
else
a.clickcount
end
) cnk from @t a
order by province,cnk