问题描述
是否可以在语句的情况下使用列号对表进行排序?这是代码
Is it possible to order table with column number in case when statement? Here is the code
;WITH Table1 AS (....), Table2 AS (....) INSERT INTO #temp SELECT TOP 50 one.id, one.sales, two.count, one.sales * two.count as 'Volume %' FROM Table1 one, INNER JOIN Table2 two on one.id = two.id ORDER BY CASE @order WHEN 1 THEN 7 ---Column number here since [Volume %] giving me an error WHEN 2 THEN 10 ---Column number END DESC
推荐答案
我做了一些测试并了解到,虽然直接 ORDER BY 可以引用列别名(即使是带有空格字符的别名),但 ORDER BY 中的 CASE 表达式不能.我从未在任何地方看到过这种记录,但我自己测试过.
I have done some testing and learned that while a straight ORDER BY can reference column aliases (even those with space characters), a CASE expression in an ORDER BY cannot. I've never seen this documented anywhere, but I have tested it myself.
别名中有特殊字符并不重要.如果你只做了:
It doesn't matter that you have a special character in your alias. If you had only done:
one.sales * two.count as Volume
您仍然会收到以下错误:
You would still get an error with:
ORDER BY CASE @order WHEN 1 THEN Volume
我怀疑这与您不能使用列号的原因相同.
And I suspect that's the same reason why you can't use column numbers.
作为解决此问题的可靠方法,我的建议是:
My suggestion as a sure-fire way to fix this is:
ORDER BY CASE @order WHEN 1 THEN one.sales * two.count