mysql - 创建具有多列和连接列的公式查询

Question

我正在尝试生成一个公式，将我们学生的奖励分数与他们的负面行为标记相比较。

学生的积极行为会获得 LEAP 分数(在 transactions 表中)。他们根据奖励类别获得更多积分，即 Model Citizen给学生 10 分。

另一方面，学生会因为消极行为而被单独标记。然后在数据库表中对标志的类别进行加权，即 Aggressive Defiance类别将具有 4 的高权重而Low Level Disruption只会值 1 .

因此，困难在于尝试考虑旗帜类别的权重。它们存储在 categories 中Weight 下的表格柱子。

---

这是 SQL fiddle :http://sqlfiddle.com/#!2/2e5756

---

在我看来，伪 SQL 代码看起来像这样......

SELECT
    CONCAT( stu.Surname, ", ", stu.Firstname ) AS `Student`,
    SUM(t.Points) AS `LEAP Points`,
    SUM(<<formula>>) AS `Flags`
    ( `LEAP Points` - `Flags` ) AS `Worked Out Points Thing`
FROM student stu
LEFT JOIN transactions t ON t.Recipient_ID = stu.id
LEFT JOIN flags f ON f.Student_ID = stu.id
LEFT JOIN categories c ON f.Category_ID = c.ID
GROUP BY stu.id

但是，它是 <<formula>>我不知道如何在 MySQL 中实现。它需要是这样的:

SUM OF[ Each of Student's Flags * that Flag's Category Weighting ]

所以，如果一个学生有这些标志......

#1  f.Reason "Being naughty", f.Category_ID "1", c.Title "Low Level Disruption", c.Weight "1"
#1  Reason "Aggressively naughty!", Category "Aggressive Defiance", Category Weighting "4"
#1  Reason "Missed detention", Category "Missed Detention", Category Weighting "3"

他们总共有 1+4+3 = 9在 Worked Out Points Thing 中使用的点等式。

因此，所需的输出本质上是......

Student         LEAP Points     Flags Equation Points       LEAP Points minus Flag Points
D Wraight       1000            800                         200
D Wraight2      500             800                         -300
D Wraight3      1200            300                         900

---

从上面的 SQL fiddle 中，这是所需的输出。我错过了一些学生，因为我必须手动解决这些问题:

STUDENT         FLAGS               LEAP            EQUATION
137608          4 (2+2)             12 (2+5+5)      8 (12-4)
139027          2 (2)               7 (2+5)         5 (7-2)
139041          4 (2+1+1+NULL)      8 (2+2+2+2)     4 (8-4)
139892          4 (4)               0               -4 (0-4)
138832          4 (4)               0               -4 (0-4)
34533           4 (4)               0               -4 (0-4)
137434          0                   10 (2*5)        10 (10-0)

---

这将帮助我们确定在考虑年终奖励旅行时我们为每个学生提供的选择。

希望这是有道理的..试图解释它让我有点困惑..

提前致谢

Answer 1

首先找出你的“公式”位，因为它是最深的部分。向外工作。建立一个标志表 * 每个学生的体重

select sum(weight), student_id from flags f
join categories c
on f.category_id = c.id
group by student_id

所以现在您有一个标志值表，可以从每个学生的交易总和中减去

select sum(points), recipient_id from transactions
group by recipient_id

所以现在我们有两个表，按学生 ID 分别有正值和负值(显然假设学生 ID 是收件人 ID)您希望那些具有事务但没有标志的结果出现在结果中，所以外部连接。并且数字减去 null 是 null 所以 ifnull 函数在标志上得到 0

select a.student, points - ifnull(penalties, 0) as netPoints
from

(select sum(points) as points, recipient_id as student from transactions
group by student) as a

left outer join

(select sum(weight) as penalties, student_id as student from flags f
join categories c
on f.category_id = c.id
group by student) as b

on

a.student = b.student

所以里面的名字就是

select
concat(firstname, ', ', surname) as name,
ifnull(points,0) as totalPoints,
ifnull(penalties,0) as totalPenalties,
ifnull(points,0) - ifnull(penalties, 0) as netPoints,
ifnull(countFlags, 0)

from
student
left join

(select sum(points) as points, recipient_id as student from transactions
group by student) as a

on student.id = a.student

left join

(select sum(weight) as penalties, count(f.id) as countFlags, student_id as student from flags f
join categories c
on f.category_id = c.id
group by student) as b

on

student.id = b.student

连接条件始终来自学生的 id 列，该列永远不会为空。可能有更有效的方法，但谁在乎呢？