您可以拆分/分解MySQL查询中的字段吗? [英] Can you split/explode a field in a MySQL query?

问题描述

我必须创建一些学生学习成绩的报告.每个学生都属于一个客户.这是表格(此问题的简化版).

I have to create a report on some student completions. The students each belong to one client. Here are the tables (simplified for this question).

CREATE TABLE  `clients` (
  `clientId` int(10) unsigned NOT NULL auto_increment,
  `clientName` varchar(100) NOT NULL default '',
  `courseNames` varchar(255) NOT NULL default ''
)

courseNames字段包含用逗号分隔的课程名称字符串，例如"AB01，AB02，AB03"

The courseNames field holds a comma-delimited string of course names, eg "AB01,AB02,AB03"

CREATE TABLE  `clientenrols` (
  `clientEnrolId` int(10) unsigned NOT NULL auto_increment,
  `studentId` int(10) unsigned NOT NULL default '0',
  `courseId` tinyint(3) unsigned NOT NULL default '0'
)

此处的courseId字段是 clients.courseNames字段中课程名称的索引.因此，如果客户的courseNames是"AB01，AB02，AB03"，而注册的courseId是2，则该学生在AB03.

The courseId field here is the index of the course name in the clients.courseNames field. So, if the client's courseNames are "AB01,AB02,AB03", and the courseId of the enrolment is 2, then the student is in AB03.

有没有办法对这些包含课程名称的表格进行单选?请记住，会有来自不同客户的学生(因此有不同的课程名称，并非所有课程名称都是连续的，例如:"NW01，NW03")

Is there a way that I can do a single select on these tables that includes the course name? Keep in mind that there will be students from different clients (and hence have different course names, not all of which are sequential,eg: "NW01,NW03")

基本上，如果我可以拆分该字段并从结果数组中返回单个元素，那将是我想要的.这就是我在神奇的伪代码中的意思:

Basically, if I could split that field and return a single element from the resulting array, that would be what I'm looking for. Here's what I mean in magical pseudocode:

SELECT e.`studentId`, SPLIT(",", c.`courseNames`)[e.`courseId`]
FROM ...

推荐答案

直到现在，我还想将那些逗号分隔的列表保留在我的SQL数据库中-充分了解所有警告！

Until now, I wanted to keep those comma separated lists in my SQL db - well aware of all warnings!

我一直认为它们比查找表有好处(查找表为标准化数据库提供了一种方法).经过几天的拒绝，我见过光了:

I kept thinking that they have benefits over lookup tables (which provide a way to a normalized data base). After some days of refusing, I've seen the light:

• 在一个字段中使用逗号分隔的值时，使用查找表不会比丑陋的字符串操作引起更多的代码.
• 查找表允许使用本机数字格式，因此不能大于这些csv字段.不过是SMALLER.
• 所涉及的字符串操作在高级语言代码(SQL和PHP)中比较薄，但是与使用整数数组相比，代价昂贵.
• 数据库并不是人类可读的，并且像我一样，试图坚持使用结构是愚蠢的，因为它们具有可读性/直接可编辑性.

简而言之，MySQL中没有本机SPLIT()函数是有原因的.

In short, there is a reason why there is no native SPLIT() function in MySQL.

这篇关于您可以拆分/分解MySQL查询中的字段吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！