SELECT a.columnOne,b.columnOne
FROM TableOne a
INNER JOIN TableTwo b
ON a.id = b.id
WHERE a.Value = 0
ORDER BY a.ColumnOne,b.ColumnTwo
‘bug’是TableTwo没有名为columnTwo的列(在ORDER BY子句中使用),但运行正常.至少它在一个数据库上,另一个抱怨它应该.但我确定没有TableTwo.columnTwo.
值得一提的是,TableOne DOES有一个名为columnTwo的列.
这是一个很容易解决的问题,但是这已经让我觉得这已经存在了很长时间没有任何问题.知道会发生什么吗？ (或者我可以提供更多信息？)
解决方法
WHEN binding the column references in the ORDER BY list to the columns
defined in the SELECT list,column ambiguities are ignored and column
prefixes are sometimes ignored. This can cause the result set to
return in an unexpected order. For example,an ORDER BY clause with a
single two-part column (.) that is used as a
reference to a column in a SELECT list is accepted,but the table
alias is ignored. Consider the following query. SELECT c1 = -c1 FROM
t_table AS x ORDER BY x.c1 When executed,the column prefix is ignored
in the ORDER BY. The sort operation does not occur on the specified
source column (x.c1) as expected; instead it occurs on the derived c1
column that is defined in the query. The execution plan for this query
shows that the values for the derived column are computed first and
then the computed values are sorted
来源 – “MSDN”
