MYSQL 統計一個班有多少個學生

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;

SQL聯表怎麼寫？

回複討論(解決方案)

繼續求指點....

SELECT temp2.cid,classname.cid_name,count(temp2.cid) counts,GROUP_CONCAT(temp2.sid_name) names FROM classname,temp2 WHERE classname.cid=temp2.cid GROUP BY temp2.cid;

