select jtbh,sex,hf,sf,(select count(*) from 表 where jtbh=aa.jtbh and sex='男' and sf='子女')男孩数,(select count(*) from 表 where jtbh=aa.jtbh and sex='女' and sf='子女')女孩数 from 表 aa
select jtbh as jtbh2,case when sex='男' and hf='未婚' then count(*) else 0 end as 男孩数,case when sex='女' and hf='未婚' then count(*) else 0 end as 女孩数 into #temp_table from table1 group by jtbh select jtbh,sex,hf,sf,男孩数,女孩数 into temp_talble from table1,#temp_table where jtbh=jtbh2 update temp_table set 男孩数=null,女孩数=null where sex='男' or hf=‘未婚’ select * from temp_table go 呵呵, 写的比较麻烦啊。呵呵~~~
你的意思是已婚的女人就是母亲??? select jtbh,sex,hf,sf,case when aa.hf='已婚' and aa.sex='女' then (select count(*) from 表 where jtbh=aa.jtbh and sex='男' and sf='子女') end 男孩数,case when aa.hf='已婚' and aa.sex='女' then (select count(*) from 表 where jtbh=aa.jtbh and sex='女' and sf='子女') end 女孩数 from 表 aa
select jtbh,sex,hf,sf, (case when sex = '女' and sf = '职工' then (select count(*) from 表 where jtbh=aa.jtbh and sex='男' and sf='子女') else null end) as 男孩数, (case when sex = '女' and sf = '职工' then (select count(*) from 表 where jtbh=aa.jtbh and sex='女' and sf='子女') else null end) as 女孩数 from 表 aa
数据库中必须存储如何判定父母子女关系的字段
答:
有统一的家庭编号(jtbh)
身份(sf)值:子女
from 表 aa
select jtbh,sex,hf,sf,男孩数,女孩数 into temp_talble from table1,#temp_table where jtbh=jtbh2
update temp_table set 男孩数=null,女孩数=null where sex='男' or hf=‘未婚’
select * from temp_table
go
呵呵, 写的比较麻烦啊。呵呵~~~
select jtbh,sex,hf,sf,case when aa.hf='已婚' and aa.sex='女' then (select count(*) from 表 where jtbh=aa.jtbh and sex='男' and sf='子女') end 男孩数,case when aa.hf='已婚' and aa.sex='女' then (select count(*) from 表 where jtbh=aa.jtbh and sex='女' and sf='子女') end 女孩数
from 表 aa
(case when sex = '女' and sf = '职工' then
(select count(*) from 表 where jtbh=aa.jtbh and sex='男' and sf='子女')
else null end) as 男孩数,
(case when sex = '女' and sf = '职工' then
(select count(*) from 表 where jtbh=aa.jtbh and sex='女' and sf='子女')
else null end) as 女孩数
from 表 aa