select (select 班机名称 from a where a.班级代码 = b.班级代码),count(性别) from b where 性别 = '男' group by 班级代码 union all select (select 班机名称 from a where a.班级代码 = b.班级代码),count(性别) from b where 性别 = '女' group by 班级代码 --这样可以显示班机名称
create table bjA (bjid int ,bjname char (4)) create table bjB (bjid int,stid int,stname char (10),sex char (2)) insert into bja select 1,'一班' union select 2,'二班' union select 3,'三班' union select 4,'四班' union select 5,'五班' union select 6,'六班' union select 7,'七班' union select 8,'八班' insert into bjB select 1,1,'张三','男' union select 1,2,'张四','女' union select 1,3,'张五','女' union select 1,4,'张六','女' union select 1,5,'张七','女' union select 2,6,'张八','女' union select 2,7,'张九','男' union select 3,8,'张十','女' union select 4,9,'李三','男' union select 4,10,'李四','男' union select 5,11,'张一','女' union select 5,12,'张二','女' union select 5,13,'张林','男' union select 5,14,'张钱','男' union select 5,15,'张王','男' select a.bjid as 班级ID,a.bjname as 班组名称, sum(case when sex='男' then 1 else 0 end) as 男生人数, sum(case when sex='女' then 1 else 0 end) as 女生人数 from bja a,bjb b where a.bjid=b.bjid group by a.bjid,a.bjname order by a.bjid------------- 班级ID 班组名称 男生人数 女生人数 ------ ------- ------- ------ 1 一班 1 4 2 二班 1 1 3 三班 0 1 4 四班 2 0 5 五班 3 2
select a.* ,男生人数=isnull(b.男生人数,0) ,女生人数=isnull(b.女生人数,0) from A left join( select 班级代码 ,男生人数=sum(case 性别 when '男' then 1 else 0 end) ,女生人数=sum(case 性别 when '女' then 1 else 0 end) from B group by 班级代码 )b on a.班级代码=b.班级代码
用left join,可以保证即使B表没有某班的记录,也会按0的人数显示.
我那个也要改了 select a.bjid as 班级ID,a.bjname as 班组名称, isnull (b.男生人数,0) as 男生人数, isnull (b.女生人数,0) as 女生人数 from bja a full join ( select b.bjid, sum(case when sex='男' then 1 else 0 end) as 男生人数, sum(case when sex='女' then 1 else 0 end) as 女生人数 from bjb b group by b.bjid) b on a.bjid=b.bjid
isnull (b.男生人数,0) as 男生人数, isnull (b.女生人数,0) as 女生人数sum(case when sex='男' then 1 else 0 end) as 男生人数, sum(case when sex='女' then 1 else 0 end) as 女生人数由于我才学这个,上面的还有没有看懂是什么意思?不知道是否可以解释一下? 谢谢!
isnull (b.男生人数,0) as 男生人数, 假如男生人数为NULL 则取0
isnull (b.男生人数,0) as 男生人数, 如果男生人数是空(null)则记为0sum(case when sex='男' then 1 else 0 end) as 男生人数, 计算对男生人数求和,如果性别为男则记数加1; 如果性别不是男则记为0。
邹建,有了这句 男生人数=sum(case 性别 when '男' then 1 else 0 end) 那 男生人数只可能是>=0,又如何来这句? 男生人数=isnull(b.男生人数,0)
--我改老大的代码^_^ select a.*,b.男生人数,b.女生人数 from A left join( select 班级代码 ,男生人数=sum(case 性别 when '男' then 1 else 0 end) ,女生人数=sum(case 性别 when '女' then 1 else 0 end) from B group by 班级代码 )b on a.班级代码=b.班级代码
这样好象更好: select distinct a.班级代码,b.男生人数,b.女生人数 from A left join( select 班级代码 ,男生人数=sum(case 性别 when '男' then 1 else 0 end) ,女生人数=sum(case 性别 when '女' then 1 else 0 end) from B group by 班级代码 )b on a.班级代码=b.班级代码 order by a.班级代码
union all
select (select 班机名称 from a where a.班级代码 = b.班级代码),count(性别) from b where 性别 = '女' group by 班级代码
--这样可以显示班机名称
create table bjA (bjid int ,bjname char (4))
create table bjB (bjid int,stid int,stname char (10),sex char (2))
insert into bja
select 1,'一班' union
select 2,'二班' union
select 3,'三班' union
select 4,'四班' union
select 5,'五班' union
select 6,'六班' union
select 7,'七班' union
select 8,'八班' insert into bjB
select 1,1,'张三','男' union
select 1,2,'张四','女' union
select 1,3,'张五','女' union
select 1,4,'张六','女' union
select 1,5,'张七','女' union
select 2,6,'张八','女' union
select 2,7,'张九','男' union
select 3,8,'张十','女' union
select 4,9,'李三','男' union
select 4,10,'李四','男' union
select 5,11,'张一','女' union
select 5,12,'张二','女' union
select 5,13,'张林','男' union
select 5,14,'张钱','男' union
select 5,15,'张王','男'
select a.bjid as 班级ID,a.bjname as 班组名称,
sum(case when sex='男' then 1 else 0 end) as 男生人数,
sum(case when sex='女' then 1 else 0 end) as 女生人数
from bja a,bjb b
where a.bjid=b.bjid
group by a.bjid,a.bjname
order by a.bjid-------------
班级ID 班组名称 男生人数 女生人数
------ ------- ------- ------
1 一班 1 4
2 二班 1 1
3 三班 0 1
4 四班 2 0
5 五班 3 2
select a.*
,男生人数=isnull(b.男生人数,0)
,女生人数=isnull(b.女生人数,0)
from A left join(
select 班级代码
,男生人数=sum(case 性别 when '男' then 1 else 0 end)
,女生人数=sum(case 性别 when '女' then 1 else 0 end)
from B
group by 班级代码
)b on a.班级代码=b.班级代码
select a.bjid as 班级ID,a.bjname as 班组名称,
isnull (b.男生人数,0) as 男生人数,
isnull (b.女生人数,0) as 女生人数
from
bja a full join (
select b.bjid,
sum(case when sex='男' then 1 else 0 end) as 男生人数,
sum(case when sex='女' then 1 else 0 end) as 女生人数
from bjb b
group by b.bjid) b
on a.bjid=b.bjid
isnull (b.女生人数,0) as 女生人数sum(case when sex='男' then 1 else 0 end) as 男生人数,
sum(case when sex='女' then 1 else 0 end) as 女生人数由于我才学这个,上面的还有没有看懂是什么意思?不知道是否可以解释一下?
谢谢!
假如男生人数为NULL 则取0
如果男生人数是空(null)则记为0sum(case when sex='男' then 1 else 0 end) as 男生人数,
计算对男生人数求和,如果性别为男则记数加1;
如果性别不是男则记为0。
那 男生人数只可能是>=0,又如何来这句? 男生人数=isnull(b.男生人数,0)
select a.*,b.男生人数,b.女生人数
from A left join(
select 班级代码
,男生人数=sum(case 性别 when '男' then 1 else 0 end)
,女生人数=sum(case 性别 when '女' then 1 else 0 end)
from B
group by 班级代码
)b on a.班级代码=b.班级代码
select distinct a.班级代码,b.男生人数,b.女生人数
from A left join(
select 班级代码
,男生人数=sum(case 性别 when '男' then 1 else 0 end)
,女生人数=sum(case 性别 when '女' then 1 else 0 end)
from B
group by 班级代码
)b on a.班级代码=b.班级代码
order by a.班级代码
“ ,男生人数=isnull(b.男生人数,0)
,女生人数=isnull(b.女生人数,0) ” 数目为0的项会置NULL的。谢谢各位这么乐意帮我了,我先不结贴,多听听大家还有什么好介绍先。):