Mysql
使用具有多個子查詢的加權平均值對行進行排名
所以我想做的是根據學生的加權平均數對學生進行排名，這將通過根據類型（無論是“考試”還是“非考試）分離成績來計算。然後將兩者的平均值加在一起給出將用於對學生進行排名的總數**。**
我使用了下面的查詢，但它沒有使用正確的平均值，因為它沒有正確分組類型。例如..它應該獲得所有非考試成績的平均值，它還包括考試成績：
SELECT * FROM (SELECT student_id, avg, exam,(exam) + (avg) as total, (@rn := @rn + 1) AS rank FROM (SELECT student_id, ((SELECT AVG(grade) where grades.type != 'exam')/100 * 40 ) as avg, ((SELECT AVG(grade) where type = 'exam')/100 * 60 ) as exam FROM grades WHERE class_id = 1 AND subject_id = 1 GROUP BY student_id ) agg CROSS JOIN (SELECT @rn := 0) CONST ORDER BY total desc) t請參閱下表的螢幕截圖：
請參閱下面的查詢以創建表：
表的表結構
gradesCREATE TABLE `grades` ( `grade_id` int(11) NOT NULL, `assessment_id` int(11) NOT NULL, `subject_id` int(11) NOT NULL, `student_id` int(11) NOT NULL, `class_id` int(11) NOT NULL, `grade` double NOT NULL, `teacher_comment` text, `type` enum('Home Work','Class','Unit Test','Assignment','Exam') DEFAULT NULL, `term` enum('1','2','3') DEFAULT NULL, `sch_year` year(4) NOT NULL, `entered_by` int(11) DEFAULT NULL, `enter_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `grades` -- INSERT INTO `grades` (`grade_id`, `assessment_id`, `subject_id`, `student_id`, `class_id`, `grade`, `teacher_comment`, `type`, `term`, `sch_year`, `entered_by`, `enter_date`) VALUES (1, 1, 3, 10001, 2, 88, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:04:38'), (5, 1, 3, 10001, 2, 80, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:12:17'), (6, 1, 3, 10002, 2, 60, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:12:17'), (7, 1, 3, 10001, 2, 80, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:13:50'), (8, 1, 3, 10002, 2, 75, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:13:50'), (9, 1, 3, 10003, 2, 95, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:13:50'), (10, 1, 3, 10001, 2, 85, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:22:53'), (11, 1, 3, 10002, 2, 88, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:22:53'), (12, 1, 3, 10003, 2, 90, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:22:54'), (13, 1, 3, 10001, 2, 98, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:23:44'), (14, 1, 3, 10002, 2, 95, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:23:44'), (15, 1, 3, 10003, 2, 90, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:23:44'), (16, 1, 3, 10001, 2, 96, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:24:42'), (17, 1, 3, 10002, 2, 88, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:24:42'), (18, 1, 3, 10003, 2, 86, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:24:42'), (22, 3, 4, 10001, 2, 55, NULL, 'Class', '3', 2015, NULL, '2016-06-21 02:27:47'), (23, 3, 4, 10002, 2, 75, NULL, 'Class', '3', 2015, NULL, '2016-06-21 02:27:47'), (24, 3, 4, 10003, 2, 85, NULL, 'Class', '3', 2015, NULL, '2016-06-21 02:27:47'), (25, 4, 3, 10001, 2, 85, NULL, 'Unit Test', '3', 2015, NULL, '2016-06-21 17:41:05'), (26, 4, 3, 10002, 2, 90, 'Test', 'Unit Test', '3', 2015, NULL, '2016-06-21 17:41:05'), (27, 4, 3, 10003, 2, 65, NULL, 'Unit Test', '3', 2015, NULL, '2016-06-21 17:41:05'), (28, 5, 3, 10001, 2, 96, NULL, 'Assignment', '3', 2015, NULL, '2016-06-22 19:10:34'), (29, 5, 3, 10002, 2, 90, 'Test', 'Assignment', '3', 2015, NULL, '2016-06-22 19:10:34'), (30, 5, 3, 10003, 2, 98, NULL, 'Assignment', '3', 2015, NULL, '2016-06-22 19:10:34'), (31, 6, 2, 10001, 2, 55, NULL, 'Exam', '3', 2015, NULL, '2016-06-29 15:04:47'), (32, 6, 2, 10002, 2, 65, 'Test', 'Exam', '3', 2015, NULL, '2016-06-29 15:04:47'), (33, 6, 2, 10003, 2, 60, NULL, 'Exam', '3', 2015, NULL, '2016-06-29 15:04:47'), (34, 10, 3, 10001, 2, 95, NULL, 'Exam', '3', 2015, NULL, '2016-06-30 15:53:34'), (35, 10, 3, 10002, 2, 85, NULL, 'Exam', '3', 2015, NULL, '2016-06-30 15:53:34'), (36, 10, 3, 10003, 2, 90, NULL, 'Exam', '3', 2015, NULL, '2016-06-30 15:53:34'), (37, 9, 1, 10001, 2, 95, NULL, 'Exam', '3', 2015, NULL, '2016-06-30 15:55:20'), (38, 9, 1, 10002, 2, 90, NULL, 'Exam', '3', 2015, NULL, '2016-06-30 15:55:20'), (39, 9, 1, 10003, 2, 93, NULL, 'Exam', '3', 2015, NULL, '2016-06-30 15:55:20'), (40, 12, 2, 10001, 2, 95, NULL, 'Exam', '3', 2015, NULL, '2016-07-11 15:56:34'), (41, 12, 2, 10002, 2, 96, NULL, 'Exam', '3', 2015, NULL, '2016-07-11 15:56:34'), (42, 12, 2, 10003, 2, 90, NULL, 'Exam', '3', 2015, NULL, '2016-07-11 15:56:34'), (43, 11, 3, 10001, 2, 85, NULL, 'Exam', '3', 2015, NULL, '2016-07-11 16:00:22'), (44, 11, 3, 10002, 2, 88, NULL, 'Exam', '3', 2015, NULL, '2016-07-11 16:00:22'), (45, 11, 3, 10003, 2, 92, NULL, 'Exam', '3', 2015, NULL, '2016-07-11 16:00:22'), (46, 13, 4, 10001, 2, 85, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:53'), (47, 13, 4, 10002, 2, 96, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:53'), (48, 13, 4, 10003, 2, 52, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:53'), (49, 13, 4, 10004, 2, 85, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:53'), (50, 13, 4, 10005, 2, 74, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:53'), (51, 13, 4, 10006, 2, 96, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:53'), (52, 13, 4, 10007, 2, 85, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:53'), (53, 13, 4, 10008, 2, 74, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'), (54, 13, 4, 10009, 2, 58, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'), (55, 13, 4, 10010, 2, 97, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'), (56, 13, 4, 10011, 2, 88, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'), (57, 13, 4, 10024, 2, 96, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'), (58, 13, 4, 10012, 2, 88, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'), (59, 13, 4, 10013, 2, 89, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'), (60, 13, 4, 10014, 2, 54, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'), (61, 13, 4, 10015, 2, 75, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'), (62, 13, 4, 10016, 2, 86, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'), (63, 13, 4, 10017, 2, 57, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'), (64, 13, 4, 10018, 2, 89, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'), (65, 13, 4, 10019, 2, 93, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'), (66, 13, 4, 10020, 2, 99, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'), (67, 13, 4, 10021, 2, 72, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'), (68, 13, 4, 10022, 2, 100, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'), (69, 13, 4, 10023, 2, 85, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'), (70, 13, 4, 10025, 2, 95, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'), (71, 13, 4, 10026, 2, 75, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'), (72, 13, 4, 10027, 2, 75, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'), (73, 14, 5, 10001, 2, 85, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'), (74, 14, 5, 10002, 2, 53, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'), (75, 14, 5, 10003, 2, 21, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'), (76, 14, 5, 10004, 2, 88, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'), (77, 14, 5, 10005, 2, 96, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'), (78, 14, 5, 10006, 2, 55, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'), (79, 14, 5, 10007, 2, 75, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'), (80, 14, 5, 10008, 2, 89, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'), (81, 14, 5, 10009, 2, 93, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'), (82, 14, 5, 10010, 2, 99, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'), (83, 14, 5, 10011, 2, 57, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'), (84, 14, 5, 10024, 2, 88, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'), (85, 14, 5, 10012, 2, 75, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'), (86, 14, 5, 10013, 2, 80, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'), (87, 14, 5, 10014, 2, 97, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'), (88, 14, 5, 10015, 2, 95, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'), (89, 14, 5, 10016, 2, 45, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'), (90, 14, 5, 10017, 2, 77, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'), (91, 14, 5, 10018, 2, 88, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'), (92, 14, 5, 10019, 2, 99, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'), (93, 14, 5, 10020, 2, 87, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'), (94, 14, 5, 10021, 2, 57, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'), (95, 14, 5, 10022, 2, 85, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:48'), (96, 14, 5, 10023, 2, 75, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:48'), (97, 14, 5, 10025, 2, 82, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:48'), (98, 14, 5, 10026, 2, 78, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:48'), (99, 14, 5, 10027, 2, 99, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:48'), (100, 15, 5, 10001, 2, 86, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'), (101, 15, 5, 10002, 2, 90, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'), (102, 15, 5, 10003, 2, 99, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'), (103, 15, 5, 10004, 2, 55, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'), (104, 15, 5, 10005, 2, 85, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'), (105, 15, 5, 10006, 2, 87, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'), (106, 15, 5, 10007, 2, 86, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'), (107, 15, 5, 10008, 2, 58, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'), (108, 15, 5, 10009, 2, 98, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'), (109, 15, 5, 10010, 2, 75, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'), (110, 15, 5, 10011, 2, 77, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'), (111, 15, 5, 10024, 2, 88, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'), (112, 15, 5, 10012, 2, 86, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'), (113, 15, 5, 10013, 2, 88, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'), (114, 15, 5, 10014, 2, 79, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'), (115, 15, 5, 10015, 2, 98, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'), (116, 15, 5, 10016, 2, 83, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'), (117, 15, 5, 10017, 2, 80, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'), (118, 15, 5, 10018, 2, 84, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'), (119, 15, 5, 10019, 2, 86, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'), (120, 15, 5, 10020, 2, 89, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'), (121, 15, 5, 10021, 2, 78, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'), (122, 15, 5, 10022, 2, 89, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'), (123, 15, 5, 10023, 2, 87, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'), (124, 15, 5, 10025, 2, 88, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'), (125, 15, 5, 10026, 2, 99, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'), (126, 15, 5, 10027, 2, 95, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'), (127, 16, 1, 10028, 1, 85, NULL, 'Home Work', '1', 0000, NULL, '2016-07-15 23:36:33'), (128, 16, 1, 10029, 1, 88, NULL, 'Home Work', '1', 0000, NULL, '2016-07-15 23:36:33'), (129, 16, 1, 10030, 1, 89, NULL, 'Home Work', '1', 0000, NULL, '2016-07-15 23:36:33'), (130, 17, 1, 10028, 1, 90, NULL, 'Class', '1', 0000, NULL, '2016-07-16 04:19:18'), (131, 17, 1, 10029, 1, 95, NULL, 'Class', '1', 0000, NULL, '2016-07-16 04:19:18'), (132, 17, 1, 10030, 1, 96, NULL, 'Class', '1', 0000, NULL, '2016-07-16 04:19:18'); -- -- Indexes for dumped tables -- -- -- Indexes for table `grades` -- ALTER TABLE `grades` ADD PRIMARY KEY (`grade_id`), ADD KEY `subject_id` (`subject_id`), ADD KEY `student_id` (`student_id`), ADD KEY `class_id` (`class_id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `grades` -- ALTER TABLE `grades` MODIFY `grade_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=133; -- -- Constraints for dumped tables -- -- -- Constraints for table `grades` -- ALTER TABLE `grades` ADD CONSTRAINT `grades_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`) ON UPDATE CASCADE; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
您發布的查詢實際上正在執行而沒有語法錯誤？
您需要像這樣的 CASE 的平均值：
SELECT * FROM ( SELECT student_id, avg, exam,(exam) + (avg) as total, (@rn := @rn + 1) AS rank FROM ( SELECT student_id, AVG(CASE WHEN type <> 'exam' THEN grade END) / 100 * 40 as avg, AVG(CASE WHEN type = 'exam' THEN grade END) / 100 * 60 as exam FROM grades WHERE class_id = 1 AND subject_id = 1 GROUP BY student_id ) agg CROSS JOIN (SELECT @rn := 0) CONST ORDER BY total desc ) t
這是問題的解決方案：
SELECT * FROM(
SELECT student_id, avg, exam,(exam) + (avg) as total, (@rn := @rn + 1) AS rank FROM (SELECT student_id, ( SELECT AVG(g.grade) / 100 * 40 FROM grades AS g WHERE g.type != 'Exam' AND g.student_id = grades.student_id ) as avg, ( SELECT AVG(s.grade)/100 * 60 FROM grades AS s WHERE s.type = 'Exam' AND s.student_id = grades.student_id ) as exam FROM grades WHERE class_id = 2 AND subject_id = 1 GROUP BY student_id ) agg CROSS JOIN (SELECT @rn := 0) CONST ORDER BY total desc) 噸