MYSQL 统计每个班有多少个学生

[pkkgu1]

create temporary table classname
    select 1 as cid, '小一班' as cid_name
    union all select 2, '小二班'
    union all select 3, '小三班'
    union all select 4, '中一班'
    union all select 5, '中二班'
    union all select 6, '中三班'
    union all select 7, '大一班'
    union all select 8, '大二班';

create temporary table temp2
    select 1 as sid, '张一' as sid_name, 2 as cid
    union all select 2, '张二', 1
    union all select 3, '张三', 1
    union all select 4, '张四', 3
    union all select 5, '张五', 2
    union all select 6, '张六', 5
    union all select 7, '张七', 6
    union all select 8, '张八', 6;

MYSQL 统计每个班有多少个学生

[ACMAIN_CHM]

select cid,cid_name, (select count(*) from temp2 where cid=classname.cid) from classname

[weishaolin13x]

select a.cid_name ,count(*) c from classname a inner join temp2 b on a.cid=b.cid group by a.cid,a.cid_name

[weishaolin13x]

最好不要用相关子查询，每条记录都执行一次子查询，效率相当慢

[weishaolin13x]

如果为0的也要统计，那用 ： select a.cid_name ,count(b.cid) c from classname a left join temp2 b on a.cid=b.cid group by a.cid,a.cid_name

[深蓝999]

Select Select Sid ,count (1) num from temp2 group by sid