使用多列和联合列创建公式化查询
使用多列和联合列创建公式化查询
提问于 2014-03-11 12:58:00
我正在努力制定一种公式，使我们学生的奖励分数与他们的消极行为标志相对照。
学生的积极行为(在transactions表中)被给予了飞跃点。他们得到更多的分数取决于他们的奖励类别，即Model Citizen给学生10分。
另一方面，对学生的负面行为给予单一旗帜。然后在数据库表中对国旗类别进行加权，即Aggressive Defiance类别将具有较高的4权重，而Low Level Disruption值仅为1。
因此，困难在于试图将国旗类别的权重考虑在内。它们存储在categories表中的Weight列下。
这里是SQL小提琴： 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>>中实现它。它应该是这样的：
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方程中总共使用Worked Out Points Thing = 9点。
因此，所需的输出基本上是..。
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小提琴来看，这是所需的输出。我错过了一些学生，因为我不得不手工解决这些问题：
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)这将帮助我们制定我们提供给每个学生在年底奖励旅行的选择。
希望这有意义..。我的头有点不舒服，想解释一下。
提前谢谢你，
回答 2
Stack Overflow用户
回答已采纳
发布于 2014-03-12 23:52:55
先找出你的“公式”位，因为它是最深的部分。向外工作。为每个学生建立一个旗子*重量表
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，所以如果要获得0的标志上的ifnull函数
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列，它从不为null。也许有更有效的方法，但谁在乎呢？
Stack Overflow用户
发布于 2014-03-11 13:55:02
回到这个问题(冒着重复自己的风险！)，给定以下数据集，所需的结果集将是什么样子.
SELECT * FROM flags;
+------+------------+----------+---------------------+-----------+-------------+--------------------------+---------------------+
| ID | Student_ID | Staff_ID | Datetime | Period_ID | Category_ID | Action_Taken_Category_ID | Action_Taken_Status |
+------+------------+----------+---------------------+-----------+-------------+--------------------------+---------------------+
| 8843 | 137608 | 35003 | 2014-03-11 08:31:00 | 8 | 16 | 7 | P |
| 8844 | 137608 | 35003 | 2014-03-11 08:31:00 | 8 | 16 | 7 | P |
| 8845 | 139027 | 35003 | 2014-03-11 08:31:00 | 8 | 16 | 7 | P |
| 8846 | 139041 | 35003 | 2014-03-11 08:31:00 | 8 | 16 | 7 | P |
| 8847 | 139041 | 34961 | 2014-03-11 09:01:02 | 2 | 12 | 26 | P |
| 8848 | 139041 | 34996 | 2014-03-11 09:23:21 | 3 | 12 | 27 | C |
| 8849 | 139041 | 35022 | 2014-03-11 11:07:46 | 4 | 34 | 28 | P |
| 8850 | 139892 | 138439 | 2014-03-11 11:12:47 | 4 | 21 | 7 | C |
| 8851 | 138832 | 138439 | 2014-03-11 11:12:48 | 4 | 21 | 7 | C |
| 8852 | 34533 | 138439 | 2014-03-11 11:12:48 | 4 | 21 | 7 | C |
+------+------------+----------+---------------------+-----------+-------------+--------------------------+---------------------+
SELECT * FROM categories;
+----+------+--------------------------------------+--------+----------+
| ID | Type | Title | Weight | Added_By |
+----+------+--------------------------------------+--------+----------+
| 10 | F | Low level disruption | 1 | NULL |
| 11 | F | Swearing directly at another student | 2 | NULL |
| 12 | F | Late | 1 | NULL |
| 13 | F | Absconded | 3 | NULL |
| 14 | F | Refusal to follow instruction | 3 | NULL |
| 15 | F | Smoking | 2 | NULL |
| 16 | F | No homework | 2 | NULL |
| 17 | F | Disruptive outside classroom | 2 | NULL |
| 18 | F | Eating/drinking in lesson | 1 | NULL |
| 19 | F | Incorrect uniform/equipment | 1 | NULL |
| 20 | F | Phone out in lesson | 3 | NULL |
| 21 | F | Aggressive defiance | 4 | NULL |
| 22 | F | Missed detention | 3 | NULL |
| 23 | F | Inappropriate behaviour/comments | 3 | NULL |
| 32 | F | IT Misuse | NULL | NULL |
| 34 | F | Inappropriate attitude towards staff | NULL | NULL |
| 35 | F | Care & Guidance | NULL | NULL |
+----+------+--------------------------------------+--------+----------+
SELECT * FROM transactions;
+----------------+------------+----------+--------------+--------+-------------+
| Transaction_ID | Datetime | Giver_ID | Recipient_ID | Points | Category_ID |
+----------------+------------+----------+--------------+--------+-------------+
| 34 | 2011-09-07 | 35019 | 137608 | 2 | 1 |
| 35 | 2011-09-07 | 35019 | 139027 | 2 | 1 |
| 36 | 2011-09-07 | 35019 | 139041 | 2 | 1 |
| 37 | 2011-09-07 | 35019 | 139041 | 2 | 1 |
| 38 | 2011-09-07 | 35019 | 139041 | 2 | 1 |
| 39 | 2011-09-07 | 35019 | 139041 | 2 | 1 |
| 40 | 2011-09-07 | 35019 | 137434 | 2 | 1 |
| 41 | 2011-09-07 | 35019 | 137434 | 2 | 1 |
| 42 | 2011-09-07 | 35019 | 137434 | 2 | 1 |
| 43 | 2011-09-07 | 35019 | 137434 | 2 | 1 |
| 44 | 2011-09-07 | 35006 | 137434 | 2 | 1 |
| 45 | 2011-09-07 | 35006 | 90306 | 2 | 1 |
| 46 | 2011-09-07 | 35006 | 90306 | 2 | 1 |
| 47 | 2011-09-07 | 35006 | 90306 | 2 | 1 |
| 48 | 2011-09-07 | 35023 | 137608 | 5 | 2 |
| 49 | 2011-09-07 | 35023 | 139027 | 5 | 2 |
| 50 | 2011-09-07 | 35023 | 139564 | 5 | 2 |
| 51 | 2011-09-07 | 35023 | 139564 | 5 | 2 |
| 52 | 2011-09-07 | 35023 | 139564 | 5 | 2 |
| 53 | 2011-09-07 | 35023 | 137608 | 5 | 3 |
+----------------+------------+----------+--------------+--------+-------------+
SELECT id,UPN,Year_Group,Tutor_Group,SEN_Status,Flags FROM student;
+--------+---------------+------------+-------------+------------+--------+
| id | UPN | Year_Group | Tutor_Group | SEN_Status | Flags |
+--------+---------------+------------+-------------+------------+--------+
| 137608 | A929238400044 | 11 | 11VID | A | |
| 139027 | A929238401045 | 10 | 10KS | | |
| 139041 | A929238402017 | 10 | 10RJ | A | FSM |
| 139892 | A929238403018 | 9 | 9BW | | |
| 139938 | A929238403020 | 9 | 9RH | | |
| 137434 | A929238500027 | 11 | 11VID | | |
| 138832 | A929238502002 | 10 | 10RY | A | FSM,PA |
| 34533 | A929238599028 | 0 | | | PA |
| 139564 | A929241500025 | 12 | | | PA |
| 90306 | A929253100006 | 12 | SLH | A | PA |
+--------+---------------+------------+-------------+------------+--------+页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/22326433
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号