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 省,市
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
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 省,市
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 省
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 , 210select 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 行)
动态的,自动适应类型字段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)
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 省,市
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
from 表
group by 省,市
owner的‘bh’是唯一的,不重复的。
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 省
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 , 210select
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 行)
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)