在语句的情况下按列号对表进行排序

2021-09-10 00:00:00 sql tsql sql-server

是否可以在语句的情况下使用列号对表进行排序?这是代码

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

相关文章