[MySQL]查询学生选课的情况(一)

这是我工作遇到的问题,现在自己设计一个简化的类似场景,现实中这样的数据表设计可能有很多不合理的地方。 首先看表结构:

+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id     | varchar(38)  | NO   | PRI | NULL    |       |
| name   | varchar(255) | YES  |     | NULL    |       |
| course | varchar(300) | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+

这里只是记录学生的ID,名字,还有选课的科目,科目有很多,在没有关联表的情况下,这么多科目只保存在一个字段中,用逗号隔开。 再看一些数据:

+--------------------------------------+--------+--------------------------------+
| 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          |
| 656a247a-f33d-11e4-a31d-089e0140e076 | 曹达华 | Chesses                        |
+--------------------------------------+--------+--------------------------------+

那么如何查找到选择了Math课程的学生?

想想使用关联表的时候,张三, 李四, 李五, 王小明这四个人都一条选择了Math这门课的记录,还有其他不是Math的记录。此时要查找选择了Math课程的学生,一般使用IN语句就可以了:

select * from student_course where course IN ('Math');

如果要查找选择了MathAlgorithm课程的学生呢:

select * from student_course where course IN ('Math', 'Algorithm');

如此,回到原来的问题,如果我设计一个类似IN一样的函数,那么就可以解决这个问题了。 这个流程我们可以想象出来,是这样子的: 我们取张三的课程信息Math,English,Chinese,首先切割成Math, English,Chinese三个字段,然后分别与与查找条件做比较,类似'Math'.indexOf('Math');,'Math'.indexOf('English');... 只要找到一个就认为符合查找条件。 同样的,如果要查找选择了MathAlgorithm课程的学生,比较过程就变成了: 'Math,Algorithm'.indexOf('Math');,'Math,Algorithm'.indexOf('English');...

切割函数 getSplitTotalLength, getSplitString

CREATE DEFINER = `root`@`%` FUNCTION `getSplitTotalLength`(`f_string` varchar(500),`f_delimiter` varchar(5))
 RETURNS int(11)
BEGIN
    # 计算传入字符串能切分成多少段 
   return 1+(length(f_string) - length(replace(f_string,f_delimiter,''))); 

    RETURN 0;
END;
CREATE DEFINER = `root`@`%` FUNCTION `getSplitString`(`f_string` varchar(500),`f_delimiter` varchar(5),`f_order` int)
 RETURNS varchar(500)
BEGIN
    #拆分传入的字符串,分隔符,顺序,返回拆分所得的新字符串
    declare result varchar(500) default '';
    set result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_order)),f_delimiter,1));
    RETURN result;
END;

类似IN的那个函数 isInSearch

CREATE DEFINER=`root`@`%` FUNCTION `isInSearch`(f_course VARCHAR(300), f_string VARCHAR(300)) RETURNS INT
BEGIN

    DECLARE len INT DEFAULT 0;
    DECLARE idx INT DEFAULT 0;
    DECLARE item_code VARCHAR(300) DEFAULT '';
    DECLARE item_index INT DEFAULT 0;
    IF f_course IS NULL THEN
        RETURN 0;
    END IF;
    SELECT getSplitTotalLength(f_course, ',') INTO len;
    label: LOOP
        SET idx = idx + 1;
        IF idx > len THEN
            LEAVE label;
        END IF;
        SELECT getSplitString(f_course , ',', idx) INTO item_code;
        # f_string.indexOf(item_code) > -1 ?
        SELECT LOCATE(item_code, f_string) INTO item_index;
        IF item_index > 0 THEN
            RETURN 1; # got one
        END IF;
    END LOOP label;
    RETURN 0;
    END;

这里说下locate函数,locate(item_code, f_string),如果item_codef_string的子串,返回的结果大于0,是item_codef_string的起始下标(从1开始算起),这个一般的indexOf函数有些不同。

mysql> select locate('Math','Math,Algorithm');
+---------------------------------+
| locate('Math','Math,Algorithm') |
+---------------------------------+
|                               1 |
+---------------------------------+
mysql> select locate('Math','Chinese,Math,Algorithm');
+-----------------------------------------+
| locate('Math','Chinese,Math,Algorithm') |
+-----------------------------------------+
|                                       9 |
+-----------------------------------------+
mysql> select locate('Math','Chinese,Algorithm');
+------------------------------------+
| locate('Math','Chinese,Algorithm') |
+------------------------------------+
|                                  0 |
+------------------------------------+

可以看到isInSearch函数返回的是INT类似,因为MySQLIN也是这样的机制。

mysql> select 'Math' in ('Math','Algorightm');
+---------------------------------+
| 'Math' in ('Math','Algorightm') |
+---------------------------------+
|                               1 |
+---------------------------------+
mysql> select 'Math' in ('Chinese','Algorightm');
+------------------------------------+
| 'Math' in ('Chinese','Algorightm') |
+------------------------------------+
|                                  0 |
+------------------------------------+

如果存在返回1,不存在返回0。

在SELECT语句中使用自定义的函数

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          |
+--------------------------------------+--------+--------------------------------+

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏IT杂记

Mapreduce程序中reduce的Iterable参数迭代出是同一个对象

今天在对reduce的参数Iterable进行迭代时,发现一个问题,即Iterator的next()方法每次返回的是同一个对象,next()只是修改了Writa...

21050
来自专栏GreenLeaves

关于null的操作

空值     空值一般用NULL表示     一般表示未知的、不确定的值,也不是空格     一般运算符与其进行运算时,都会为空     空不与任何值相等   ...

22070
来自专栏码云1024

mysql 数据类型

37740
来自专栏Rgc

sqlalchemy和flask-sqlalchemy几种分页操作

sqlalchemy中使用query查询,而flask-sqlalchemy中使用basequery查询,他们是子类与父类的关系 假设 page_index=1...

42770
来自专栏十月梦想

mysql数据类型

mysql数据的数据类型,指定了字段的类型,不符合指定的字段类型,传入的值则会提示错误;

9840
来自专栏GreenLeaves

oracle 中关于null的操作

空值     空值一般用NULL表示     一般表示未知的、不确定的值,也不是空格     一般运算符与其进行运算时,都会为空     空不与任何值相等   ...

19680
来自专栏杨建荣的学习笔记

MySQL数据类型(r3笔记第87天)

今天在本地装了一个MySQL的学习环境,简单的熟悉了一下。准备开始好好学习MySQL了。 学习编程语言我都是从数据类型入手。每种编程语言的数据类型都有自己的特点...

301100
来自专栏峰会SaaS大佬云集

Oracle 数据库入门之----------------------单行函数

SQL> select lower('Hello World') 转小写,upper('Hello World') 转大写,initcap('hello wor...

8200
来自专栏Python

sqlalchemy和flask-sqlalchemy的几种分页方法

sqlalchemy中使用query查询,而flask-sqlalchemy中使用basequery查询,他们是子类与父类的关系 假设 page_index=1...

600100
来自专栏猿人谷

Mysql字符串截取总结:left()、right()、substring()、substring_index()

在实际的项目开发中有时会有对数据库某字段截取部分的需求,这种场景有时直接通过数据库操作来实现比通过代码实现要更方便快捷些,mysql有很多字符串函数可以用来处...

35300

扫码关注云+社区

领取腾讯云代金券