如何编写SQL查询实现多表关联聚合，展示文章、作者与关键词？

AI 小北

2026-7-30

SQL查询：关联多表并拼接作者与关键词

现有表结构

1. Author表

AuthorID	FirstName	LastName
1	Alex	Popov
2	Serg	Smit
3	John	Paulse

2. NameStatie表

NameStatieID	NameStatie	URLStatie
1	POPULATION OF TRNA-DERIVED	/item001
2	AGENT-BASED SIMULATION MODEL	/item002
3	CHAGAS' DISEASE DIAGNOSIS:	/item003

3. KeyWord表

KeyWordID	NameKeyWord
1	VIRTUAL LEARNING ENVIRONMENT
2	EDUCATIONAL OBJECTIVES
3	ACADEMIC PROGRESS EVALUATION

4. Statie表（关联表）

StatieID	NameStatieID	AuthorID
1	1	1
2	1	3
3	2	2

5. NameStatieAndKeyWord表（中间表，避免冗余）

NameStatieAndKeyWordID	NameStatieID	KeyWordID
1	1	1
2	1	3
3	2	2

需求

需要编写SQL查询，得到如下结果：

NameStatie	KeyWords	Author
POPULATION OF TRNA-DERIVED	VIRTUAL LEARNING ENVIRONMENT, ACADEMIC PROGRESS EVALUATION	Alex Popov, John Paulse

已有可拼接作者的SQL语句，但无法加入关键词拼接，需要完善查询。

解决方案

通过关联中间表NameStatieAndKeyWord和KeyWord表，用GROUP_CONCAT拼接关键词，同时保留原有作者拼接逻辑。使用LEFT JOIN避免丢失无关键词/作者的条目，最终按NameStatieID分组：

SELECT 
    ns.NameStatie,
    GROUP_CONCAT(DISTINCT kw.NameKeyWord ORDER BY kw.NameKeyWord SEPARATOR ', ') AS KeyWords,
    GROUP_CONCAT(DISTINCT a.LastName || ' ' || a.FirstName ORDER BY a.LastName SEPARATOR ', ') AS Author
FROM NameStatie ns
LEFT JOIN Statie s ON ns.NameStatieID = s.NameStatieID
LEFT JOIN Author a ON s.AuthorID = a.AuthorID
LEFT JOIN NameStatieAndKeyWord nskw ON ns.NameStatieID = nskw.NameStatieID
LEFT JOIN KeyWord kw ON nskw.KeyWordID = kw.KeyWordID
GROUP BY ns.NameStatieID, ns.NameStatie;