如何用单条SQL查询获取学生及其关联好友列表？

正确的SQL查询语句 #

首先，你的需求是获取所有学生及其对应的好友列表，这意味着需要包含那些没有好友的学生。你当前用的JOIN是内连接（INNER JOIN），它只会返回有匹配好友记录的学生，不符合需求。应该改用左连接（LEFT JOIN），并且不需要额外的WHERE子句（除非你有特定的过滤条件，比如只查询某个范围的学生）。

场景1：每个学生一行，好友列表合并为字符串 #

如果希望每个学生的好友列表合并成一个易读的字段（比如用逗号分隔），可以用GROUP_CONCAT函数：

SELECT 
    s.ID AS student_id,
    s.NAME AS student_name,
    GROUP_CONCAT(friend.NAME SEPARATOR ', ') AS friend_list
FROM STUDENT s
LEFT JOIN FRIEND f ON s.ID = f.ID_1
LEFT JOIN STUDENT friend ON f.ID_2 = friend.ID
GROUP BY s.ID, s.NAME

没有好友的学生，friend_list会显示为NULL，你也可以用COALESCE(friend_list, '无好友')替换成更友好的提示文字。

场景2：每个学生的每条好友记录单独一行 #

如果不需要合并列表，想保留每条好友关系的原始行结构，用以下语句：

SELECT 
    s.ID AS student_id,
    s.NAME AS student_name,
    friend.ID AS friend_id,
    friend.NAME AS friend_name
FROM STUDENT s
LEFT JOIN FRIEND f ON s.ID = f.ID_1
LEFT JOIN STUDENT friend ON f.ID_2 = friend.ID

没有好友的学生，对应的friend_id和friend_name字段会是NULL。

性能优化要点 #

1. 添加必要索引

   • 给FRIEND表的ID_1字段创建索引：CREATE INDEX idx_friend_id1 ON FRIEND(ID_1);，这会大幅加快STUDENT表与FRIEND表的关联速度。
   • 给FRIEND表的ID_2字段创建索引：CREATE INDEX idx_friend_id2 ON FRIEND(ID_2);，用于快速通过好友ID查询对应学生的信息。
   • 确保STUDENT表的ID是主键（默认自带主键索引），这是关联查询的性能基础。

2. 避免不必要的字段查询
   不要用SELECT *，明确写出需要的字段（比如上面例子中的student_id、student_name等），减少数据传输和数据库的处理开销。

3. 处理大数据量下的GROUP_CONCAT
   如果学生的好友数量很多，GROUP_CONCAT的结果可能会超过数据库默认长度限制，可以通过调整参数group_concat_max_len扩大上限，但要注意这会占用更多内存，需根据实际数据量配置。

4. 过滤场景下的WHERE子句
   如果需要添加过滤条件（比如只查询ID大于100的学生），直接在WHERE子句中添加即可，例如：WHERE s.ID > 100，索引会帮助优化过滤效率。

内容的提问来源于stack exchange，提问作者student.321 university