set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goCREATE PROCEDURE [dbo].[getPlaceEvaluate]
@placeno varchar(10),@getnumtimeBegin datetime, @getnumtimeEnd datetime
as
begin
select b.placename as '网点名称',sum( case evaluate when '1' then 1 else 0 end)+sum( case evaluate when '2' then 1 else 0 end) +sum( case evaluate when '3' then 1 else 0 end) + sum( case evaluate when '4' then 1 else 0 end) as '评价人数',
sum( case evaluate when '1' then 1 else 0 end) as '非常满意',sum( case evaluate when '2' then 1 else 0 end) as '满意',sum( case evaluate when '3' then 1 else 0 end) as '基本满意', sum( case evaluate when '4' then 1 else 0 end) as '不满意',sum( case evaluate when '0' then 1 else 0 end) as '未发起', sum( case evaluate when '9' then 1 else 0 end) as '未评价',
case convert(float(2),
sum( case evaluate when '1' then 1 else 0 end)+
sum( case evaluate when '2' then 1 else 0 end) +
sum( case evaluate when '3' then 1 else 0 end) +
sum( case evaluate when '4' then 1 else 0 end) +
sum( case evaluate when '0' then 1 else 0 end) +
sum( case evaluate when '9' then 1 else 0 end) )
when 0 then '0%'
else
LEFT(convert(varchar,(convert(float(2),sum( case evaluate when '1' then 1 when '2' then 1 when '3' then 1 when '4' then 1 else 0 end))/
convert(float(2),
sum( case evaluate when '1' then 1 else 0 end)+
sum( case evaluate when '2' then 1 else 0 end) +
sum( case evaluate when '3' then 1 else 0 end) +
sum( case evaluate when '4' then 1 else 0 end) +
sum( case evaluate when '0' then 1 else 0 end) +
sum( case evaluate when '9' then 1 else 0 end) )*100) ),5)+'%'
end
as '客户评价率',case
convert(float(2),sum( case evaluate when '1' then 1 else 0 end)+sum( case evaluate when '2' then 1 else 0 end) +sum( case evaluate when '3' then 1 else 0 end) + sum( case evaluate when '4' then 1 else 0 end) )
when 0 then '0%'
else
left(convert(varchar,(convert(float(2),
sum( case evaluate when '1' then 1 else 0 end) )/
convert(float(2),
sum( case evaluate when '1' then 1 else 0 end)+
sum( case evaluate when '2' then 1 else 0 end) +
sum( case evaluate when '3' then 1 else 0 end) +
sum( case evaluate when '4' then 1 else 0 end) )*100) ),5)+'%' end as '非常满意率',
case convert(float(2),
sum( case evaluate when '1' then 1 else 0 end)+
sum( case evaluate when '2' then 1 else 0 end) +
sum( case evaluate when '3' then 1 else 0 end) +
sum( case evaluate when '4' then 1 else 0 end) )
when 0 then '0%'
else left(convert(varchar,(convert(float(2),
sum( case evaluate when '2' then 1 else 0 end) )
/convert(float(2),
sum( case evaluate when '1' then 1 else 0 end)+
sum( case evaluate when '2' then 1 else 0 end) +
sum( case evaluate when '3' then 1 else 0 end) +
sum( case evaluate when '4' then 1 else 0 end) )*100) ),5)+'%' end as '满意率',
case convert(float(2),
sum( case evaluate when '1' then 1 else 0 end)+
sum( case evaluate when '2' then 1 else 0 end) +
sum( case evaluate when '3' then 1 else 0 end) +
sum( case evaluate when '4' then 1 else 0 end) )
when 0 then '0%'
else left(convert(varchar,(convert(float(2),
sum( case evaluate when '3' then 1 else 0 end) )/
convert(float(2),
sum( case evaluate when '1' then 1 else 0 end)+
sum( case evaluate when '2' then 1 else 0 end) +
sum( case evaluate when '3' then 1 else 0 end) +
sum( case evaluate when '4' then 1 else 0 end) )*100) ),5)+'%' end as '基本满意率',
case convert(float(2),
sum( case evaluate when '1' then 1 else 0 end)+
sum( case evaluate when '2' then 1 else 0 end) +
sum( case evaluate when '3' then 1 else 0 end) +
sum( case evaluate when '4' then 1 else 0 end) )
when 0 then '0%' else left(convert(varchar,(convert(float(2),
sum( case evaluate when '4' then 1 else 0 end) )/
convert(float(2),
sum( case evaluate when '1' then 1 else 0 end)+
sum( case evaluate when '2' then 1 else 0 end) +
sum( case evaluate when '3' then 1 else 0 end) +
sum( case evaluate when '4' then 1 else 0 end) )*100) ),5)+'%' end as '不满意率'
from history a
left join placeInfo b
on b.placeNo=a.placeNo
Where a.placeno = @placeno and getnumtime between @getnumtimeBegin and @getnumtimeEnd and dealtype='1'
group by b.placeName
--endUNION ALL--begin
select
count(*) as '叫号数',
sum(case dealtype when '0' then 1 else 0 end) as '空号数',
count(*)-sum(case dealtype when '0' then 1 else 0 end) as '业务量',
placename as '网点名称'from history c
left join placeinfo d
on c.placeNo=d.placeNo
Where c.placeno = @placeno and getnumtime between @getnumtimeBegin and @getnumtimeEnd
group by d.placeName
end
set QUOTED_IDENTIFIER ON
goCREATE PROCEDURE [dbo].[getPlaceEvaluate]
@placeno varchar(10),@getnumtimeBegin datetime, @getnumtimeEnd datetime
as
begin
select b.placename as '网点名称',sum( case evaluate when '1' then 1 else 0 end)+sum( case evaluate when '2' then 1 else 0 end) +sum( case evaluate when '3' then 1 else 0 end) + sum( case evaluate when '4' then 1 else 0 end) as '评价人数',
sum( case evaluate when '1' then 1 else 0 end) as '非常满意',sum( case evaluate when '2' then 1 else 0 end) as '满意',sum( case evaluate when '3' then 1 else 0 end) as '基本满意', sum( case evaluate when '4' then 1 else 0 end) as '不满意',sum( case evaluate when '0' then 1 else 0 end) as '未发起', sum( case evaluate when '9' then 1 else 0 end) as '未评价',
case convert(float(2),
sum( case evaluate when '1' then 1 else 0 end)+
sum( case evaluate when '2' then 1 else 0 end) +
sum( case evaluate when '3' then 1 else 0 end) +
sum( case evaluate when '4' then 1 else 0 end) +
sum( case evaluate when '0' then 1 else 0 end) +
sum( case evaluate when '9' then 1 else 0 end) )
when 0 then '0%'
else
LEFT(convert(varchar,(convert(float(2),sum( case evaluate when '1' then 1 when '2' then 1 when '3' then 1 when '4' then 1 else 0 end))/
convert(float(2),
sum( case evaluate when '1' then 1 else 0 end)+
sum( case evaluate when '2' then 1 else 0 end) +
sum( case evaluate when '3' then 1 else 0 end) +
sum( case evaluate when '4' then 1 else 0 end) +
sum( case evaluate when '0' then 1 else 0 end) +
sum( case evaluate when '9' then 1 else 0 end) )*100) ),5)+'%'
end
as '客户评价率',case
convert(float(2),sum( case evaluate when '1' then 1 else 0 end)+sum( case evaluate when '2' then 1 else 0 end) +sum( case evaluate when '3' then 1 else 0 end) + sum( case evaluate when '4' then 1 else 0 end) )
when 0 then '0%'
else
left(convert(varchar,(convert(float(2),
sum( case evaluate when '1' then 1 else 0 end) )/
convert(float(2),
sum( case evaluate when '1' then 1 else 0 end)+
sum( case evaluate when '2' then 1 else 0 end) +
sum( case evaluate when '3' then 1 else 0 end) +
sum( case evaluate when '4' then 1 else 0 end) )*100) ),5)+'%' end as '非常满意率',
case convert(float(2),
sum( case evaluate when '1' then 1 else 0 end)+
sum( case evaluate when '2' then 1 else 0 end) +
sum( case evaluate when '3' then 1 else 0 end) +
sum( case evaluate when '4' then 1 else 0 end) )
when 0 then '0%'
else left(convert(varchar,(convert(float(2),
sum( case evaluate when '2' then 1 else 0 end) )
/convert(float(2),
sum( case evaluate when '1' then 1 else 0 end)+
sum( case evaluate when '2' then 1 else 0 end) +
sum( case evaluate when '3' then 1 else 0 end) +
sum( case evaluate when '4' then 1 else 0 end) )*100) ),5)+'%' end as '满意率',
case convert(float(2),
sum( case evaluate when '1' then 1 else 0 end)+
sum( case evaluate when '2' then 1 else 0 end) +
sum( case evaluate when '3' then 1 else 0 end) +
sum( case evaluate when '4' then 1 else 0 end) )
when 0 then '0%'
else left(convert(varchar,(convert(float(2),
sum( case evaluate when '3' then 1 else 0 end) )/
convert(float(2),
sum( case evaluate when '1' then 1 else 0 end)+
sum( case evaluate when '2' then 1 else 0 end) +
sum( case evaluate when '3' then 1 else 0 end) +
sum( case evaluate when '4' then 1 else 0 end) )*100) ),5)+'%' end as '基本满意率',
case convert(float(2),
sum( case evaluate when '1' then 1 else 0 end)+
sum( case evaluate when '2' then 1 else 0 end) +
sum( case evaluate when '3' then 1 else 0 end) +
sum( case evaluate when '4' then 1 else 0 end) )
when 0 then '0%' else left(convert(varchar,(convert(float(2),
sum( case evaluate when '4' then 1 else 0 end) )/
convert(float(2),
sum( case evaluate when '1' then 1 else 0 end)+
sum( case evaluate when '2' then 1 else 0 end) +
sum( case evaluate when '3' then 1 else 0 end) +
sum( case evaluate when '4' then 1 else 0 end) )*100) ),5)+'%' end as '不满意率'
from history a
left join placeInfo b
on b.placeNo=a.placeNo
Where a.placeno = @placeno and getnumtime between @getnumtimeBegin and @getnumtimeEnd and dealtype='1'
group by b.placeName
--endUNION ALL--begin
select
count(*) as '叫号数',
sum(case dealtype when '0' then 1 else 0 end) as '空号数',
count(*)-sum(case dealtype when '0' then 1 else 0 end) as '业务量',
placename as '网点名称'from history c
left join placeinfo d
on c.placeNo=d.placeNo
Where c.placeno = @placeno and getnumtime between @getnumtimeBegin and @getnumtimeEnd
group by d.placeName
end
然后加上UNION ALL即可.
优化的话,
建议使用中间表来完成
set QUOTED_IDENTIFIER ON
goCREATE PROCEDURE [dbo].[getPlaceEvaluate]
@placeno varchar(10),@getnumtimeBegin datetime, @getnumtimeEnd datetime
as
begin select b.placeName,
sum( case evaluate when '1' then 1 else 0 end) as '非常满意',
sum( case evaluate when '2' then 1 else 0 end) as '满意',
sum( case evaluate when '3' then 1 else 0 end) as '基本满意',
sum( case evaluate when '4' then 1 else 0 end) as '不满意',
sum( case evaluate when '0' then 1 else 0 end) as '未发起',
sum( case evaluate when '9' then 1 else 0 end) as '未评价'
into #
from history a
left join placeInfo b
on b.placeNo=a.placeNo
Where a.placeno = @placeno and getnumtime between @getnumtimeBegin and @getnumtimeEnd and dealtype='1'
group by b.placeName
select [非常满意] +[满意]+[基本满意]+[不满意] as '评价人数',
[非常满意],[满意], [基本满意],[不满意],[未发起],[未评价],
case when [非常满意] +[满意]+[基本满意]+[不满意]+[未发起]+[未评价] = 0 then '0%'
else right(([非常满意] +[满意]+[基本满意]+[不满意])*1.0/([非常满意] +[满意]+[基本满意]+[不满意]+[未发起]+[未评价])*100,2)+'%' end '客户评价率',
case when [非常满意] +[满意]+[基本满意]+[不满意] = 0 then '0%'
else right([非常满意]*1.0/([非常满意] +[满意]+[基本满意]+[不满意])*100,2)+'%' end '非常满意率',
case when [非常满意] +[满意]+[基本满意]+[不满意] = 0 then '0%'
else right([满意]*1.0/([非常满意] +[满意]+[基本满意]+[不满意])*100,2)+'%' end '满意率',
case when [非常满意] +[满意]+[基本满意]+[不满意] = 0 then '0%'
else right([基本满意]*1.0/([非常满意] +[满意]+[基本满意]+[不满意])*100,2)+'%' end '基本满意率',
case when [非常满意] +[满意]+[基本满意]+[不满意] = 0 then '0%'
else right([不满意率]*1.0/([非常满意] +[满意]+[基本满意]+[不满意])*100,2)+'%' end '不满意率',
from #
UNION ALL
select
count(*) as '叫号数',
sum(case dealtype when '0' then 1 else 0 end) as '空号数',
count(*)-sum(case dealtype when '0' then 1 else 0 end) as '业务量',
placename as '网点名称', null,null,null,null,null,null,null,null
from history c
left join placeinfo d
on c.placeNo=d.placeNo
Where c.placeno = @placeno and getnumtime between @getnumtimeBegin and @getnumtimeEnd
group by d.placeName
end
else right([不满意]*1.0/([非常满意] +[满意]+[基本满意]+[不满意])*100,2)+'%' end '不满意率', --勘误,多了个率
@placeno varchar(10)
,@getnumtimeBegin datetime
,@getnumtimeEnd datetime
as
begin declare @tmp_static table (
[PlaceNo] [varchar](10)
,[DealType] [int]
,[Evaluate] [varchar](4)
,Cnt [int]
)
insert into @tmp_static
select
[PlaceNo]
,[dealtype] -- 0:'空号'| 1:'有效'
,[Evaluate] -- 评价 [0:'未发起',1:'非常满意',2:'满意',3:'基本满意',4:'不满意',9:'未评价']
,Cnt = count(1)
from history a
Where a.placeno = @placeno
and getnumtime between @getnumtimeBegin and @getnumtimeEnd
and dealtype='1'
group by
[PlaceNo]
,[dealtype]
,[Evaluate] select
placeName
as '网点名称'
,sum(case when evaluate between '1' and '4' then Cnt * dealtype else 0 end)
as '评价人数'
,sum(case evaluate when '1' then Cnt * dealtype else 0 end)
as '非常满意'
,sum(case evaluate when '2' then Cnt * dealtype else 0 end)
as '满意'
,sum(case evaluate when '3' then Cnt * dealtype else 0 end)
as '基本满意'
,sum(case evaluate when '4' then Cnt * dealtype else 0 end)
as '不满意'
,sum(case evaluate when '0' then Cnt * dealtype else 0 end)
as '未发起'
,sum(case evaluate when '9' then Cnt * dealtype else 0 end)
as '未评价'
,case sum(Cnt * dealtype) when 0 then 0 else
100 * sum(case when evaluate between '1' and '4' then Cnt * dealtype else 0 end)
/ sum(Cnt * dealtype)
end
as '客户评价率'
,case sum(Cnt * dealtype) when 0 then 0 else
100 * sum(case evaluate when '1' then Cnt * dealtype else 0 end)/
/ sum(Cnt * dealtype)
end
as '非常满意率'
,case sum(Cnt * dealtype) when 0 then 0 else
100 * sum(case evaluate when '2' then Cnt * dealtype else 0 end)/
/ sum(Cnt * dealtype)
end
as '满意率'
,case sum(Cnt * dealtype) when 0 then 0 else
100 * sum(case evaluate when '3' then Cnt * dealtype else 0 end)/
/ sum(Cnt * dealtype)
end
as '基本满意率'
,case sum(Cnt * dealtype) when 0 then 0 else
100 * sum(case evaluate when '4' then Cnt * dealtype else 0 end)/
/ sum(Cnt * dealtype)
end
as '不满意率'
,sum(Cnt)
as '叫号数'
,sum(case dealtype when '0' then Cnt else 0 end)
as '空号数'
,sum(case dealtype when '1' then Cnt else 0 end)
as '业务量'
from @tmp_static a
left join placeinfo b on b.placeNo=a.placeNo
group by
placeName
end
go
计算结果也不正确,非常满意率(等)=非常满意(等)/评价人数
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[getPlaceEvaluate2]
@placeno varchar(10)
,@getnumtimeBegin datetime
,@getnumtimeEnd datetimeas
begin
declare @pname varchar(50),@jiaohao int,@konghao int,@yewuliang int,--定义用来保存表字段值的变量
@pjrs int,@fcmy int,@my int,@jbmy int,@bmy int,@wfq int,@wpj int,@pjl int,@fcmyl int,
@myl int,@jbmyl int,@bmyl int
declare @rst table (--定义汇总结果表
[PlaceName] [varchar](50),
[jiaohao] [int],
[konghao] [int],
[yewuliang] [int],
[pingjia] [int],
[fcmy] [int],
[my] [int],
[jbmy] [int],
[bmy] [int],
[wfq] [int],
[wpj] [int],
[pjl] [int],
[fcmyl] [int],
[myl] [int],
[jbmyl] [int],
[bmyl] [int]
)
declare first cursor for --定义游标一
select
placeName
as '网点名称',
sum(case when evaluate between '1' and '4' then 1 else 0 end)
as '评价人数'
,sum(case evaluate when '1' then 1 else 0 end)
as '非常满意'
,sum(case evaluate when '2' then 1 else 0 end)
as '满意'
,sum(case evaluate when '3' then 1 else 0 end)
as '基本满意'
,sum(case evaluate when '4' then 1 else 0 end)
as '不满意'
,sum(case evaluate when '0' then 1 else 0 end)
as '未发起'
,sum(case evaluate when '9' then 1 else 0 end)
as '未评价'
,case
sum(case when evaluate between '1' and '4' then 1 else 0 end)+
sum(case evaluate when '9' then 1 else 0 end)+
sum(case evaluate when '0' then 1 else 0 end)
when 0 then 0 else
100 * sum(case when evaluate between '1' and '4' then 1 else 0 end)
/ sum(case when evaluate between '1' and '4' then 1 else 0 end)+sum(case evaluate when '9' then 1 else 0 end)+sum(case evaluate when '0' then 1 else 0 end)
end
as '客户评价率'
,case sum(case when evaluate between '1' and '4' then 1 else 0 end) when 0 then 0 else
100 * sum(case evaluate when '1' then 1 else 0 end)/
sum(case when evaluate between '1' and '4' then 1 else 0 end)
end
as '非常满意率'
,case sum(case when evaluate between '1' and '4' then 1 else 0 end)when 0 then 0 else
100 * sum(case evaluate when '2' then 1 else 0 end)/
sum(case when evaluate between '1' and '4' then 1 else 0 end)
end
as '满意率'
,case sum(case when evaluate between '1' and '4' then 1 else 0 end) when 0 then 0 else
100 * sum(case evaluate when '3' then 1 else 0 end)/
sum(case when evaluate between '1' and '4' then 1 else 0 end)
end
as '基本满意率'
,case sum(case when evaluate between '1' and '4' then 1 else 0 end) when 0 then 0 else
100 * sum(case evaluate when '4' then 1 else 0 end)/
sum(case when evaluate between '1' and '4' then 1 else 0 end)
end
as '不满意率'
from history a left join placeinfo b on a.placeNo=b.placeNo
Where a.placeno = @placeno
and getnumtime between @getnumtimeBegin and @getnumtimeEnd
and dealtype='1'
group by
placeNameopen first--打开游标
while(@@fetch_status=0)
begin
fetch next from first into @pname,@pjrs,@fcmy,@my,@jbmy,@bmy,@wfq,@wpj,@pjl,@fcmyl,@myl,@jbmyl,@bmyl
insert into @rst --向汇总表里插入数据
(
[PlaceName],[pingjia],[fcmy],[my],[jbmy],[bmy],[wfq],[wpj],[pjl],[fcmyl],[myl],[jbmyl],[bmyl]
) values
(
@pname, @pjrs,@fcmy,@my,@jbmy,@bmy,@wfq,@wpj,@pjl,@fcmyl,@myl,@jbmyl,@bmyl
)
end
close first--关闭游标一
deallocate first--释放游标一declare tr cursor for--定义游标二
(
select
placeName as '网点名称',
count(*) as '叫号数'
,sum(case dealtype when '0' then 1 else 0 end)
as '空号数'
,sum(case dealtype when '1' then 1 else 0 end)
as '业务量'
from history c
left join placeinfo d on d.placeNo=c.placeNo
Where c.placeno = @placeno
and getnumtime between @getnumtimeBegin and @getnumtimeEnd
group by
placeName
)open tr--打开游标二
while(@@fetch_status=0)
begin
fetch next from tr into @pname,@jiaohao,@konghao,@yewuliang begin
update @rst --更新汇总表里面的每条数据
set
[jiaohao]=@jiaohao,
[konghao]=@konghao,
[yewuliang]=@yewuliang
where [placename]=@pname
end
end
close tr
deallocate trselect * from @rst--返回统计结果,但是实际上并没有任何数据返回
end
正确的解决办法:把dealtype的判断放到未发起评价的判断里面就OK了。。
sql语句:select b.placename as '网点名称',count(*) as '业务量',
sum(case dealtype when '0' then 1 else 0 end) as '空号数',
sum( case evaluate when '1' then 1 else 0 end)+
sum( case evaluate when '2' then 1 else 0 end) +
sum( case evaluate when '3' then 1 else 0 end) +
sum( case evaluate when '4' then 1 else 0 end) as '评价人数',
sum( case evaluate when '1' then 1 else 0 end) as '非常满意',
sum( case evaluate when '2' then 1 else 0 end) as '满意',
sum( case evaluate when '3' then 1 else 0 end) as '基本满意',
sum( case evaluate when '4' then 1 else 0 end) as '不满意',
sum( case when evaluate in ('0') and dealtype ='1' then 1 else 0 end) as '未发起',
sum( case when evaluate in ('9') and dealtype ='1' then 1 else 0 end) as '未评价',
case convert(float(2),
sum( case evaluate when '1' then 1 else 0 end)+
sum( case evaluate when '2' then 1 else 0 end) +
sum( case evaluate when '3' then 1 else 0 end) +
sum( case evaluate when '4' then 1 else 0 end) +
sum( case evaluate when '0' then 1 else 0 end) +
sum( case evaluate when '9' then 1 else 0 end) )
when 0 then '0%'
else LEFT(
convert(varchar,(convert(float(2),
sum( case evaluate when '1' then 1 when '2' then 1 when '3' then 1 when '4' then 1 else 0 end))/
convert(float(2),
sum( case evaluate when '1' then 1 else 0 end)+
sum( case evaluate when '2' then 1 else 0 end) +
sum( case evaluate when '3' then 1 else 0 end) +
sum( case evaluate when '4' then 1 else 0 end) +
sum( case evaluate when '0' then 1 else 0 end) +
sum( case evaluate when '9' then 1 else 0 end) )*100) ),5)+'%' end as '客户评价率',
case convert(float(2),
sum( case evaluate when '1' then 1 else 0 end)+
sum( case evaluate when '2' then 1 else 0 end) +
sum( case evaluate when '3' then 1 else 0 end) +
sum( case evaluate when '4' then 1 else 0 end) )
when 0 then '0%'
else left(convert(varchar,(convert(float(2),
sum( case evaluate when '1' then 1 else 0 end) )/
convert(float(2),
sum( case evaluate when '1' then 1 else 0 end)+
sum( case evaluate when '2' then 1 else 0 end) +
sum( case evaluate when '3' then 1 else 0 end) +
sum( case evaluate when '4' then 1 else 0 end) )*100) ),5)+'%' end as '非常满意率',
case convert(float(2),
sum( case evaluate when '1' then 1 else 0 end)+
sum( case evaluate when '2' then 1 else 0 end) +
sum( case evaluate when '3' then 1 else 0 end) +
sum( case evaluate when '4' then 1 else 0 end) )
when 0 then '0%'
else left(convert(varchar,(convert(float(2),
sum( case evaluate when '2' then 1 else 0 end) )/
convert(float(2),
sum( case evaluate when '1' then 1 else 0 end)+
sum( case evaluate when '2' then 1 else 0 end) +
sum( case evaluate when '3' then 1 else 0 end) +
sum( case evaluate when '4' then 1 else 0 end) )*100) ),5)+'%' end as '满意率',
case convert(float(2),sum( case evaluate when '1' then 1 else 0 end)+
sum( case evaluate when '2' then 1 else 0 end) +
sum( case evaluate when '3' then 1 else 0 end) +
sum( case evaluate when '4' then 1 else 0 end) )
when 0 then '0%'
else left(convert(varchar,(convert(float(2),
sum( case evaluate when '3' then 1 else 0 end) )/
convert(float(2),
sum( case evaluate when '1' then 1 else 0 end)+
sum( case evaluate when '2' then 1 else 0 end) +
sum( case evaluate when '3' then 1 else 0 end) +
sum( case evaluate when '4' then 1 else 0 end) )*100) ),5)+'%' end as '基本满意率',
case convert(float(2),
sum( case evaluate when '1' then 1 else 0 end)+
sum( case evaluate when '2' then 1 else 0 end) +
sum( case evaluate when '3' then 1 else 0 end) +
sum( case evaluate when '4' then 1 else 0 end) )
when 0 then '0%'
else left(convert(varchar,(convert(float(2),
sum( case evaluate when '4' then 1 else 0 end) )/
convert(float(2),
sum( case evaluate when '1' then 1 else 0 end)+
sum( case evaluate when '2' then 1 else 0 end) +
sum( case evaluate when '3' then 1 else 0 end) +
sum( case evaluate when '4' then 1 else 0 end) )*100) ),5)+'%' end as '不满意率'
from
history a
left join placeInfo b
on b.placeNo=a.placeNo
--%Where
group by b.placeName