mysql - 如何使用 GROUP_CONCAT 在 GROUP BY 之后加入？

Question

我有两个表

CREATE TABLE texts
(
TextID int(11) unsigned,
SentenceID int(11) unsigned,
Phrase tinyint(3) unsigned,
Word varchar(255),
PRIMARY KEY(TextID,SentenceID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci

CREATE TABLE phrases
(
PhraseID int(11) unsigned NOT NULL AUTO_INCREMENT,
Phrase varchar(255),
PRIMARY KEY(PhraseID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci

我通过 GROUP BY 从文本表中获取短语，

SELECT TRIM(GROUP_CONCAT(Word SEPARATOR ' '))  AS GroupedPhrase
FROM texts 
WHERE Phrase IS NOT NULL GROUP BY TextID,SentenceID,Phrase

但我需要JOIN 两个表来获取PhraseID 而不是Phrase，执行此操作的查询:

SELECT a.TextID,a.SentenceID,b.PhraseID 
FROM texts a JOIN phrases b ON TRIM(GROUP_CONCAT(a.Word SEPARATOR ' '))=b.Phrase 
WHERE a.Phrase IS NOT NULL GROUP BY a.TextID,a.SentenceID,a.Phrase

Answer 1

使用子查询:

SELECT *
FROM (SELECT TextID,SentenceID,TRIM(GROUP_CONCAT(Word SEPARATOR ' '))  AS GroupedPhrase
     FROM texts 
     WHERE Phrase IS NOT NULL 
     GROUP BY TextID,SentenceID,Phrase) sub
JOIN phrases b ON sub.GroupedPhrase=b.Phrase