select 1 编号,'00' 性质, 10 数量,1.1 指标1,10 指标2 into 表1 union all select 2 编号,'01' 性质, 20 数量,1.2 指标1,20 指标2 union all select 3 编号,'00' 性质, 30 数量,1.5 指标1,10 指标2 union all select 4 编号,'01' 性质, 40 数量,1.9 指标1,30 指标2 union all select 5 编号,'00' 性质, 40 数量,1.2 指标1,20 指标2 select case when 指标1<1.0 then '<1.0' when 指标1 >=1.0 and 指标1 <=1.29 then '1.0-1.29' when 指标1 >=1.3 and 指标1 <=1.59 then '1.3-1.59' when 指标1 between 1.6 and 1.99 then '1.6-1.99' when 指标1 >=2 then '>=2' end as [范围], sum( case when 性质='00'then 数量*1.000 else 0 end )/ (select sum(数量) from 表1 where 性质='00') as [性质(00)], sum( case when 性质='01'then 数量*1.000 else 0 end )/ (select sum(数量) from 表1 where 性质='01') as [性质(01)] from ( select * from 表1 union select -1,'mm',0,0,0 union select -1,'mm',0,1,0 union select -1,'mm',0,1.3,0 union select -1,'mm',0,1.6,0 union select -1,'mm',0,2,0 ) a group by case when 指标1<1.0 then '<1.0' when 指标1 >=1.0 and 指标1 <=1.29 then '1.0-1.29' when 指标1 >=1.3 and 指标1 <=1.59 then '1.3-1.59' when 指标1 between 1.6 and 1.99 then '1.6-1.99' when 指标1 >=2 then '>=2' end union select '指标1平均值' 范围 , sum(case when 性质='00' then 指标1 else 0 end)/sum(case when 性质='00' then 1 else 0 end), sum(case when 性质='01' then 指标1 else 0 end)/sum(case when 性质='01' then 1 else 0 end) from 表1 drop table 表1(所影响的行数为 5 行)范围 性质(00) 性质(01) ----------- ---------------------------------------- ----------------- <1.0 .000000 .000000 >=2 .000000 .000000 1.0-1.29 .625000 .333333 1.3-1.59 .375000 .000000 1.6-1.99 .000000 .666666 指标1平均值 1.266666 1.550000(所影响的行数为 6 行)
--示例--测试数据 create table tb(编号 int,性质 varchar(10),数量 int,指标1 decimal(10,1),指标2 decimal) insert tb select 1 ,'00' ,10,1.1 ,10 union all select 2 ,'01' ,20,1.2 ,20 union all select 3 ,'00' ,30,1.5 ,10 union all select 4 ,'01' ,40,1.9 ,35 union all select 5 ,'00' ,40,1.2 ,20 go--查询 select a,范围,[性质(00)],[性质(01)] from( select a=case a.id when 1 then '指标1' when 21 then '指标2' else '' end, 范围=a.lb, [性质(00)]=cast(case when b.a>0 then isnull(a.a*1./b.a,0) else 0 end as decimal(10,2)), [性质(01)]=cast(case when b.a>0 then isnull(a.a*1./b.a,0) else 0 end as decimal(10,2)), a.id from( select b.id,b.lb, a=sum(case a.性质 when '00' then a.数量 end), b=sum(case a.性质 when '01' then a.数量 end) from tb a right join( select id=1,lb='<1.0' ,a=null,b=1.0 union all select id=2,lb='1.0-1.29',a=1.0 ,b=1.3 union all select id=3,lb='1.3-1.59',a=1.3 ,b=1.9 union all select id=4,lb='1.9-1.99',a=1.9 ,b=2.0 union all select id=5,lb='>=2' ,a=2.0 ,b=null )b on a.指标1>=isnull(b.a,a.指标1) and a.指标1<isnull(b.b,a.指标1-1) group by b.id,b.lb union all select b.id,b.lb, a=sum(case a.性质 when '00' then a.数量 end), b=sum(case a.性质 when '01' then a.数量 end) from tb a right join( select id=21,lb='<10' ,a=null,b=10 union all select id=22,lb='10-31',a=10 ,b=31 union all select id=23,lb='31-50',a=31 ,b=51 union all select id=25,lb='>=50' ,a=50 ,b=null )b on a.指标2>=isnull(b.a,a.指标2) and a.指标2<isnull(b.b,a.指标2-1) group by b.id,b.lb )a,( select a=isnull(sum(case 性质 when '00' then 数量 end),0), b=isnull(sum(case 性质 when '01' then 数量 end),0) from tb )b union all select '指标1平均值','', cast(isnull( case when count(case 性质 when '00' then 性质 end)>0 then sum(case 性质 when '00' then 指标1 end) *1./count(case 性质 when '00' then 性质 end) else 0 end,0) as decimal(10,2)), cast(isnull( case when count(case 性质 when '01' then 性质 end)>0 then sum(case 性质 when '01' then 指标1 end) *1./count(case 性质 when '01' then 性质 end) else 0 end,0) as decimal(10,2)), id=6 from tb union all select '指标2平均值','', cast(isnull( case when count(case 性质 when '00' then 性质 end)>0 then sum(case 性质 when '00' then 指标2 end) *1./count(case 性质 when '00' then 性质 end) else 0 end,0) as decimal(10,2)), cast(isnull( case when count(case 性质 when '01' then 性质 end)>0 then sum(case 性质 when '01' then 指标2 end) *1./count(case 性质 when '01' then 性质 end) else 0 end,0) as decimal(10,2)), id=26 from tb union all select '数量合计:','', isnull(sum(case 性质 when '00' then 数量 end),0), isnull(sum(case 性质 when '01' then 数量 end),0), id=30 from tb )a order by id go--删除测试 drop table tb/*--测试结果 a 范围 性质(00) 性质(01) ----------- -------- -------------- -------------- 指标1 <1.0 .00 .00 1.0-1.29 .63 .63 1.3-1.59 .38 .38 1.9-1.99 .00 .00 >=2 .00 .00 指标1平均值 1.27 1.55 指标2 <10 .00 .00 10-31 1.00 1.00 31-50 .00 .00 >=50 .00 .00 指标2平均值 13.33 27.50 数量合计: 80.00 60.00(所影响的行数为 12 行) --*/
select 2 编号,'01' 性质, 20 数量,1.2 指标1,20 指标2 union all
select 3 编号,'00' 性质, 30 数量,1.5 指标1,10 指标2 union all
select 4 编号,'01' 性质, 40 数量,1.9 指标1,30 指标2 union all
select 5 编号,'00' 性质, 40 数量,1.2 指标1,20 指标2 select
case when 指标1<1.0 then '<1.0'
when 指标1 >=1.0 and 指标1 <=1.29 then '1.0-1.29'
when 指标1 >=1.3 and 指标1 <=1.59 then '1.3-1.59'
when 指标1 between 1.6 and 1.99 then '1.6-1.99'
when 指标1 >=2 then '>=2'
end as [范围],
sum( case when 性质='00'then 数量*1.000 else 0 end )/
(select sum(数量) from 表1 where 性质='00') as [性质(00)],
sum( case when 性质='01'then 数量*1.000 else 0 end )/
(select sum(数量) from 表1 where 性质='01') as [性质(01)]
from
( select * from 表1
union
select -1,'mm',0,0,0
union
select -1,'mm',0,1,0
union
select -1,'mm',0,1.3,0
union
select -1,'mm',0,1.6,0
union
select -1,'mm',0,2,0
) a
group by case when 指标1<1.0 then '<1.0'
when 指标1 >=1.0 and 指标1 <=1.29 then '1.0-1.29'
when 指标1 >=1.3 and 指标1 <=1.59 then '1.3-1.59'
when 指标1 between 1.6 and 1.99 then '1.6-1.99'
when 指标1 >=2 then '>=2' end
union
select '指标1平均值' 范围 ,
sum(case when 性质='00' then 指标1 else 0 end)/sum(case when 性质='00' then 1 else 0 end),
sum(case when 性质='01' then 指标1 else 0 end)/sum(case when 性质='01' then 1 else 0 end)
from 表1 drop table 表1(所影响的行数为 5 行)范围 性质(00) 性质(01)
----------- ---------------------------------------- -----------------
<1.0 .000000 .000000
>=2 .000000 .000000
1.0-1.29 .625000 .333333
1.3-1.59 .375000 .000000
1.6-1.99 .000000 .666666
指标1平均值 1.266666 1.550000(所影响的行数为 6 行)
谢谢你!
不过这样只能得到一个指标的结果,我试着作成问题的格式,可是总出错,能再提示提示么?
谢谢,谢谢。谢谢!!!!!!
create table tb(编号 int,性质 varchar(10),数量 int,指标1 decimal(10,1),指标2 decimal)
insert tb select 1 ,'00' ,10,1.1 ,10
union all select 2 ,'01' ,20,1.2 ,20
union all select 3 ,'00' ,30,1.5 ,10
union all select 4 ,'01' ,40,1.9 ,35
union all select 5 ,'00' ,40,1.2 ,20
go--查询
select a,范围,[性质(00)],[性质(01)]
from(
select
a=case a.id when 1 then '指标1' when 21 then '指标2' else '' end,
范围=a.lb,
[性质(00)]=cast(case when b.a>0 then isnull(a.a*1./b.a,0) else 0 end as decimal(10,2)),
[性质(01)]=cast(case when b.a>0 then isnull(a.a*1./b.a,0) else 0 end as decimal(10,2)),
a.id
from(
select b.id,b.lb,
a=sum(case a.性质 when '00' then a.数量 end),
b=sum(case a.性质 when '01' then a.数量 end)
from tb a
right join(
select id=1,lb='<1.0' ,a=null,b=1.0 union all
select id=2,lb='1.0-1.29',a=1.0 ,b=1.3 union all
select id=3,lb='1.3-1.59',a=1.3 ,b=1.9 union all
select id=4,lb='1.9-1.99',a=1.9 ,b=2.0 union all
select id=5,lb='>=2' ,a=2.0 ,b=null
)b on a.指标1>=isnull(b.a,a.指标1)
and a.指标1<isnull(b.b,a.指标1-1)
group by b.id,b.lb
union all
select b.id,b.lb,
a=sum(case a.性质 when '00' then a.数量 end),
b=sum(case a.性质 when '01' then a.数量 end)
from tb a right join(
select id=21,lb='<10' ,a=null,b=10 union all
select id=22,lb='10-31',a=10 ,b=31 union all
select id=23,lb='31-50',a=31 ,b=51 union all
select id=25,lb='>=50' ,a=50 ,b=null
)b on a.指标2>=isnull(b.a,a.指标2)
and a.指标2<isnull(b.b,a.指标2-1)
group by b.id,b.lb
)a,(
select
a=isnull(sum(case 性质 when '00' then 数量 end),0),
b=isnull(sum(case 性质 when '01' then 数量 end),0)
from tb
)b
union all
select '指标1平均值','',
cast(isnull(
case
when count(case 性质 when '00' then 性质 end)>0
then sum(case 性质 when '00' then 指标1 end)
*1./count(case 性质 when '00' then 性质 end)
else 0
end,0) as decimal(10,2)),
cast(isnull(
case
when count(case 性质 when '01' then 性质 end)>0
then sum(case 性质 when '01' then 指标1 end)
*1./count(case 性质 when '01' then 性质 end)
else 0
end,0) as decimal(10,2)),
id=6
from tb
union all
select '指标2平均值','',
cast(isnull(
case
when count(case 性质 when '00' then 性质 end)>0
then sum(case 性质 when '00' then 指标2 end)
*1./count(case 性质 when '00' then 性质 end)
else 0
end,0) as decimal(10,2)),
cast(isnull(
case
when count(case 性质 when '01' then 性质 end)>0
then sum(case 性质 when '01' then 指标2 end)
*1./count(case 性质 when '01' then 性质 end)
else 0
end,0) as decimal(10,2)),
id=26
from tb
union all
select '数量合计:','',
isnull(sum(case 性质 when '00' then 数量 end),0),
isnull(sum(case 性质 when '01' then 数量 end),0),
id=30
from tb
)a order by id
go--删除测试
drop table tb/*--测试结果
a 范围 性质(00) 性质(01)
----------- -------- -------------- --------------
指标1 <1.0 .00 .00
1.0-1.29 .63 .63
1.3-1.59 .38 .38
1.9-1.99 .00 .00
>=2 .00 .00
指标1平均值 1.27 1.55
指标2 <10 .00 .00
10-31 1.00 1.00
31-50 .00 .00
>=50 .00 .00
指标2平均值 13.33 27.50
数量合计: 80.00 60.00(所影响的行数为 12 行)
--*/