order by charindex(a.dwmc,'咨询监理设计') asc,d.Zjgcszczsbh desc
order by case when charindex('咨询',a.dwmc )<>0 then -1 end desc, case when charindex('监理',a.dwmc )<>0 then -2 end desc, case when charindex('设计',a.dwmc )<>0 then -3 end desc, d.Zjgcszczsbh desc
order by case when charindex('咨询',a.dwmc )<>0 then 1 when charindex('监理',a.dwmc )<>0 then 2 when charindex('设计',a.dwmc )<>0 then 3 end, d.Zjgcszczsbh desc
order by case when charindex('咨询',a.dwmc )<>0 then '咨询' else -1 end desc, case when charindex('监理',a.dwmc )<>0 then '监理' else -1 end desc, case when charindex('设计',a.dwmc )<>0 then '设计'else -1 end desc, d.Zjgcszczsbh desc
order by case when charindex('咨询',a.dwmc )<>0 then -3 when charindex('监理',a.dwmc )<>0 then -2 when charindex('设计',a.dwmc )<>0 then -1 else 0 end, d.Zjgcszczsbh desc
不行,是顺序还不对吗?是不是你的DWMC中有可能出现“咨询”,也同时可能会有“监理”的情况啊?
它的顺序相当于order by -3,-2,-1,0,d.Zjgcszczsbh desc有没有别的方法呢?
order by case when charindex('咨询',a.dwmc )<>0 then -5 end desc, right(rtrim(b.fsf), 2) desc,c.gczjzxsre desc,d.Zjgcszczsbh desc, case when charindex('监理',a.dwmc )<>0 then -4 end desc, case when charindex('设计',a.dwmc )<>0 then -3 end desc, case when charindex('会计',a.dwmc )<>0 then -2 end desc这样可以排,但是到了“监理”的情况,就又会不行呀。
大意是:select a.* from tableA a left join (select '咨询'dwmc ,1 ord union all select '监理' dwmc,2 ord union all select '会计' dwmc,3 ord) o on a.dwmc=o.dwmc order by o.ord
order by case when charindex('咨询',a.dwmc )<>0 then 1 when charindex('监理',a.dwmc )<>0 then 2 when charindex('设计',a.dwmc )<>0 then 3 else 4 end, d.Zjgcszczsbh desc
case when charindex('咨询',a.dwmc )<>0 then -1 end desc,
case when charindex('监理',a.dwmc )<>0 then -2 end desc,
case when charindex('设计',a.dwmc )<>0 then -3 end desc,
d.Zjgcszczsbh desc
case when charindex('咨询',a.dwmc )<>0 then 1
when charindex('监理',a.dwmc )<>0 then 2
when charindex('设计',a.dwmc )<>0 then 3 end,
d.Zjgcszczsbh desc
case when charindex('咨询',a.dwmc )<>0 then '咨询' else -1 end desc,
case when charindex('监理',a.dwmc )<>0 then '监理' else -1 end desc,
case when charindex('设计',a.dwmc )<>0 then '设计'else -1 end desc,
d.Zjgcszczsbh desc
case when charindex('咨询',a.dwmc )<>0 then -3
when charindex('监理',a.dwmc )<>0 then -2
when charindex('设计',a.dwmc )<>0 then -1
else 0
end,
d.Zjgcszczsbh desc
是不是你的DWMC中有可能出现“咨询”,也同时可能会有“监理”的情况啊?这种情况是有,但不会影响什么的,它首先按咨询排序,没错,可是第二个,他不按d.Zjgcszczsbh desc
来排呀?
case when charindex('咨询',a.dwmc )<>0 then -5 end desc,
right(rtrim(b.fsf), 2) desc,c.gczjzxsre desc,d.Zjgcszczsbh desc,
case when charindex('监理',a.dwmc )<>0 then -4 end desc,
case when charindex('设计',a.dwmc )<>0 then -3 end desc,
case when charindex('会计',a.dwmc )<>0 then -2 end desc这样可以排,但是到了“监理”的情况,就又会不行呀。
from tableA a
left join (select '咨询'dwmc ,1 ord union all
select '监理' dwmc,2 ord union all
select '会计' dwmc,3 ord) o
on a.dwmc=o.dwmc
order by o.ord
order by
case when charindex('咨询',a.dwmc )<>0 then 1
when charindex('监理',a.dwmc )<>0 then 2
when charindex('设计',a.dwmc )<>0 then 3
else 4
end,
d.Zjgcszczsbh desc