【mysql】分组查询，分组计数

表结构：

tin_group(分组表，主键为id）

tin_group_stu(分组学生绑定表，外键为group_id)

tin_group_approve（分组审批绑定表，外键为group_id）

需求：

查询出所有所有分组，统计每个分组绑定的学生数，绑定的审批人数

SQL语句：

   SELECT
        tg.*,tgs.stuCount,tga.approveCount,ta.user_name
        FROM
        tin_group tg
        LEFT JOIN ( SELECT group_id, count( stu_id ) AS stuCount FROM tin_group_stu WHERE is_delete = 0 GROUP BY group_id ) tgs
        ON tg.id = tgs.group_id
        LEFT JOIN (SELECT group_id,count( approve_id ) AS approveCount FROM tin_group_approve WHERE is_delete = 0 AND approve_id IS NOT NULL AND approve_id <![CDATA[<>]]> ''
        GROUP BY group_id) tga ON tg.id = tga.group_id
        LEFT JOIN tc_allusers ta ON tg.user_id = ta.id
        WHERE tg.is_delete = 0