按指定顺序排序

[leon51]

https://bbs.csdn.net/topics/392565281

在上面的贴子中“Dear SQL”帮我实现了分组汇总，
现在想将结果按订单数量降序排列，但“其它”客户排在最后。
我这样写：

ORDER BY CASE WHEN customer ='其它' THEN 1 ELSE 0 END,qty DESC

提示
[Err] 42000 - [SQL Server]ORDER BY 子句中的列 "datalist.customer" 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。
请问应该如何修改?谢谢

[吉普赛的歌]

发完整 sql 吧， 不要搞个半截子

[leon51]

引用 1 楼 吉普赛的歌 的回复:

发完整 sql 吧， 不要搞个半截子

完整的如下所示：

with list as(
	select customer='a',qty=100 union all
	select customer='a',qty=88 union all
	select customer='b',qty=88 union all
	select customer='b',qty=99 union all
	select customer='c',qty=309 union all
	select customer='c',qty=58 union all
	select customer='d',qty=28 union all
	select customer='e',qty=88 union all
	select customer='f',qty=88
),datalist as(
	select customer,qty=sum(qty)
		,rid=ROW_NUMBER()over (order by sum(qty) desc)
	from list
	group by customer
)
select customer=case when rid between 1 and 3 then customer else '其它' end
	,qty=sum(qty)
from datalist
group by case when rid between 1 and 3 then customer else '其它' end
ORDER BY CASE WHEN customer = '其它' THEN 1 ELSE 0 END,qty DESC

[吉普赛的歌]

with list as(
	select customer='a',qty=100 union all
	select customer='a',qty=88 union all
	select customer='b',qty=88 union all
	select customer='b',qty=99 union all
	select customer='c',qty=309 union all
	select customer='c',qty=58 union all
	select customer='d',qty=28 union all
	select customer='e',qty=88 union all
	select customer='f',qty=88
),datalist as(
	select customer,qty=sum(qty)
		,rid=ROW_NUMBER()over (order by sum(qty) desc)
	from list
	group by customer
)
SELECT * FROM (
	select customer=case when rid between 1 and 3 then customer else '其它' end
		,qty=sum(qty)
	from datalist
	group by case when rid between 1 and 3 then customer else '其它' END
) AS t
ORDER BY CASE WHEN customer = '其它' THEN 1 ELSE 0 END,qty DESC