sql server根据列的不同内容进行标记

hank_er 2015-05-18 02:05:11

表如下：

drop table #a
go
create table #a
(
Name varchar(255),
Score float,
counts float
)
insert into #a(Name,Score,counts) values ('张一','70','')
insert into #a(Name,Score,counts) values ('张一','80','')
insert into #a(Name,Score,counts) values ('张一','90','')
insert into #a(Name,Score,counts) values ('张二','80','')
insert into #a(Name,Score,counts) values ('张二','90','')
insert into #a(Name,Score,counts) values ('张三','90','')
insert into #a(Name,Score,counts) values ('张三','100','')
insert into #a(Name,Score,counts) values ('张四','100','')

求列 counts中的统计数字，比如张一 70分的counts 是 1,80分的counts是80,90分的counts是3，张二80分的counts是1，张二90分的的counts是2，张四 100分的counts是1,看是哪个人的第几个分数，谢谢

...全文

113 2 打赏收藏转发到动态举报

写回复

用AI写文章

2 条回复

切换为时间正序

请发表友善的回复…

发表回复

hank_er 2015-05-22

打赏
举报

非常感谢！！

还在加载中灬 2015-05-18

打赏
举报

--查询
SELECT Name,Score
	,ROW_NUMBER()OVER(PARTITION BY Name ORDER BY Score)counts
FROM #a
--更新
;WITH CTE AS(
	SELECT Name,Score,counts
		,ROW_NUMBER()OVER(PARTITION BY Name ORDER BY Score)RN
	FROM #a
)
UPDATE CTE
SET counts=RN