問題描述
我必須創建一份關于一些學生完成情況的報告.每個學生都屬于一個客戶.這是表格(針對此問題進行了簡化).
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.
這篇關于您可以拆分/分解 MySQL 查詢中的字段嗎?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！