mysql 搜索逗号分隔字段的SQL查询

vqlkdk9b 于 2024-01-05 发布在 Mysql

关注(0)|答案(4)|浏览(143)

我有一个学生表，看起来像这样：

id  |  name  |  school_descriptors
-------------------------------------------------------
 1   |  Rob   |  Comp Sci,Undergraduate,2020  
 2   |  Tim   |  Business,MBA,2022
 3   |  Matt  |  Business,MBA,2022
 4   |  Jack  |  Law,Masters,2024
 5   |  Steph |  Comp Sci,Masters,2022

字符串
school_descriptors字段只是一个列，以逗号分隔的字符串存储有关课程，资格和毕业年份的信息。（它的设计非常糟糕，我希望它可以被分割成自己的字段，但现在不能（我不是数据库所有者））
我想提供一个界面，教师可以快速找到符合某些课程，资格和毕业年份的学生，从而希望创建相关查询。

**问题1：例如，我希望教师能够从UI中选择：“Business”，“MBA”并获得ID为2和3的返回学生。具体来说，我的示例问题是： 查找正在学习Business课程并正在进行MBA资格认证的学生：

SELECT * FROM student_table WHERE school_descriptors LIKE '%Business%' AND school_descriptors LIKE '%MBA%'

型
我想到的查询是一个基本的LIKE查询，但我不禁想到有一个更有效的查询，它可以利用school_descriptor字符串1）总是以特定顺序的事实（例如，课程、资格、毕业），以及2）逗号分隔，并且因此可能被拆分。

相关问题2：* 查找2019年后毕业的Comp Sci课程学生：*

是否可以拆分school_descriptors字段并添加一个>2019操作数？
多谢了！

mysql

来源：https://stackoverflow.com/questions/64069141/sql-query-that-searches-comma-delimited-field

4条答案

按热度按时间

clj7thdc1#

在MySQL中，你可以使用函数SUBSTRING_INDEX()来拆分列school_descriptors。
只有在课程、资格和毕业年份的位置固定的情况下，这才有效。

select *,
  substring_index(school_descriptors, ',', 1) Course, 
  substring_index(substring_index(school_descriptors, ',', 2), ',', -1) Qualification, 
  substring_index(school_descriptors, ',', -1) Graduation
from student_table

字符串
参见demo。
结果如下：

> id | name  | school_descriptors          | Course   | Qualification | Graduation
> -: | :---- | :-------------------------- | :------- | :------------ | :---------
>  1 | Rob   | Comp Sci,Undergraduate,2020 | Comp Sci | Undergraduate | 2020      
>  2 | Tim   | Business,MBA,2022           | Business | MBA           | 2022      
>  3 | Matt  | Business,MBA,2022           | Business | MBA           | 2022      
>  4 | Jack  | Law,Masters,2024            | Law      | Masters       | 2024      
>  5 | Steph | Comp Sci,Masters,2022       | Comp Sci | Masters       | 2022

型

赞(0）回复(0）举报 2024-01-05

xqkwcwgp2#

select id, name, 
  substring_index(school_descriptors,',',1) as course, 
  substring_index(substring(school_descriptors,length(substring_index(school_descriptors,',',1))+2,200),',',1) as Qualifications, 
  substring_index(school_descriptors,',',-1) as year
from student;

字符串
产出：

+------+-------+----------+----------------+------+
| id   | name  | course   | Qualifications | year |
+------+-------+----------+----------------+------+
|    1 | Rob   | Comp Sci | Undergraduate  | 2020 |
|    2 | Tim   | Business | MBA            | 2022 |
|    3 | Matt  | Business | MBA            | 2022 |
|    4 | Jack  | Law      | Masters        | 2024 |
|    5 | Steph | Comp Sci | Masters        | 2022 |
+------+-------+----------+----------------+------+

型
如果你想了解SUBSTRING_INDEX（），可以使用文档的链接。

赞(0）回复(0）举报 2024-01-05

polkgigr3#

回答1：

SELECT * FROM student_table WHERE school_descriptors REGEXP ['Business','MBA']

字符串
通过使用此查询，您可以获得所有具有Business或MBA的记录。如果您只想选择Business，MBA，您可以这样尝试
SELECT * FROM student_table WHERE school_descriptors LIKE '%Business，MBA%'

回答二：

SELECT * 
FROM student 
WHERE
  SUBSTRING_INDEX(SUBSTRING_INDEX(school_descriptors , ',', 1), ',', -1)='Comp Sci' 
  AND
  SUBSTRING_INDEX(SUBSTRING_INDEX(school_descriptors , ',', 3), ',', -1)> 2019;

型

赞(0）回复(0）举报 2024-01-05

4zcjmb1e4#

LIKE运算符不是最佳选择。在某些情况下，LIKE运算符会让我们失败。例如，如果您有一个Master Business in School描述符，LIKE运算符列也会拉取该记录。要找到精确的匹配，您需要使用RLIKE

SELECT * FROM student_table WHERE school_descriptors RLIKE '[[:<:]]'Business'[[:>:]]' AND school_descriptors RLIKE '[[:<:]]'MBA'[[:>:]]'

字符串

赞(0）回复(0）举报 2024-01-05

我来回答

mysql 搜索逗号分隔字段的SQL查询

4条答案

相关问题

热门标签

最新问答