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 统计每个班有多少个学生
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 统计每个班有多少个学生
(select count(*) from temp2 where cid=classname.cid)
from classname
group by a.cid,a.cid_name
group by a.cid,a.cid_name