问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张A 语文 74
张B 语文 83
张C 语文 93
李D 语文 74
王E 语文 66
李F 语文 84
李G 语文 94
共N条记录
统计后，想变成(得到如下结果)：
姓名 成绩高于本人人数
张A 4
张B 3
张C 1
李D 4
王E 6
李F 2
李G 0
sql 查询语句，如何获得所要的结果？
在线等!!!!!!!!!!!!!!!!!
------解决思路----------------------
--SQL2005+
SELECT 姓名,课程
,RANK()OVER(PARTITION BY 课程 ORDER BY 分数 DESC)-1 [成绩高于本人人数]
FROM tb
--SQL2000
SELECT T1.姓名,T1.课程
,ISNULL(COUNT(T2.姓名),0)[成绩高于本人人数]
FROM tb T1
LEFT JOIN tb T2 ON T1.课程=T2.课程 AND T1.分数<T2.分数
GROUP BY T1.姓名,T1.课程
------解决思路----------------------
IF OBJECT_ID('tempdb..#tb','U') > 0 drop table #tb
create table #tb
(
name nvarchar(10),
kecheng nvarchar(10),
fenshu int
)
go
insert into #tb values
(N'张A', N'语文', N'74'),
(N'张B', N'语文', N'83'),
(N'张C', N'语文', N'93'),
(N'李D', N'语文', N'74'),
(N'王E', N'语文', N'66'),
(N'李F', N'语文', N'84'),
(N'李G', N'语文', N'94')
GO
SELECT name,kecheng ,RANK()OVER(PARTITION BY kecheng ORDER BY fenshu DESC)-1 高于分数统计
FROM #tb
SELECT *,(select COUNT(*) from #tb where kecheng = a.kecheng and fenshu > a.fenshu) 高于分数统计 FROM #tb a
------解决思路----------------------
你这个子查询的方式，数据量一多当然会有效率问题了
如果是SQL2005，用这个可以提高效率
SELECT 姓名,课程
,RANK()OVER(PARTITION BY 课程 ORDER BY 分数 DESC)-1 [成绩高于本人人数]
FROM tb
如果是2000，我1楼的方式，也比那个子查询要好点
------解决思路----------------------
试试这个
create table #tb
(
name nvarchar(10),
kc nvarchar(10),
fs int
)
go
insert into #tb values
(N'张A', N'语文', N'74'),
(N'张B', N'语文', N'83'),
(N'张C', N'语文', N'93'),
(N'李D', N'语文', N'74'),
(N'王E', N'语文', N'66'),
(N'李F', N'语文', N'84'),
(N'李G', N'语文', N'94')
GO
select * from #tb
select name,(select COUNT(*) from #tb b where b.fs>a.fs)
from #tb a
order by substring(name,2,1)
------解决思路----------------------
借用上楼的表结构，采用自连接：
SELECT a.name,count(*)
FROM #tb as a
JOIN #tb AS b
ON a.name<>b.name AND a.fs<b.fs AND a.kc=b.kc
GROUP BY a.name
ORDER BY a.name DESC