使用GROUP_CONCAT时，ORDER BY子句似乎工作正常。但是，使用ISNULL或COALESCE替换NULL值时，尽管实际值按顺序排列，NULL值都在串联的开头结束（见下文）。如何使我的NULL替换值按正确的列顺序排列，以便它们正确地表示被替换的值。

SELECT filledForm.creationDate AS date, GROUP_CONCAT(IFNULL(answers.answer, "NULL") ORDER BY answers.promptID) AS answers
FROM filledForm
JOIN prompts ON filledForm.formID = prompts.formID AND prompts.formID = 100
LEFT JOIN  answers ON prompts.promptID = answers.promptID AND filledForm.filledFormID = answers.filledFormID
GROUP BY filledForm.filledFormID;

使用一些样本数据，这给出：

+---------------------+-----------------------------------------------------------------+
| date                | answers                                                         |
+---------------------+-----------------------------------------------------------------+
| 2023-03-10 14:21:03 | NULL,NULL,NULL,NULL,NULL,bob,2023-03-16,English                 |
| 2023-03-11 02:28:23 | NULL,NULL,NULL,Johnny Depp,60,testy2@gwet.com,2023-03-24,German |
| 2023-03-11 02:32:41 | NULL,NULL,Tom,22,tom@testiclal.com,2023-03-29,11:00,English     |
+---------------------+-----------------------------------------------------------------+

而不是：

+---------------------+-----------------------------------------------------------------+
| date                | answers                                                         |
+---------------------+-----------------------------------------------------------------+
| 2023-03-10 14:21:03 | bob,NULL,NULL,2023-03-16,NULL,English,NULL,NULL                 |
| 2023-03-11 02:28:23 | Johnny Depp,60,testy2@gwet.com,2023-03-24,NULL,German,NULL,NULL |
| 2023-03-11 02:32:41 | Tom,22,tom@testiclal.com,2023-03-29,11:00,EnglishNULL,NULL      |
+---------------------+-----------------------------------------------------------------+

当我运行不带groupby部分的查询时可以看到：
一个三个三个一个
或者可能更容易理解，多几列显示：

SELECT filledForm.filledFormID, prompts.promptID, answers.answer AS answers
FROM filledForm
JOIN prompts ON filledForm.formID = prompts.formID AND prompts.formID = 100
LEFT JOIN  answers ON prompts.promptID = answers.promptID AND filledForm.filledFormID = answers.filledFormID
ORDER BY filledForm.filledFormID, prompts.promptID;

+--------------+----------+-------------------+
| filledFormID | promptID | answer            |
+--------------+----------+-------------------+
|           14 |        9 | bob               |
|           14 |       10 | NULL              |
|           14 |       11 | NULL              |
|           14 |       12 | 2023-03-16        |
|           14 |       13 | NULL              |
|           14 |       14 | English           |
|           14 |       15 | NULL              |
|           14 |       16 | NULL              |
|           15 |        9 | Johnny Depp       |
|           15 |       10 | 60                |
|           15 |       11 | testy2@gwet.com   |
|           15 |       12 | 2023-03-24        |
|           15 |       13 | NULL              |
|           15 |       14 | German            |
|           15 |       15 | NULL              |
|           15 |       16 | NULL              |
|           16 |        9 | Tom               |
|           16 |       10 | 22                |
|           16 |       11 | tom@testiclal.com |
|           16 |       12 | 2023-03-29        |
|           16 |       13 | 11:00             |
|           16 |       14 | English           |
|           16 |       15 | NULL              |
|           16 |       16 | NULL              |
+--------------+----------+-------------------+

以下是完整的表格：

CREATE TABLE prompts (
    promptID INT PRIMARY KEY AUTO_INCREMENT,
    formID INT NOT NULL,
    FOREIGN KEY (formID)
        REFERENCES form (formID)
        ON DELETE CASCADE,        
    hash VARCHAR(16) NOT NULL,
    prompt TEXT(1023) NOT NULL,
    changeDate TIMESTAMP
    UNIQUE(formID, hash)
);

CREATE TABLE filledForm (
    filledFormID INT PRIMARY KEY AUTO_INCREMENT,
    formID INT NOT NULL,
    FOREIGN KEY (formID)
        REFERENCES form (formID)
        ON DELETE CASCADE,
    creationDate TIMESTAMP NOT NULL DEFAULT (UTC_TIMESTAMP),
    INDEX (formID)
);

CREATE TABLE answers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    filledFormID INT NOT NULL, 
    FOREIGN KEY (filledFormID)
        REFERENCES filledForm (filledFormID)
        ON DELETE CASCADE,
    promptID INT NOT NULL,
    FOREIGN KEY (promptID)
        REFERENCES prompts (promptID)
        ON DELETE CASCADE,    
    answer TEXT(99999),
    INDEX (filledFormID)
);

SET SESSION group_concat_max_len = 1000000;

任何帮助都将不胜感激。