如何在sql中使用average [英] how to use average in sql

问题描述

我在我的项目中有一个表评级，其中我有属性Teacher_Id（int），Student_Id（int），Rating（int），Date（DateTime）;

教师会评价他们的学生，现在我想选择那些评分最高的学生的身份证，但是如果老师评价学生14并且两位教师评价学生22和2则我有问题，因此两者都有相同的评分。我有一个表名最好的学生现在我有一个问题，如何找到学生的平均值，并根据评级选择最佳学生ID。

i have a table Rating in my project in which i have attribute Teacher_Id (int),Student_Id(int),Rating(int),Date(DateTime);
Teachers will rate their Student , now i want to select id of those student which have highest Rating but i have a problem if a teacher rate a student1 "4" and two teacher rate student2 "2" and "2" thus both have same ratings. I have a table name best student now this i have a problem to how to find the average of student and select best student id based on ratings.

推荐答案

if a teacher rate a student1 "4" and two teacher rate student2 "2" and "2" thus both have same ratings

怎么样可能???



尝试以下查询：

How is it possible???

Try below query:

--Teacher_Id (int),Student_Id(int),Rating(int),Date(DateTime);

DECLARE @tt TABLE ([Teacher_Id] INT, [Student_Id] INT, [Rating] INT, [Date] DATETIME)

INSERT INTO @tt ([Teacher_Id], [Student_Id], [Rating], [Date])
VALUES(1,1,2,'2013-01-02')
INSERT INTO @tt ([Teacher_Id], [Student_Id], [Rating], [Date])
VALUES(1,1,2,'2013-01-03')
INSERT INTO @tt ([Teacher_Id], [Student_Id], [Rating], [Date])
VALUES(1,2,4,'2013-01-04')
INSERT INTO @tt ([Teacher_Id], [Student_Id], [Rating], [Date])
VALUES(1,3,5,'2013-01-05')
INSERT INTO @tt ([Teacher_Id], [Student_Id], [Rating], [Date])
VALUES(1,3,1,'2013-01-06')
INSERT INTO @tt ([Teacher_Id], [Student_Id], [Rating], [Date])
VALUES(1,4,4,'2013-01-07')
INSERT INTO @tt ([Teacher_Id], [Student_Id], [Rating], [Date])
VALUES(1,4,4,'2013-01-08')


SELECT  [Student_Id], [Rating]
FROM @tt

SELECT  [Student_Id], AVG([Rating]) AS AVERAGE
FROM @tt
GROUP BY [Student_Id]

结果：

1.查询

Results:
1. query

Student_Id	Rating
1		2
1		2
2		4
3		5
3		1
4		4
4		4

2.查询

2. query

Student_Id	Average
1		2
2		4
3		3
4		4

我不会为你做的工作，但我会给你一个正确的方向：



你可以得到每个学生的平均值与 GROUP BY 条款一起使用。



进一步提示：如果学生可能被列入但尚未被一名或多名教师评定的可能性，您需要一种机制来处理这种情况，具体取决于未评级学生条目的默认值。

I won''t do the work for you, but I''ll give you a push in the right direction:

You can get the averages for each student in conjunction with a GROUP BY clause.

Further hint: if the possibility exists for a student to be listed but as yet unrated by one or more instructors, you need a mechanism to handle this, depending upon the default value of unrated student entries.

嗨Jackson，



请查看以下查询。



它将有助于了解SQL服务器的平均值。

Hi Jackson ,

Please find the below query .

it will help to know about average in SQL server.

select Name,SUM(marks) as Sum ,AVG(marks) as Avegage from sa  Group By Name 

这篇关于如何在sql中使用average的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！