DECLARE @table TABLE(Names NVARCHAR(60),subject NVARCHAR(60),subscore int)
DECLARE @table1 TABLE(rownum INT,Names NVARCHAR(60),subject NVARCHAR(60),subscore int)
DECLARE @index INT;
SET @index=0;
INSERT INTO @table VALUES('三毛','语文',96);
INSERT INTO @table VALUES('三毛','数学',92);
INSERT INTO @table VALUES('三毛','英语',90);
INSERT INTO @table VALUES('四毛','语文',96);
INSERT INTO @table VALUES('四毛','数学',92);
INSERT INTO @table VALUES('四毛','英语',90);
INSERT INTO @table VALUES('五毛','语文',96);
INSERT INTO @table VALUES('五毛','数学',92);
INSERT INTO @table1 VALUES(0,'三毛','语文',96);
INSERT INTO @table1 VALUES(0,'三毛','数学',92);
INSERT INTO @table1 VALUES(0,'三毛','英语',90);
SELECT ROW_NUMBER()over(order by 总分 desc),* FROM (
SELECT names,
SUM(CASE subject when '语文' THEN subscore ELSE 0 end) '语文' ,
SUM(CASE subject when '数学' THEN subscore ELSE 0 end) '数学' ,
SUM(CASE subject when '英语' THEN subscore ELSE 0 end) '英语' ,
SUM(ISNULL(subscore,0)) '总分'
FROM @table GROUP BY names ) t ORDER BY 总分 DESC
