日期:2014-05-18 浏览次数:20555 次
use sh2 select *,(convert(varchar,(cast( (convert(decimal,pg1)*3+convert(decimal,pg2)*2 +convert(decimal,pg3)*1-convert(decimal,pg4)*1-convert(decimal,pg5)*2 -convert(decimal,pg6)*3)/ (convert(decimal,zs)*3)*100 as decimal(15,2))))+'%') as myd1,(cast( (convert(decimal,pg1)*3+convert(decimal,pg2)*2 +convert(decimal,pg3)*1-convert(decimal,pg4)*1-convert(decimal,pg5)*2 -convert(decimal,pg6)*3)/ (convert(decimal,zs)*3)*100 as decimal(15,2))) as myd, (convert(varchar,(convert(decimal(10,2),round(ywc*1.00/zs,2))*100))+'%') as wcl, (convert(varchar,(convert(decimal(10,2),round(yuqi*1.00/zs,2))*100))+'%') as yql from ( select a.serviceid, a.xm, a.zu, count(b.id) as zs, (select name from zu where id=a.zu) as name , (select count(wenti.id) from wenti where (typeid=4 ) and service=a.serviceid) as ywc, (select count(wenti.id) from wenti where (typeid<>4 ) and (typeid<>7) and service=a.serviceid) as clz, (select count(wenti.id) from wenti where (typeid=7 ) and service=a.serviceid) as dfk, ( select count(*) from wenti where service=a.serviceid and jhwctime<date7 and typeid<>6 and typeid<>5) as yuqi, (select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=1) as pg1, (select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=2) as pg2, (select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=3) as pg3, (select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=4)as pg4, (select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=5) as pg5, (select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=6) as pg6 from service as a left join wenti as b on a.serviceid=b.service where b.id>0 and b.adddate between '2012-3-1' and'2012-4-9' group by a.serviceid,a.xm,a.zu ) as table1 order by zu, myd desc
(select count(wenti.id) from wenti where (typeid=4 ) and service=a.serviceid and b.adddate between '2012-3-1' and'2012-4-9') as ywc,
(select name from zu where id=a.zu) as name , (select count(wenti.id) from wenti where (typeid=4 ) and service=a.serviceid) as ywc, (select count(wenti.id) from wenti where (typeid<>4 ) and (typeid<>7) and service=a.serviceid) as clz, (select count(wenti.id) from wenti where (typeid=7 ) and service=a.serviceid) as dfk, ( select count(*) from wenti where service=a.serviceid and jhwctime<date7 and typeid<>6 and typeid<>5) as yuqi, (select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=1) as pg1, (select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=2) as pg2, (select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=3) as pg3, (select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=4)as pg4, (select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=5) as pg5, (select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=6) as pg6
------解决方案--------------------
你的写法效率太低了,这样加上条件
use sh2 select *, (convert(varchar,(cast( (convert(decimal,pg1)*3+co