具有交叉列划分的等级记录

Question

我需要编写一个SQL查询，它根据来自不同表中列的两个派生值的除法来获得排序记录。因此，表1有A列，表2有B列，这两个表都具有公共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_value的基础上按DESC顺序排列。我现在能够写两个问题：

• 只为某一学生选择不同的学校，并以DESC的顺序列出相应的最高总分。
• 选择按DESC顺序颁发学校的次数。

如何在上面定义的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。

Answer 1

我会使用两个聚合查询，一个用于每所学校的分数之和，另一个用于统计奖励，加入到学校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

Answer 2

     select tableA.school_id,
    (sum(marks)/(count(tableA .school_id) *1.00)) as required_value   
    from tableA inner join tableB on tableA.school_id =tableB.school_id             
    group by school_id 
    order by (sum(marks)/(count(tableA.school_id) *1.00)) desc

Answer 3

我能够用这个查询来完成这个任务：

SELECT a.school_id, total_marks/COUNT(b.school_id) as required_value FROM (
  SELECT school_id, SUM(marks) AS total_marks
  FROM TableA
  GROUP BY school_id
) a
LEFT JOIN TableB b on a.school_id = b.school_id
GROUP BY a.school_id
ORDER BY required_value DESC

第一，做你的原始查询，得到总分，分组按学校：

SELECT school_id, SUM(marks) AS total_marks
FROM TableA
GROUP BY school_id

从这里开始，这是一个简单的左加入(获取学校获得奖励COUNT(b.school_id)的所有次数)，然后做一些数学！

请参阅sql花弦 这里。

现在开始表演。这似乎比上面@Mureinik的查询稍微好一些，因为您使用的是1个派生表，而不是2：

我的问题是：

​

Mureink的查询：

​

对于小型数据集，这不太重要，但是当数据集增长时，这个查询将逐渐变慢。