A表结构:
stuid schoolid name sex
B表结构:
schoolid schoolname
求的表结构
schoolid schooname 该校总人数 该校男生总数 该校女生总数
stuid schoolid name sex
B表结构:
schoolid schoolname
求的表结构
schoolid schooname 该校总人数 该校男生总数 该校女生总数
stuid schoolid name sex
B表结构:
schoolid schoolname
求的表结构
schoolid schooname 该校总人数 该校男生总数 该校女生总数 select b.schoolid,b.schooname,
count(*) as 该校总人数,
sum(case sex when '男' then 1 else 0 end) 该校男生总数,
sum(case sex when '女' then 1 else 0 end) 该校女生总数
from A,B
where a.schoolid = b.schoolid
group by b.schoolid,b.schooname
select b.schoolid,b.schoolname,
count(*) as 该校总人数,
sum(case sex when '男' then 1 else 0 end) 该校男生总数,
sum(case sex when '女' then 1 else 0 end) 该校女生总数
from A,B
where a.schoolid = b.schoolid
group by b.schoolid,b.schoolname
select schoolid,schooname,
该校总人数=(select count(1) from a where schoolid=b.schoolid),
该校男生总数=(select count(1) from a where schoolid=b.schoolid and sex='男'),
该校女生总数=(select count(1) from a where schoolid=b.schoolid and sex='女')
from b