問題描述
是否可以在語句的情況下使用列號對表進行排序?這是代碼
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
這篇關于在語句的情況下按列號對表進行排序的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！