create temporary table classname 
Select 1 as cid, 'small class as cid_name 
Union all select 2, 'small class 2' 
Union all select 3, 'small three' 
Union all select 4, 'class' 
Union all select 5, 'in the class two' 
Union all select 6, 'class 3' 
Union all select 7, 'big one' 
Union all select 8, 'a sophomore class; 

 
Create temporary table temp2 
Select 1 as sid, '1' as sid_name, 2 as cid 
Union all select 2, 'zhang 2, 1 
Union all select 3, 'zhang SAN, 1 
Union all select 4, 'zhang four, 3 
Union all select 5, 'fives, 2 
Union all select 6, 'sixes, 5 
Union all select 7, 'seven', 6 
Union all select 8, 'pieces of eight, 6; 

MYSQL statistics how many students are there in each class

CodePudding user response:

Select cid, cid_name,
(select count (*) from temp2 where cid=classname. Cid)
The from the classname

CodePudding user response:

The select a.c id_name, count (*) c from the classname a inner join temp2 b on a.c id=biggest id
Group by a.c id, a.c id_name

CodePudding user response:

It is best not to use correlated subqueries, each record is executed a second query, efficiency is quite slow

CodePudding user response:

If 0 to statistics, it use:

The select a.c id_name, count (biggest id) c from the classname a left join temp2 b on a.c id=biggest id
Group by a.c id, a.c id_name

CodePudding user response:

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