SQL查询所有队伍数据，以及队伍关联的成员和队长

本文介绍了如何使用SQL查询获取所有队伍的数据，并同时关联成员和队长信息。通过多表联查和GROUP_CONCAT函数，实现了一次性获取完整队伍结构的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

效果如下：

队伍名称	队长	成员
A战队	李四A	张三A，李四A，王五A
B战队	张三B	张三B，李四B，王五B

表关系如下：

一个队伍对应多个成员

建表语句：

     CREATE TABLE `team` (                                                      
          `id_` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '编号',               
          `name_` varchar(64) DEFAULT NULL COMMENT '队伍名称',                 
          `del_flag` tinyint(1) NOT NULL COMMENT '删除标志',                   
          PRIMARY KEY (`id_`)                                                      
     ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='队伍表'  


     CREATE TABLE `team_member` (                                                                   
       `id_` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '编号',                                   
       `team_id` bigint(20) DEFAULT NULL COMMENT '队伍id',                                        
       `team_identity` int(2) DEFAULT NULL COMMENT '队伍身份（1：队长；2：队员；）',  
       `user_name` varchar(64) DEFAULT NULL COMMENT '成员名',                                    
       `del_flag` tinyint(1) NOT NULL COMMENT '删除标志',                                       
       PRIMARY KEY (`id_`)                                                                          
     ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8 COMMENT='队伍成员表'

方式一：

select a.*,b.memberLeader from 
(
    select a.id_ AS id, a.name_ AS name,
    GROUP_CONCAT(b.user_name) AS members
    from team a
    left join team_member b on (a.id_ = b.team_id and b.del_flag = '0')
	where
	a.del_flag = '0'
	group by a.id_
) a,
(
    select a.id_ AS id, b.user_name AS memberLeader
    from team a
    left join team_member b on (a.id_ = b.team_id and b.del_flag = '0' and b.team_identity = 1)
	where
	a.del_flag = '0'
) b
where a.id = b.id

方式二：

    select a.id_ AS id, a.name_ AS name,
    GROUP_CONCAT(b.user_name) AS members,
    (	    
	select u.user_name AS memberLeader 
	from team_member u 
	where 
	del_flag = '0' 
	and team_identity = 1 
	and u.team_id = a.id_
    ) AS memberLeader
    from team a
    left join team_member b on (a.id_ = b.team_id and b.del_flag = '0')
	where
	a.del_flag = '0'
	group by a.id_

方式三：

select a.*, b.user_name AS memberLeader from 
(
    select a.id_ AS id, a.name_ AS name,
    GROUP_CONCAT(b.user_name) AS members
    from team a
    left join team_member b on (a.id_ = b.team_id and b.del_flag = '0')
	where
	a.del_flag = '0'
	group by a.id_
) a
left join team_member b on (a.id = b.team_id and b.del_flag = '0' and b.team_identity = 1)