日期:2014-05-18 浏览次数:20608 次
use sh2 select *,(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) as myd 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(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 group by a.serviceid,a.xm,a.zu ) as table1 order by zu, zs,myd desc
declare @t table (myd numeric(17,16)) insert into @t select 0.538814246439498 union all select 0.157517201835248 union all select 0.636789155319799 union all select 0.0917430894309527 union all select 0.390864313523449 union all select 0.0197113645979016 union all select 0.691648790372575 union all select 0.771912013731017 union all select 0.86527561193655 union all select 0.412721545043269 union all select 0.304729819221732 union all select 0.494764308426483 --不要去掉这个别名a 否则结果会发生变化的 select ltrim(cast(myd*100 as decimal(18,2)))+'%' as myd from @t a order by a.myd desc /* myd ------------------------------------------ 86.53% 77.19% 69.16% 63.68% 53.88% 49.48% 41.27% 39.09% 30.47% 15.75% 9.17% 1.97% */
------解决方案--------------------
别名 不能在同一条语句中显示使用。