[MySQL]查询学生选课的情况（二） - 快车库

上一篇文章使用了自定义的函数查找出来选择Math，或者选择Chinese或选择Japanese课程的学生。

mysql> select * from student_course where isInSearch(course, 'Math');
+--------------------------------------+--------+--------------------------------+
| id                                   | name   | course                         |
+--------------------------------------+--------+--------------------------------+
| 32268995-f33d-11e4-a31d-089e0140e076 | 张三   | Math,English,Chinese           |
| 3d670ef2-f33d-11e4-a31d-089e0140e076 | 李四   | Math,English,Chinese,Algorithm |
| 475d51a6-f33d-11e4-a31d-089e0140e076 | 李五   | Math,English,Algorithm         |
| 547fdea0-f33d-11e4-a31d-089e0140e076 | 王小明 | Math,English,Japanese          |
+--------------------------------------+--------+--------------------------------+

mysql> select * from student_course where isInSearch(course, 'Chinese,Japanese');
+--------------------------------------+--------+--------------------------------+
| id                                   | name   | course                         |
+--------------------------------------+--------+--------------------------------+
| 32268995-f33d-11e4-a31d-089e0140e076 | 张三   | Math,English,Chinese           |
| 3d670ef2-f33d-11e4-a31d-089e0140e076 | 李四   | Math,English,Chinese,Algorithm |
| 547fdea0-f33d-11e4-a31d-089e0140e076 | 王小明 | Math,English,Japanese          |
+--------------------------------------+--------+--------------------------------+

但是在中文情景下这样还是不够的，我们还必须将Math,English,Chinese转成中文的。为了解决这个问题，接下来会引入字段表，当然这一切都是因为最初的数据表设计不合理引起的。
这是简化了的字典表：

mysql> select * from stu_dict;
+--------------------------------------+-----------+------+
| id                                   | code      | text |
+--------------------------------------+-----------+------+
| 14baec33-f3f9-11e4-8563-089e0140e076 | Math      | 数学 |
| 14bd3e97-f3f9-11e4-8563-089e0140e076 | English   | 英语 |
| 51aebb7b-f3f9-11e4-8563-089e0140e076 | Chinese   | 汉语 |
| 51b4e637-f3f9-11e4-8563-089e0140e076 | Algorithm | 算法 |
| 51b4e82f-f3f9-11e4-8563-089e0140e076 | Japanese  | 日语 |
| 51b4e921-f3f9-11e4-8563-089e0140e076 | Chesses   | 象棋 |
+--------------------------------------+-----------+------+

为了将Math,English,Chinese转成数学,英语,汉语，首先必须将course字段切割，然后关联字典表查找出来对应的课程中文名，最后再拼接在一起返回。

这回我们还需要另外一个自定义函数。

DELIMITER $$

USE `test`$$

DROP FUNCTION IF EXISTS `getCourseNames`$$

CREATE DEFINER = `root`@`localhost` FUNCTION `getCourseNames`(f_string VARCHAR(255)) RETURNS CHAR(255)
BEGIN
    DECLARE len INT DEFAULT 0;
    DECLARE idx INT DEFAULT 0;
    DECLARE result VARCHAR(500) DEFAULT '';
    DECLARE item_code VARCHAR(300) DEFAULT '';
    DECLARE item_name VARCHAR(300) DEFAULT '';
    SELECT getSplitTotalLength(f_string, ',') INTO len;
    label: LOOP
        SET idx = idx + 1;
        IF idx > len THEN
            LEAVE label;
        END IF;
        SELECT getSplitString(f_string, ',', idx) INTO item_code;
        SELECT `text` INTO item_name from stu_dict where `code` = item_code;
        IF result = '' THEN
            SET result = item_name;
        ELSE 
            SET result = CONCAT(result, ',', item_name);
        END IF;
    END LOOP label;
    RETURN result;
END$$

DELIMITER ;

这个函数使用了上一篇设计的getSplitTotalLength和getSplitString，用于切割course，将得到的每门课item_code在字典表中查出得出课程中文名。

结果：

mysql> SELECT id, `name`, getCourseNames(course) FROM student_course;
+--------------------------------------+--------+------------------------+
| id                                   | name   | getCourseNames(course) |
+--------------------------------------+--------+------------------------+
| 32268995-f33d-11e4-a31d-089e0140e076 | 张三   | 数学,英语,汉语         |
| 3d670ef2-f33d-11e4-a31d-089e0140e076 | 李四   | 数学,英语,汉语,算法    |
| 475d51a6-f33d-11e4-a31d-089e0140e076 | 李五   | 数学,英语,算法         |
| 547fdea0-f33d-11e4-a31d-089e0140e076 | 王小明 | 数学,英语,日语         |
| 656a247a-f33d-11e4-a31d-089e0140e076 | 曹达华 | 象棋                   |
+--------------------------------------+--------+------------------------+