如何根据sql server中平均两列的数量对数据进行排序? [英] how to sort the data based on average number of two columns in sql server?
问题描述
如何根据平均值排序数据。出现..
studentid,professorid,讲话(这些是我的专栏名称....)
我需要根据平均每个学生的数量对数据进行排序教授...
例如:教授可能有65名学生,有4种不同的讲义,平均为16,这样教授平均最高,教授学生必须首先列出) />
我能够获得另一张桌子上的教授和平均学生人数,但我无法在同一张桌子上排序!!并且使用 - count(*)over(由churchid分区)为[studentcount]这个语句我可以根据最高计数对数据进行排序..但不能进行平均..
<前lang =text> studentid professorid讲义
1 101 401
2 101 402
3 101 401
4 101 402
5 102 403
10 102 404
7 103 405
8 104 406
根据您的样本数据,我认为您需要使用ORDER BY
具有聚合函数,例如:COUNT()
。
< pre lang =sql> DECLARE @ tmp TABLE (studentid INT IDENTITY ( 1 , 1 ),professorid INT ,讲话 INT )
INSERT INTO @tmp (professorid,讲话)
VALUES ( 101 , 401 ),( 101 , 402 ),
( 101 , 401 ),( 101 , 402 ),
( 102 , 403 ),( 102 , 404 ),
( 103 , 405 ),( 104 , 406 )
SELECT professorid,CountOfStudents / CountOfLectures AS AvgCount
FROM (
SELECT professorid,COUNT(studentid) AS CountOfStudents,COUNT( DISTINCT 讲座) AS CountOfLectures
FROM @ tmp
GROUP BY professorid
) AS T
ORDER BY CountOfStudents / CountOfLectures DESC
使用CTE计算平均值,然后加入CTE通过
DECLARE @ tmp 表(studentid INT IDENTITY ( 1 , 1 ),professorid INT ,讲话 INT )
INSERT INTO @ tmp (教授,讲师)
VALUES ( 101 , 401 ),( 101 , 402 ),( 101 , 402 ),( 101 , 402 ),( 101 , 402 ),( 101 , 402 ),
( 101 , 401 ),( 101 , 402 ),
( 102 , 403 ),( 102 , 404 ),( 102 , 404 ),( 102 , 404 ),( 102 , 404 ),
( 103 ,< span class =code-digit> 405 ),( 104 , 406 );
选择 * 来自 @ TMP 跨度>;
avgStudents as
(
select professorId,count(studentid)/ count( distinct 讲话) as 来自 @ tmp group by professorId
)
select t.studentid,av .professorid,t.lectureid,av.average 来自 @ tmp t
内部 加入 avgStudents av av.professorId = t.professorId
订单 按 av.average desc 跨度>
How to sort the data based on average no. of occurances..
studentid, professorid, lectureid (these are my column names....)
I need to sort the data based on average no.of students per professor...
For ex: A professor might have 65 students, with 4 different lectureids, the average is 16, like this which professors average is highest, that professor students must be listed first)
I am able to get the professorid and average count of students in another table, but I am not able to sort it in the same table!!..And using-- count(*) over(partition by professorid) as [studentcount] this statement I am able to sort the data based on highiest count..but not able to do averaging..
studentid professorid lectureid
1 101 401
2 101 402
3 101 401
4 101 402
5 102 403
6 102 404
7 103 405
8 104 406
Based on your sample data, i think you need to useORDER BY
with aggregate function, like:COUNT()
.
DECLARE @tmp TABLE(studentid INT IDENTITY(1,1), professorid INT, lectureid INT) INSERT INTO @tmp (professorid, lectureid) VALUES(101, 401), (101, 402), (101, 401), (101, 402), (102, 403), (102, 404), (103, 405), (104, 406) SELECT professorid, CountOfStudents / CountOfLectures AS AvgCount FROM ( SELECT professorid, COUNT(studentid) AS CountOfStudents, COUNT(DISTINCT lectureid) AS CountOfLectures FROM @tmp GROUP BY professorid ) AS T ORDER BY CountOfStudents / CountOfLectures DESC
Use a CTE to calculate your averages, then join to that CTE to create an order by
DECLARE @tmp TABLE(studentid INT IDENTITY(1,1), professorid INT, lectureid INT) INSERT INTO @tmp (professorid, lectureid) VALUES(101, 401), (101, 402),(101, 402),(101, 402),(101, 402),(101, 402), (101, 401), (101, 402), (102, 403), (102, 404),(102, 404),(102, 404),(102, 404), (103, 405), (104, 406); select * from @tmp; with avgStudents as ( select professorId, count(studentid)/count( distinct lectureid) as average from @tmp group by professorId ) select t.studentid, av.professorid, t.lectureid, av.average from @tmp t inner join avgStudents av on av.professorId = t.professorId order by av.average desc
这篇关于如何根据sql server中平均两列的数量对数据进行排序?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!