mysql - mysql存储过程中的多选择语句

Question

我在 dbpre_exer5 中得到了这些表；//mysql wampserver2.2

+-----------------------+
| Tables_in_dbpre_exer5 |
+-----------------------+
| tblcourse             |
| tblcutoff             |
| tblgrades             |
| tblstud               |
| tblsub                |
+-----------------------+

现在，我只想问考虑到存储过程的使用，我将如何获得这些输出。输出是:

+---------+----------------+---------------+-------+
| Gender  |  With Failure  | W/out Failure | Total |
+---------+----------------+---------------+-------+
| Male    |       1        |       1       |   2   |
| Female  |       2        |       1       |   3   |
+---------+----------------+---------------+-------+

顺便说一下，这里是在此处输入代码；**

mysql> select * from tblcourse;
+-----------+------------------------+
| course_id | course_name            |
+-----------+------------------------+
|         1 | Information Technology |
|         2 | Computer Science       |
+-----------+------------------------+
2 rows in set (0.00 sec)

mysql> select * from tblgrades;
+---------+--------+-------+
| stud_id | sub_id | grade |
+---------+--------+-------+
|       1 |      1 |    80 |
|       1 |      2 |    78 |
|       2 |      2 |    75 |
|       2 |      3 |    84 |
|       3 |      1 |    81 |
|       3 |      3 |    90 |
|       4 |      1 |    74 |
|       4 |      2 |    77 |
|       5 |      2 |    76 |
|       5 |      3 |    81 |
+---------+--------+-------+
10 rows in set (0.00 sec)

mysql> select * from tblcourse;
+-----------+------------------------+
| course_id | course_name            |
+-----------+------------------------+
|         1 | Information Technology |
|         2 | Computer Science       |
+-----------+------------------------+
2 rows in set (0.00 sec)

mysql> select * from tblcutoff;
+-----------+
| passgrade |
+-----------+
|        78 |
+-----------+
1 row in set (0.00 sec)

mysql> select * from tblgrades;
+---------+--------+-------+
| stud_id | sub_id | grade |
+---------+--------+-------+
|       1 |      1 |    80 |
|       1 |      2 |    78 |
|       2 |      2 |    75 |
|       2 |      3 |    84 |
|       3 |      1 |    81 |
|       3 |      3 |    90 |
|       4 |      1 |    74 |
|       4 |      2 |    77 |
|       5 |      2 |    76 |
|       5 |      3 |    81 |
+---------+--------+-------+
10 rows in set (0.00 sec)

mysql> select * from tblstud;
+---------+-------------------+--------+-----------+
| stud_id | stud_name         | gender | course_id |
+---------+-------------------+--------+-----------+
|       1 | Angelina Jolie    | F      |         1 |
|       2 | Jennifer Garner   | F      |         1 |
|       3 | Liam Neeson       | M      |         2 |
|       4 | Paul Walker       | M      |         2 |
|       5 | Jennifer Lawrence | F      |         2 |
+---------+-------------------+--------+-----------+
5 rows in set (0.00 sec)

mysql> select * from tblsub;
+--------+------------+
| sub_id | sub_name   |
+--------+------------+
|      1 | Math 1     |
|      2 | English 1  |
|      3 | Filipino 1 |
+--------+------------+
3 rows in set (0.00 sec)

mysql>

**

我的第一个问题是在性别下获得“男性”和“女性”结果。有什么帮助吗？非常感谢。

Answer 1

已更新

SELECT gender, 
       SUM(failure) `With failure`,
       COUNT(*) - SUM(failure) `Without failure`,
       COUNT(*) total
  FROM
(
  SELECT s.stud_id, 
         CASE WHEN s.gender = 'M' THEN 'Male' ELSE 'Female' END gender,
         MAX(CASE WHEN g.grade <  c.passgrade THEN 1 ELSE 0 END) failure
    FROM tblgrades g JOIN tblstud s 
      ON g.stud_id = s.stud_id CROSS JOIN tblcutoff c
   GROUP BY s.stud_id
) q
 GROUP BY gender

示例输出:

| GENDER | WITH FAILURE | WITHOUT FAILURE | TOTAL |---------------------------------------------------| Female |            2 |               1 |     3 ||   Male |            1 |               1 |     2 |

这里是SQLFiddle 演示