这样的统计SQL如何写?

Question

1.户表结构如下: 
户ID  户名
1     xxx
2     yyy
.....2.成员表结构:
ID  户ID  姓名  性别  年龄 文化程度
1   1     a     男     20    1
2   2     b     女      11   2
....文化程度分为三种: 1(小学), 2(中学)  3(大学)要求统计出如下结果:-----------------------------------------------------------------------------------
户   |  户   |   本户总人数   |     小学人数   |     中学人数   |    大学人数    |
ID   |  名   | 合计 | 男 | 女 | 合计 | 男 | 女 | 合计 | 男 | 女 | 合计 | 男 | 女 |
------------------------------------------------------------------------------------
id   | xxxx  | 3    | 2  | 1  | 1    |  0 | 1  | 1    |  1 | 0  | 2    |  1 | 1  |
............................

Answer 1

select a.户ID,b.户名,
count(*) as 本户总人数合计,
sum(case when a.性别='男' then 1 else 0 end) as 本户总人数男,
sum(case when a.性别='女' then 1 else 0 end) as 本户总人数女,
sum(case when a.文化程度=1 then 1 else 0 end) as 小学人数合计,
sum(case when a.文化程度=1 and a.性别='男' then 1 else 0 end) as 小学人数男,
sum(case when a.文化程度=1 and a.性别='女' then 1 else 0 end) as 小学人数女,
sum(case when a.文化程度=2 then 1 else 0 end) as 中学人数合计,
sum(case when a.文化程度=2 and a.性别='男' then 1 else 0 end) as 中学人数男,
sum(case when a.文化程度=2 and a.性别='女' then 1 else 0 end) as 中学人数女,
sum(case when a.文化程度=3 then 1 else 0 end) as 大学人数合计,
sum(case when a.文化程度=3 and a.性别='男' then 1 else 0 end) as 大学人数男,
sum(case when a.文化程度=3 and a.性别='女' then 1 else 0 end) as 大学人数女
from 成员表 a,户表 b
where a.户ID=b.户ID
group by a.户ID,b.户名

Answer 2

create table 户表(户ID int,  户名 varchar(10))
insert 户表
select 1,'xxx' union all
select 2,'yyy' union all
select 3,'zzz'
select * from 户表create table 成员表(ID int identity(1,1),户ID int,  姓名 varchar(10),  性别 varchar(2),  年龄 int,文化程度 int)
insert 成员表(户ID,姓名,性别,年龄,文化程度)
select 1     ,'a','男',     20,    1 union all
select 2     ,'b','女',      11,   2
select * from 成员表select a.户ID,A.户名,
count(A.姓名) as 本户总人数合计,
sum(case when a.性别='男' then 1 else 0 end) as 本户总人数男,
sum(case when a.性别='女' then 1 else 0 end) as 本户总人数女,
sum(case when a.文化程度=1 then 1 else 0 end) as 小学人数合计,
sum(case when a.文化程度=1 and a.性别='男' then 1 else 0 end) as 小学人数男,
sum(case when a.文化程度=1 and a.性别='女' then 1 else 0 end) as 小学人数女,
sum(case when a.文化程度=2 then 1 else 0 end) as 中学人数合计,
sum(case when a.文化程度=2 and a.性别='男' then 1 else 0 end) as 中学人数男,
sum(case when a.文化程度=2 and a.性别='女' then 1 else 0 end) as 中学人数女,
sum(case when a.文化程度=3 then 1 else 0 end) as 大学人数合计,
sum(case when a.文化程度=3 and a.性别='男' then 1 else 0 end) as 大学人数男,
sum(case when a.文化程度=3 and a.性别='女' then 1 else 0 end) as 大学人数女
from 
(
select F.户ID,F.户名,C.姓名,C.性别,C.年龄,C.文化程度 from 户表 F
left outer join 成员表 C
on F.户ID=C.户ID
)A
group by A.户ID,A.户名drop table 户表,成员表

Answer 3

注：楼上代码为 i9988(冒牌j9988 V0.2) 的代码基础上更改

Answer 4

create table 户表(户ID int,  户名 varchar(10))
insert 户表
select 1,'xxx' union all
select 2,'yyy' union all
select 3,'zzz'
select * from 户表create table 成员表(ID int identity(1,1),户ID int,  姓名 varchar(10),  性别 varchar(2),  年龄 int,文化程度 int,申报日期 datetime)
insert 成员表(户ID,姓名,性别,年龄,文化程度,申报日期)
select 1,'a','男',20,1,'2005-5-18' union all
select 1,'c','女',18,3,'2005-8-18' union all
select 2,'b','女',11,2,'2006-6-18'
select * from 成员表select a.户ID,A.户名,
count(A.姓名) as 本户总人数合计,
sum(case when a.性别='男' then 1 else 0 end) as 本户总人数男,
sum(case when a.性别='女' then 1 else 0 end) as 本户总人数女,
sum(case when a.文化程度=1 then 1 else 0 end) as 小学人数合计,
sum(case when a.文化程度=1 and a.性别='男' then 1 else 0 end) as 小学人数男,
sum(case when a.文化程度=1 and a.性别='女' then 1 else 0 end) as 小学人数女,
sum(case when a.文化程度=2 then 1 else 0 end) as 中学人数合计,
sum(case when a.文化程度=2 and a.性别='男' then 1 else 0 end) as 中学人数男,
sum(case when a.文化程度=2 and a.性别='女' then 1 else 0 end) as 中学人数女,
sum(case when a.文化程度=3 then 1 else 0 end) as 大学人数合计,
sum(case when a.文化程度=3 and a.性别='男' then 1 else 0 end) as 大学人数男,
sum(case when a.文化程度=3 and a.性别='女' then 1 else 0 end) as 大学人数女
from 
(
select F.户ID,F.户名,C.姓名,C.性别,C.年龄,C.文化程度 from 户表 F
left outer join 成员表 C
on F.户ID=C.户ID
)A
group by A.户ID,A.户名户ID         户名         本户总人数合计     本户总人数男      本户总人数女      小学人数合计      小学人数男       小学人数女       中学人数合计      中学人数男       中学人数女       大学人数合计      大学人数男       大学人数女       
----------- ---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 
1           xxx        2           1           1           1           1           0           0           0           0           1           0           1
2           yyy        1           0           1           0           0           0           1           0           1           0           0           0
3           zzz        0           0           0           0           0           0           0           0           0           0           0           0（所影响的行数为 3 行）select '年份'=year(a.申报日期),
count(a.姓名) as 总人数合计,
sum(case when a.性别='男' then 1 else 0 end) as 本户总人数男,
sum(case when a.性别='女' then 1 else 0 end) as 总人数女,
sum(case when a.文化程度=1 then 1 else 0 end) as 小学人数合计,
sum(case when a.文化程度=1 and a.性别='男' then 1 else 0 end) as 小学人数男,
sum(case when a.文化程度=1 and a.性别='女' then 1 else 0 end) as 小学人数女,
sum(case when a.文化程度=2 then 1 else 0 end) as 中学人数合计,
sum(case when a.文化程度=2 and a.性别='男' then 1 else 0 end) as 中学人数男,
sum(case when a.文化程度=2 and a.性别='女' then 1 else 0 end) as 中学人数女,
sum(case when a.文化程度=3 then 1 else 0 end) as 大学人数合计,
sum(case when a.文化程度=3 and a.性别='男' then 1 else 0 end) as 大学人数男,
sum(case when a.文化程度=3 and a.性别='女' then 1 else 0 end) as 大学人数女
from 成员表 a
group by year(a.申报日期)
年份          总人数合计       本户总人数男      总人数女        小学人数合计      小学人数男       小学人数女       中学人数合计      中学人数男       中学人数女       大学人数合计      大学人数男       大学人数女       
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 
2005        2           1           1           1           1           0           0           0           0           1           0           1
2006        1           0           1           0           0           0           1           0           1           0           0           0（所影响的行数为 2 行）drop table 户表,成员表

Answer 5

用不着子查询
直接select b.户ID,b.户名,
count(distinct a.户ID) as 本户总人数合计,
sum(case when a.性别='男' then 1 else 0 end) as 本户总人数男,
sum(case when a.性别='女' then 1 else 0 end) as 本户总人数女,
sum(case when a.文化程度=1 then 1 else 0 end) as 小学人数合计,
sum(case when a.文化程度=1 and a.性别='男' then 1 else 0 end) as 小学人数男,
sum(case when a.文化程度=1 and a.性别='女' then 1 else 0 end) as 小学人数女,
sum(case when a.文化程度=2 then 1 else 0 end) as 中学人数合计,
sum(case when a.文化程度=2 and a.性别='男' then 1 else 0 end) as 中学人数男,
sum(case when a.文化程度=2 and a.性别='女' then 1 else 0 end) as 中学人数女,
sum(case when a.文化程度=3 then 1 else 0 end) as 大学人数合计,
sum(case when a.文化程度=3 and a.性别='男' then 1 else 0 end) as 大学人数男,
sum(case when a.文化程度=3 and a.性别='女' then 1 else 0 end) as 大学人数女
from 户表 b left join 成员表 a
on a.户ID=b.户ID
group by b.户ID,b.户名