问题描述
是否可以在语句的情况下使用列号对表进行排序?这是代码
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
这篇关于在语句的情况下按列号对表进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！