一条sql语句中的order by没有起作用

[wyumening]

是一个售后平台的问题，是在asp中，先用sql语句查询出结果，然后动态循环填充td并显示到页面上
sql语句如下

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

显示出的结果如下：
姓名 组别 总数 已完成 完成率 待反馈，处理中，评价，满意度
xxx xxx xxx xxx xxx xxx xxx xxx xxx



xxx表示相应的数据，myd表示满意度，就是客户对售后人员回答的问题的评价，分为非常满意，满意，一般，不满意，非常不满意，接近崩溃 等这六个，也就是sql语句中的pg1到pg6 , 姓名是售后人员的姓名（sql语句中的name)，组别表示售后人员的所属的组别（sql语句中的a.zu)，总数表示服务人员接收的客户提到的问题的总数(sql语句中的zs)，已完成表示客户提的问题已经处理完成的个数（sql语句中的ywc）,查询到的两个表，分别是service(服务人员）和wenti(问题）现在的需求是记录要跟据满意度排序，是在同一组的售后服务人员中找出满意度最高的然后排序，我试着用order by 发现无法按满意度排序，但是可以按组别和处理问题的总数排序，该怎么改才行呢？

[叶子]

满意度是什么类型的字段？

[wyumening]

(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 这就是满意度，.0238095238095这种样子的，decimal类型

[叶子]

既然是decimal类型的那么排序就应该是没有问题的呀？
如果给出测试数据，我可以帮你测试一下。

[叶子]

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 --前面两个字段都好使,只有myd 排序无效?

[Felixzhaowenzhong]

order by zu, zs,myd desc
你这样写的myd是 在基于zu,zs 降序的基础上，再按myd降序。前两个字段已经决定了后面的降序结构。
如果你只是想按照 组 和满意度来降序排序的话：改成 order by zu,myd desc。
也就是说 满意度排序时，必须等组 和 问题总数排序完成后才能在他们的基础上再排序。

[wyumening]

目前的问题就是这样，测试数据，不好意思没有

[叶子]

如果是先按组排序，再按满意度排序 应该是order by zu,myd desc。

[wyumening]

[Quote=引用 5 楼 的回复:]
order by zu, zs,myd desc
你这样写的myd是 在基于zu,zs 降序的基础上，再按myd降序。前两个字段已经决定了后面的降序结构。
如果你只是想按照 组 和满意度来降序排序的话：改成 order by zu,myd desc。
也就是说 满意度排序时，必须等组 和 问题总数排序完成后才能在他们的基础上再排序。
[/Quote]

还是有点问题，如何把输出的满意度字段的后面加上百分号？现在的输出格式是.0238095238095这样，不能变成像40.35%这样的格式

[叶子]

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%
*/

[ILOVE_ASPNET]

还是有点问题，如何把输出的满意度字段的后面加上百分号？现在的输出格式是.0238095238095这样，不能变成像40.35%这样的格式

对满意度这个字段做个计算列*100转换成 decimal 保留二位小数， 然后再拼上一个'%' 不知道是否是这个意思

[wyumening]

我在sql server中写成了这样 ：

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 myd1, cast(myd1*100 as decimal(15,2)) 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, myd desc

结果报错 列名“myd1"无效

[喜阳阳]

select *,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

[喜阳阳]

别名 不能在同一条语句中显示使用。

[勿勿]

外面加个括号

[叶子]

同一层的别名，不能直接在同一层使用。