！！！使用SQL对成绩进行排名 在线等。。。。。

Question

如题：
表结构如下学号    姓名    科目    成绩
001 张三 语文 80
001 张三 数学 85
002 李四 语文 90
002 李四 数学 80
003 王五 语文 70
003 王五 数学 80怎么样通过SQL语句实现如下表效果：
学号  姓名   数学    语文     总成绩      总名次       数学名次    语文名次        
002   李四   80      90       170         1           2           1
003   王五   80      70       150         3           2           3
001   张三   85      80       165         2           1           2
其中数学有个并列第二的 如果还有个学生的数学成绩是79
那么他的数学名次就是第三名（总名次跟语文名次同数学一样的排名方式）
希望各位大虾帮忙指点迷津 问题解决 分马上送上

Answer 1

DECLARE @TB TABLE([学号] VARCHAR(3), [姓名] NVARCHAR(2), [科目] NVARCHAR(2), [成绩] INT)
INSERT @TB 
SELECT '001', N'张三', N'语文', 80 UNION ALL 
SELECT '001', N'张三', N'数学', 85 UNION ALL 
SELECT '002', N'李四', N'语文', 90 UNION ALL 
SELECT '002', N'李四', N'数学', 80 UNION ALL 
SELECT '003', N'王五', N'语文', 70 UNION ALL 
SELECT '003', N'王五', N'数学', 80;WITH CTE AS
(
SELECT [学号],[姓名],
MAX(CASE WHEN [科目]=N'语文' THEN  [成绩] ELSE 0 END) AS 语文,
MAX(CASE WHEN [科目]=N'数学' THEN  [成绩] ELSE 0 END) AS 数学,
    SUM([成绩]) AS 总成绩    
FROM @TB 
GROUP BY [学号],[姓名]
)
SELECT *,RANK() OVER (ORDER BY 总成绩 DESC),
RANK() OVER (ORDER BY 语文 DESC),
RANK() OVER (ORDER BY 数学 DESC)
FROM CTE
/*
学号   姓名   语文          数学          总成绩                                                   
---- ---- ----------- ----------- ----------- -------------------- -------------------- --------------------
001  张三   80          85          165         2                    2                    1
003  王五   70          80          150         3                    3                    2
002  李四   90          80          170         1                    1                    2*/

Answer 2

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([学号] varchar(3),[姓名] varchar(4),[科目] varchar(4),[成绩] int)
insert [tb]
select '001','张三','语文',80 union all
select '001','张三','数学',85 union all
select '002','李四','语文',90 union all
select '002','李四','数学',80 union all
select '003','王五','语文',70 union all
select '003','王五','数学',80select *,
总名次=rank() over( order by 总成绩 desc),
数学名次=rank() over( order by 数学 desc),
语文名次=rank() over( order by 语文 desc)
from
(
select 学号,姓名,
语文=sum(case when 科目='语文' then 成绩 else 0 end),
数学=sum(case when 科目='数学' then 成绩 else 0 end),
总成绩=sum(成绩)
from tb group by 学号,姓名
)t/**
学号   姓名   语文          数学          总成绩         总名次                  数学名次                 语文名次
---- ---- ----------- ----------- ----------- -------------------- -------------------- --------------------
002  李四   90          80          170         1                    2                    1
001  张三   80          85          165         2                    1                    2
003  王五   70          80          150         3                    2                    3(3 行受影响)
**/
 

Answer 3

select 学号,姓名,[数学],[语文],row_number() over(order by [数学]+[语文]  desc) as 总名次
,row_number() over(order by [数学]  desc) as 数学名次
,row_number() over(order by [语文]  desc) as 语文名次
from #tb
pivot(max(成绩) for 科目 in([数学],[语文])) pvt
order by 学号学号   姓名                  数学          语文                  总名次                 数学名次                 语文名次
---- ---------- ----------- ----------- -------------------- -------------------- --------------------
001  张三                  85          80                    2                    1                    2
002  李四                  80          90                    1                    3                    1
003  王五                  80          70                    3                    2                    3(3 行受影响)

Answer 4

丢了总成绩select 学号,姓名,[数学],[语文],[数学]+[语文] as 总成绩
,row_number() over(order by [数学]+[语文]  desc) as 总名次
,row_number() over(order by [数学]  desc) as 数学名次
,row_number() over(order by [语文]  desc) as 语文名次
from #tb
pivot(max(成绩) for 科目 in([数学],[语文])) pvt
order by 学号
学号   姓名                  数学          语文         总成绩                  总名次                 数学名次                 语文名次
---- ---------- ----------- ----------- ----------- -------------------- -------------------- --------------------
001  张三                  85          80         165                    2                    1                    2
002  李四                  80          90         170                    1                    3                    1
003  王五                  80          70         150                    3                    2                    3(3 行受影响)

Answer 5

sql 2000 可以用临时表和子查询

Answer 6

'Rank' is not a recognized function name

Answer 7

--写个2000的
----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2010-04-16 19:57:13
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([学号] varchar(3),[姓名] varchar(4),[科目] varchar(4),[成绩] int)
insert [tb]
select '001','张三','语文',80 union all
select '001','张三','数学',85 union all
select '002','李四','语文',90 union all
select '002','李四','数学',80 union all
select '003','王五','语文',70 union all
select '003','王五','数学',80
--------------开始查询--------------------------
select
 * 
into
 #t 
from
(
 select
  学号,姓名,
  语文=sum(case when 科目='语文' then 成绩 else 0 end),
  数学=sum(case when 科目='数学' then 成绩 else 0 end),
  总成绩=sum(成绩)
 from
  tb
 group by
  学号,姓名)t
--select * from #t
select 
  *,
  总名次=(select count(总成绩) from #t where 总成绩>=t.总成绩),
  数学名次=(select count(数学) from #t where 数学>=t.数学),
  数学名次=(select count(语文) from #t where 语文>=t.语文)
from
  #t t
order by
  1drop table #t----------------结果----------------------------
/* 学号   姓名   语文          数学          总成绩         总名次         数学名次        数学名次
---- ---- ----------- ----------- ----------- ----------- ----------- -----------
001  张三   80          85          165         2           1           2
002  李四   90          80          170         1           3           1
003  王五   70          80          150         3           3           3(3 行受影响)
*/

Answer 8

--有并列的 修改下
----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2010-04-16 19:57:13
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([学号] varchar(3),[姓名] varchar(4),[科目] varchar(4),[成绩] int)
insert [tb]
select '001','张三','语文',80 union all
select '001','张三','数学',85 union all
select '002','李四','语文',90 union all
select '002','李四','数学',80 union all
select '003','王五','语文',70 union all
select '003','王五','数学',80
--------------开始查询--------------------------
select
 * 
into
 #t 
from
(
 select
  学号,姓名,
  语文=sum(case when 科目='语文' then 成绩 else 0 end),
  数学=sum(case when 科目='数学' then 成绩 else 0 end),
  总成绩=sum(成绩)
 from
  tb
 group by
  学号,姓名)t
--select * from #t
select 
  *,
  总名次=(select count(distinct 总成绩) from #t where 总成绩>=t.总成绩),
  数学名次=(select count(distinct 数学) from #t where 数学>=t.数学),
  数学名次=(select count(distinct 语文) from #t where 语文>=t.语文)
from
  #t t
order by
  1drop table #t----------------结果----------------------------
/* 学号   姓名   语文          数学          总成绩         总名次         数学名次        数学名次
---- ---- ----------- ----------- ----------- ----------- ----------- -----------
001  张三   80          85          165         2           1           2
002  李四   90          80          170         1           2           1
003  王五   70          80          150         3           2           3(3 行受影响)
*/

Answer 9

create table rankorder(
orderid int,
qty int
)
go
--插入数据
insert rankorder values(30,10)
insert rankorder values(10,10)
insert rankorder values(80,10)
insert rankorder values(40,10)
insert rankorder values(30,15)
insert rankorder values(30,20)
insert rankorder values(22,20)
insert rankorder values(21,20)
insert rankorder values(10,30)
insert rankorder values(30,30)
insert rankorder values(40,40)
go
SELECT orderid,qty,
  ROW_NUMBER() OVER(ORDER BY qty) AS rownumber,
  RANK()       OVER(ORDER BY qty) AS [rank],
  DENSE_RANK() OVER(ORDER BY qty) AS denserank ,
  NTILE(3) OVER(ORDER BY qty) AS [NTILE]
FROM rankorder
ORDER BY qty--结果
--ROW_NUMBER()是按qty由小到大逐一排名，不并列，排名连续
--RANK()是按qty由小到大逐一排名，并列，排名不连续
--DENSE_RANK()是按qty由小到大逐一排名，并列，排名连续
--NTILE()是按qty由小到大分成3组逐一排名，并列，排名连续
/*
orderid    qty    rownumber    rank    denserank    NTILE
30        10    1            1        1            1
10        10    2            1        1            1
80        10    3            1        1            1
40        10    4            1        1            1
30        15    5            5        2            2
30        20    6            6        3            2
22        20    7            6        3            2
21        20    8            6        3            2
10        30    9            9        4            3
30        30    10           9        4            3
40        40    11           11       5            3*/

Answer 10

--------------------SQL Server数据格式化工具-------------------
---------------------------------------------------------------
-- DESIGNER :happycell188(喜喜)
--       QQ :584738179
-- Development Tool :Microsoft Visual C++ 6.0    C Language 
-- FUNCTION :CONVERT DATA TO T-SQL
---------------------------------------------------------------
-- Microsoft SQL Server  2005
-- Developer Edition on Microsoft Windows XP [版本 5.1.2600]
---------------------------------------------------------------
---------------------------------------------------------------use test
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
学号 char(4),
姓名 char(5),
科目 char(5),
成绩 int
)
go
--插入测试数据
insert into tb select '001','张三','语文',80
union all select '001','张三','数学',85
union all select '002','李四','语文',90
union all select '002','李四','数学',80
union all select '003','王五','语文',70
union all select '003','王五','数学',80
go
--代码实现select 学号,姓名,数学,语文,数学+语文 总成绩,
rank()over(order by (数学+语文)desc)总名次,
rank()over(order by 数学 desc)数学名次,
rank()over(order by 语文 desc)语文名次
from tb pivot(max(成绩)for 科目 in (数学,语文))pvt/*测试结果学号 姓名 数学 语文 总成绩 总名次 数学名次 语文名次   
--------------------------------------------------------
002  李四  80 90 170 1 2 1
001  张三  85 80 165 2 1 2
003  王五  80 70 150 3 2 3(n行受影响)
*/