数据表Table1信息:
客户编号 理赔数量 销售数量 理赔率
0035 5 0 5
0088 37 0 37
0059 1 0 1
... ... ... ...
要求结果:
范围 客户数量 总理赔数 总销售数
0-5 7 12 0
5-10 1 5 0
10-15 2 20 0
15-20 1 18 0
说明:如果销售数量为0,则按1处理。
使用方法:
DECLARE @p INT
SET @p=5
SELECT RTRIM(p*@p)+'-'+RTRIM((p+1)*@p) as '范围'
,COUNT(*) AS '经销商数量'
,SUM(T1.LP) AS '理赔数'
,SUM(T1.Sale) AS '销售量'
FROM
(SELECT T1.CUSID
,COUNT(TYRNO) AS 'LP'
,ISNULL(SUM(Amount),0) AS 'Sale'
,(COUNT(TYRNO)/(CASE ISNULL(SUM(Amount),0) WHEN 0 THEN 1 ELSE ISNULL(SUM(Amount),0) END))/@p AS p
FROM CTVBI T1 INNER JOIN
CTVDI T2 ON T1.CSTID=T2.CSTID LEFT JOIN
CTBAD T3 ON T1.CUSID=T3.CUSID LEFT JOIN
tb_CS_SaleAmount T4 ON T3.CUSID=T4.CustomerID
WHERE T2.Atype='01'
GROUP BY T1.CUSID) T1
WHERE T1.p >=0 AND T1.p<=6
GROUP BY T1.p问题:
1、临界值统计不对,例如:0-5,如果理赔率为5,则此数据不在统计之内?
2、范围缺少,如果理赔率在20-25之间不存在数据,则此范围不显示,要求显示,合计值均为0?
3、统计要求理赔率在30%以上的做一类处理,不知道如何实现?
客户编号 理赔数量 销售数量 理赔率
0035 5 0 5
0088 37 0 37
0059 1 0 1
... ... ... ...
要求结果:
范围 客户数量 总理赔数 总销售数
0-5 7 12 0
5-10 1 5 0
10-15 2 20 0
15-20 1 18 0
说明:如果销售数量为0,则按1处理。
使用方法:
DECLARE @p INT
SET @p=5
SELECT RTRIM(p*@p)+'-'+RTRIM((p+1)*@p) as '范围'
,COUNT(*) AS '经销商数量'
,SUM(T1.LP) AS '理赔数'
,SUM(T1.Sale) AS '销售量'
FROM
(SELECT T1.CUSID
,COUNT(TYRNO) AS 'LP'
,ISNULL(SUM(Amount),0) AS 'Sale'
,(COUNT(TYRNO)/(CASE ISNULL(SUM(Amount),0) WHEN 0 THEN 1 ELSE ISNULL(SUM(Amount),0) END))/@p AS p
FROM CTVBI T1 INNER JOIN
CTVDI T2 ON T1.CSTID=T2.CSTID LEFT JOIN
CTBAD T3 ON T1.CUSID=T3.CUSID LEFT JOIN
tb_CS_SaleAmount T4 ON T3.CUSID=T4.CustomerID
WHERE T2.Atype='01'
GROUP BY T1.CUSID) T1
WHERE T1.p >=0 AND T1.p<=6
GROUP BY T1.p问题:
1、临界值统计不对,例如:0-5,如果理赔率为5,则此数据不在统计之内?
2、范围缺少,如果理赔率在20-25之间不存在数据,则此范围不显示,要求显示,合计值均为0?
3、统计要求理赔率在30%以上的做一类处理,不知道如何实现?
----统计条件加上 and ISNULL(SUM(Amount),0) END))/@p<>0 or ISNULL(SUM(Amount),0) END))=02、范围缺少,如果理赔率在20-25之间不存在数据,则此范围不显示,要求显示,合计值均为0?
----必须预先定义好样式,再左连你的结果3、统计要求理赔率在30%以上的做一类处理,不知道如何实现?
----大
第一个问题可以这样
create table test(客户编号 varchar(10),理赔数量 int,销售数量 int,理赔率 int)insert into test
select '0035', 5 ,0, 5
union all select
'0088', 37, 0 ,37
union all select
'0059', 1 ,0, 1
union all select
'0061',1,2,null
DECLARE @p INT
SET @p=5select 客户数量=count(1),总理赔数=sum(理赔数量),总销售数=sum(销售数量)
,理赔率=(isnull(理赔率,0)-1)/@p
from test
group by (isnull(理赔率,0)-1)/@p
select '0035', 5 ,0, 5
union all select
'0088', 37, 0 ,37
union all select
'0059', 1 ,0, 1
union all select
'0061',1,2,null
select b.理赔率,客户数量,总理赔数,总销售数 from
(
select 理赔率='0-5'
union all select '6-10' union all select '11-15'
union all select '16-20' union all select '21-25'
union all select '26-30' union all select '30%以上'
)b
left join
(select 客户数量=count(1),总理赔数=sum(理赔数量),总销售数=sum(销售数量),理赔率 from
(select 客户编号,理赔数量,销售数量
,理赔率=case when (isnull(理赔率,0)-1)/5>=6 then '30%以上' else cast((isnull(理赔率,0)-1)/5 as varchar)+'-'+cast(((isnull(理赔率,0)-1)/5+1)*5 as varchar) end
from test)a
group by a.理赔率)a
on b.理赔率=a.理赔率
select '0035',5,0,5
union all select
'0088',37,0,37
union all select
'0059',1,0,1
union all select
'0061',1,2,nulldeclare @eachP int ---每一统计层的数量(这里为5)
declare @steps int ---统计几层,其余以(“以上”为计)(这里为6层,5*6=30)
declare @i int ---临时用计数 set @eachP=6
set @steps=6
set @i=0---得到显示样式表
declare @r table(理赔率 varchar(200))
while(@i<=@steps)
begin
if @i=0
begin
insert into @r
select '0-'+cast(@eachp as varchar)
end
else if @i<>@steps
begin
insert into @r
select cast(@i*@steps+1 as varchar)+'-'+cast(@i*@steps+@eachp as varchar)
end
else
begin
insert into @r
select cast(@i*@steps as varchar)+'%以上'
end
set @i=@i+1
endselect b.理赔率,客户数量,总理赔数,总销售数 from
(
select 理赔率 from @r
)b
left join
(select 客户数量=count(1),总理赔数=sum(理赔数量),总销售数=sum(销售数量),理赔率 from
(select 客户编号,理赔数量,销售数量
,理赔率=case when (isnull(理赔率,0)-1)/@eachP>=@steps then cast(@eachP*@steps as varchar)+'%以上' else cast((isnull(理赔率,0)-1)/@eachP as varchar)+'-'+cast(((isnull(理赔率,0)-1)/@eachP+1)*@eachP as varchar) end
from test)a
group by a.理赔率)a
on b.理赔率=a.理赔率
declare @steps int ---统计几层,其余以(“以上”为计)(这里为6层,5*6=30)
-------------------------------------------------------------这两句话注解有点问题,你自己改回来