这个SQL要怎么写？

[abinnet]

表1：owner:
bh rq
========================
1 2001-1-1
2 2002-1-1
3 2003-1-1
4 2004-1-1

表2：house:
编号 类型 省 市 重量 金额
==================================
1 汽车 A 1 12 210
2 汽车 A 2 23 120
2 摩托车 A 1 15 450
3 摩托车 B 2 45 320
3 自行车 B 1 54 620
4 自行车 B 2 11 210

要求结果：owner.rq 在 2000-1-1 到 2004-12-31 内的汇总.
========================================
汽数 汽重 汽金 摩数 摩重 摩金 自数 自重 自金
A省 2 35 330 1 15 450
A省1市 1 12 210
A省2省 1 23 120
B省 1 45 320 2 65 830
B省1市 1 54 620
B省2省 1 45 320 1 11 210

汽数:汽车数量；汽重：汽车重量；汽金：汽车金额，其它也一样

[了缘]

house:与owner:的关联字段是什么

[yjzhg]

select 汽数, 汽重, 汽金, 0 as 摩数, 0 as 摩重, 0 as 摩金, 0 as 自数,0 as 自重,0 as 自金
from 表
group by 省，市
union all
select 0 as 汽数,0 as 汽重,0 as 汽金,摩数,摩重,摩金, 0 as 自数,0 as 自重,0 as 自金
from 表
group by 省，市
union all
select 0 as 汽数,0 as 汽重,0 as 汽金,0 as 摩数,0 as 摩重,0 as 摩金,自数,自重, 自金
from 表
group by 省，市

[yjzhg]

select *
from (
select 汽数, 汽重, 汽金, 摩数, 摩重, 摩金, 0 as 自数,0 as 自重,0 as 自金
from 表
group by 省，市
union all
select 0 as 汽数,0 as 汽重,0 as 汽金,摩数,摩重,摩金, 0 as 自数,0 as 自重,0 as 自金
from 表
group by 省，市
union all
select 0 as 汽数,0 as 汽重,0 as 汽金,0 as 摩数,0 as 摩重,0 as 摩金,自数,自重, 自金
from 表
group by 省，市
)a
group by XXXXXX

[yyhyy23]

select isnull(sum(汽数),0) as 汽数, isnull(sum(汽重),0) as 汽重, isnull(sum(汽金),0) as 汽金,isnuLL(sum(摩数),0) as 摩数, isnull(sum(摩重),0) as 摩重, isnull(sum(摩金),) as 摩金, isnull(sum(自数),) as 摩金, isnull(sum(自重),0) as 自重, isnull(sum(自金),0) as 自重
from 表
group by 省，市

[yyhyy23]

你没有把两个表之间的关系说清楚。

[abinnet]

house的‘编号’取自owner的'bh'。
owner的‘bh’是唯一的，不重复的。

[梅青松]

select rtrim(a.省)+ '省' + cast(a.市 as varchar(10)) + '市' ,
Isnull((select count(*) from house where 省= a.省 and 市 = a. 市 and 类型 = '汽?' ), 0) as 汽数,
Isnull((select sum(重量) from house where 省= a.省 and 市 = a. 市 and 类型 = '汽?' ), 0) as 汽重,
Isnull((select sum(金额) from house where 省= a.省 and 市 = a. 市 and 类型 = '汽?' ), 0) as 汽金,
Isnull((select count(*) from house where 省= a.省 and 市 = a. 市 and 类型 = '摩托车' ), 0) as 摩数,
Isnull((select sum(重量) from house where 省= a.省 and 市 = a. 市 and 类型 = '摩托车' ), 0) as 摩重,
Isnull((select sum(金额) from house where 省= a.省 and 市 = a. 市 and 类型 = '摩托车' ), 0) as 摩金,
Isnull((select count(*) from house where 省= a.省 and 市 = a. 市 and 类型 = '自行车' ), 0) as 自数,
Isnull((select sum(重量) from house where 省= a.省 and 市 = a. 市 and 类型 = '自行车' ), 0) as 自重,
Isnull((select sum(金额) from house where 省= a.省 and 市 = a. 市 and 类型 = '自行车' ), 0) as 自金
from house as a
where a.编号
in(select bh from owner where rq between '2000-1-1' and '2004-12-31')
group by 省, 市
union
select rtrim(a.省)+ '省' ,
Isnull((select count(*) from house where 省= a.省 and 类型 = '汽?' ), 0) as 汽数,
Isnull((select sum(重量) from house where 省= a.省 and 类型 = '汽?' ), 0) as 汽重,
Isnull((select sum(金额) from house where 省= a.省 and 类型 = '汽?' ), 0) as 汽金,
Isnull((select count(*) from house where 省= a.省 and 类型 = '摩托车' ), 0) as 摩数,
Isnull((select sum(重量) from house where 省= a.省 and 类型 = '摩托车' ), 0) as 摩重,
Isnull((select sum(金额) from house where 省= a.省 and 类型 = '摩托车' ), 0) as 摩金,
Isnull((select count(*) from house where 省= a.省 and 类型 = '自行车' ), 0) as 自数,
Isnull((select sum(重量) from house where 省= a.省 and 类型 = '自行车' ), 0) as 自重,
Isnull((select sum(金额) from house where 省= a.省 and 类型 = '自行车' ), 0) as 自金
from house as a
where a.编号
in(select bh from owner where rq between '2000-1-1' and '2004-12-31')
group by 省

[cloudchen]

create table owner (bh int, rq datetime)
insert into owner
select 1, '2001-1-1' union all
select 2 , '2002-1-1' union all
select 3 , '2003-1-1' union all
select 4 ,'2004-1-1'

create table house (编号 int, 类型 nvarchar(20), 省 varchar(10), 市 int, 重量 int, 金额 int)
insert into house
select 1 ,'汽车', 'A', 1 ,12, 210 union all
select 2 ,'汽车', 'A' , 2 ,23 , 120 union all
select 2 ,'摩托车', 'A' , 1 , 15 , 450 union all
select 3 ,'摩托车', 'B' , 2 , 45 , 320 union all
select 3 ,'自行车', 'B' ,1 , 54 , 620 union all
select 4 ,'自行车', 'B' ,2, 11 , 210

select
case
when b.市 is null then b.省+'省'
else b.省+'省'+cast(b.市 as varchar)+'市'
end,
汽数=sum(case 类型 when '汽车' then 1 end),
汽重=sum(case 类型 when '汽车' then 重量 end),
汽金=sum(case 类型 when '汽车' then 金额 end),
摩数=sum(case 类型 when '摩托车' then 1 end),
摩重=sum(case 类型 when '摩托车' then 重量 end),
摩金=sum(case 类型 when '摩托车' then 金额 end),
自数=sum(case 类型 when '自行车' then 1 end),
自重=sum(case 类型 when '自行车' then 重量 end),
自金=sum(case 类型 when '自行车' then 金额 end)
from
owner a join house b on a.bh = b.编号 where a.rq between '2000-1-1' and '2004-12-31'
group by b.省,b.市
with rollup
having grouping(b.省) = 0
order by 1

汽数 汽重 汽金 摩数 摩重 摩金 自数 自重 自金
-------------------------------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
A省 2 35 330 1 15 450 NULL NULL NULL
A省1市 1 12 210 1 15 450 NULL NULL NULL
A省2市 1 23 120 NULL NULL NULL NULL NULL NULL
B省 NULL NULL NULL 1 45 320 2 65 830
B省1市 NULL NULL NULL NULL NULL NULL 1 54 620
B省2市 NULL NULL NULL 1 45 320 1 11 210

（所影响的行数为 6 行）

[cloudchen]

动态的，自动适应类型字段

declare @sql varchar(8000)
set @sql = 'select
case
when b.市 is null then b.省+''省''
else b.省+''省''+cast(b.市 as varchar)+''市''
end'
select @sql=@sql+
','+left(类型,1)+'数=sum(case 类型 when '''+类型+''' then 1 end)' +
','+left(类型,1)+'重=sum(case 类型 when '''+类型+''' then 重量 end)' +
','+left(类型,1)+'金=sum(case 类型 when '''+类型+''' then 金额 end)'
from house
group by 类型
select @sql=@sql+' from
owner a join house b on a.bh = b.编号 where a.rq between ''2000-1-1'' and ''2004-12-31''
group by b.省,b.市
with rollup
having grouping(b.省) = 0
order by 1'
exec(@sql)