SQL查询问题

问题描述：

我有一个名为StudentMarks.which SQL表由StudentID，主旨名称的，SubjectMarkSQL查询问题

我想写一个存储过程来检索StudentID，TotalSubjectMarks，MarksAverage，ClassPosition每个student.I需要找到MarksAverage的ClassPosition。对于具有最高MarksAverage的学生，ClassPosition应该是1.其他人应该得到的位置2,3,4 .... 任何人可以帮助我吗？

谢谢。

请编辑您的问题;增加以下几点可能会给你更好的答案：1.你到目前为止尝试过什么（这很重要，否则人们会认为“做你自己的作业”并忽略这个问题）？ 2.你得到了什么结果？ 3.这与你所期望的结果有何不同？ – Piskvor 2010-09-23 10:20:23

答

SELECT StudentID, TotalSubjectMark, MarksAverage, 
    ROW_NUMBER() OVER (ORDER BY MarksAverage DESC) ClassPosition 
    FROM (
    SELECT StudentID, 
    SUM(SubjectMark) TotalSubjectMark, 
    AVG(SubjectMark) MarksAverage 
    FROM StudentMarks sm 
    GROUP BY StudentId 
)

答

试试这个：

select * , ROW_NUMBER() OVER(ORDER BY avgmarks) AS ClassPosition from 
(
select 
studentid, sum(subjectmark) as total , AVG(subjectmark) as avgmarks 
from studentmarks group by studentid 
) d

答

使用SQL Server 2005 +，你可以尝试

DECLARE @StudentMarks TABLE(
     StudentID INT, 
     SubjectName VARCHAR(50), 
     SubjectMark FLOAT 
) 

INSERT INTO @StudentMarks SELECT 1, 'A', 80 
INSERT INTO @StudentMarks SELECT 1, 'B', 80 
INSERT INTO @StudentMarks SELECT 1, 'C', 80 
INSERT INTO @StudentMarks SELECT 2, 'A', 60 
INSERT INTO @StudentMarks SELECT 2, 'B', 60 
INSERT INTO @StudentMarks SELECT 2, 'C', 60 


;WITH Marks AS (
     SELECT StudentID, 
       SUM(SubjectMark) TotalSubjectMarks, 
       AVG(SubjectMark) MarksAverage 
     FROM @StudentMarks 
     GROUP BY StudentID 
) 
SELECT *, 
     ROW_NUMBER() OVER(ORDER BY MarksAverage DESC) Position 
FROM Marks

相关推荐