时间戳

首页 > 解决方案 > 使用跨表列划分对记录进行排名

mysql - 使用跨表列划分对记录进行排名

问题描述

我需要编写一个 SQL 查询，它根据来自不同表中的列的两个派生值的划分来带来排名记录。因此，表 1 有一个 A 列，表 2 有一个 B 列，两个表都有 common school_id。表 A 中的数据如下所示：

school_id | student_id | subject_id | marks
123         ABC          7Y8U         94
234         UHT          4Y8U         87
123         ABC          8P0F         49
567         FCV          5Q1U         70

表 B 中的数据如下所示：

school_id | prize_amt
123         3000     
234         4700     
123         8210     
567         3200

我需要做的计算是：

required_value = total marks per school per student / number of times that school is awarded

我需要按顺序排列n的记录。我现在可以写两个查询：required_valueDESC

如何获得required_value以上定义的多条记录？期望的结果将是：

school_id | required_value   
234         87     
123         71.50     
567         70

所以，有 ID 的学校的123分数是 94 + 49 = 143，它已经获得了 2 次奖励，所以它的required_value值是 143/2 = 71.50。

标签： mysqlsqlselect

解决方案

我会使用两个聚合查询，一个是对每所学校的分数求和，一个是对奖励进行计数，将它们加入学校 ID 并划分结果：

SELECT   a.school_id, sum_marks / num_awards
FROM     (SELECT   school_id, SUM(marks) AS sum_marks
          FROM     a
          GROUP BY school_id) a
JOIN     (SELECT   school_id, COUNT(*) AS num_awards
          FROM     b
          GROUP BY school_id) b ON a.school_id = b.school_id
ORDER BY 2 DESC

推荐阅读