declare @class table( classId int, className varchar(20) )declare @student table( classId int, userName varchar(20), sex int ) insert into @class values(1,'A') insert into @class values(2,'B') insert into @student values(1,'m',1) insert into @student values(1,'n',2) insert into @student values(1,'o',2)insert into @student values(2,'p',2) insert into @student values(2,'q',1)select * from @classselect * from @student select count(*) as number,a.classId,b.sex from @class a inner join @student b on a.classId=b.classId group by a.classId,b.sex
mysql> select * from student; +------+------+------+------+------+ | id | name | age | cid | sex | +------+------+------+------+------+ | 1 | 张1 | 18 | 1 | 1 | | 2 | 张2 | 20 | 1 | 2 | | 3 | 张3 | 17 | 1 | 1 | | 4 | 张4 | 18 | 1 | 2 | | 5 | 张5 | 16 | 1 | 1 | | 6 | 张6 | 19 | 1 | 2 | | 7 | 张7 | 15 | 1 | 1 | | 8 | 刘1 | 16 | 2 | 2 | | 9 | 刘2 | 19 | 2 | 1 | | 10 | 刘3 | 21 | 2 | 2 | | 11 | 刘4 | 20 | 2 | 1 | | 12 | 刘5 | 15 | 2 | 2 | | 13 | 刘6 | 17 | 2 | 1 | | 14 | 刘7 | 20 | 2 | 2 | | 15 | 刘8 | 21 | 2 | 1 | +------+------+------+------+------+mysql> select * from class; +------+-----------+ | id | name | +------+-----------+ | 1 | 信计052班 | | 2 | 信计051班 | +------+-----------+ 2 rows in set (0.00 sec)mysql> select c.name,b.num boy_num,g.num girl_num -> from class c, -> (select s.cid,count(*) num -> from student s where s.sex=1 -> group by s.cid) b, -> (select s.cid,count(*) num -> from student s where s.sex=2 -> group by s.cid) g -> where c.id=b.cid and c.id=g.cid; +-----------+---------+----------+ | name | boy_num | girl_num | +-----------+---------+----------+ | 信计052班 | 4 | 3 | | 信计051班 | 4 | 4 | +-----------+---------+----------+ 2 rows in set (0.00 sec)
declare @class table(
classId int,
className varchar(20)
)declare @student table(
classId int,
userName varchar(20),
sex int
)
insert into @class values(1,'A')
insert into @class values(2,'B')
insert into @student values(1,'m',1)
insert into @student values(1,'n',2)
insert into @student values(1,'o',2)insert into @student values(2,'p',2)
insert into @student values(2,'q',1)select * from @classselect * from @student
select count(*) as number,a.classId,b.sex from @class a inner join @student b on a.classId=b.classId group by a.classId,b.sex
mysql> select * from student;
+------+------+------+------+------+
| id | name | age | cid | sex |
+------+------+------+------+------+
| 1 | 张1 | 18 | 1 | 1 |
| 2 | 张2 | 20 | 1 | 2 |
| 3 | 张3 | 17 | 1 | 1 |
| 4 | 张4 | 18 | 1 | 2 |
| 5 | 张5 | 16 | 1 | 1 |
| 6 | 张6 | 19 | 1 | 2 |
| 7 | 张7 | 15 | 1 | 1 |
| 8 | 刘1 | 16 | 2 | 2 |
| 9 | 刘2 | 19 | 2 | 1 |
| 10 | 刘3 | 21 | 2 | 2 |
| 11 | 刘4 | 20 | 2 | 1 |
| 12 | 刘5 | 15 | 2 | 2 |
| 13 | 刘6 | 17 | 2 | 1 |
| 14 | 刘7 | 20 | 2 | 2 |
| 15 | 刘8 | 21 | 2 | 1 |
+------+------+------+------+------+mysql> select * from class;
+------+-----------+
| id | name |
+------+-----------+
| 1 | 信计052班 |
| 2 | 信计051班 |
+------+-----------+
2 rows in set (0.00 sec)mysql> select c.name,b.num boy_num,g.num girl_num
-> from class c,
-> (select s.cid,count(*) num
-> from student s where s.sex=1
-> group by s.cid) b,
-> (select s.cid,count(*) num
-> from student s where s.sex=2
-> group by s.cid) g
-> where c.id=b.cid and c.id=g.cid;
+-----------+---------+----------+
| name | boy_num | girl_num |
+-----------+---------+----------+
| 信计052班 | 4 | 3 |
| 信计051班 | 4 | 4 |
+-----------+---------+----------+
2 rows in set (0.00 sec)