您可以拆分/分解 MySQL 查询中的字段吗?

2021-11-20 00:00:00 mysql

我必须创建一份关于一些学生完成情况的报告.每个学生都属于一个客户.这是表格(针对此问题进行了简化).

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 字段大.虽然它更小.
  • 涉及的字符串操作在高级语言代码(SQL 和 PHP)中很少，但与使用整数数组相比成本很高.
  • 数据库不应该是人类可读的，因为它们的可读性/直接可编辑性而试图坚持结构，这在很大程度上是愚蠢的，就像我所做的一样.

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

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

相关文章