我正在尝试使用case语句确定外部select语句中的列的顺序来确定要排序的字段。由于某些原因,我无法使用在外部选择中创建的列。如果我尝试不使用case语句按该列排序,那么它将起作用。我究竟做错了什么?
伪代码来说明问题:
DECLARE @orderBy varchar(8)
SET @orderBy = 'rating'
SELECT
CASE
WHEN rating is not null THEN 1
ELSE 0
END as hasRating,
*
FROM
(SELECT col1, ...) AS table1
LEFT OUTER JOIN
(SELECT col1, rating, ...) AS table2
ON table1.col1 = table2.col2
--causes error "Invalid column name 'hasRating'"
ORDER BY
CASE WHEN @orderBy = 'rating' THEN hasRating END DESC,
CASE WHEN @orderby = 'something else' THEN ...
--works
ORDER BY hasRating desc
这会引发错误:
ORDER BY
CASE WHEN @orderBy = 'rating' THEN CASE WHEN rating is not null THEN 1 ELSE 0 END END DESC,
CASE WHEN @orderby = 'something else' THEN ...
好像CASE
在ORDER
别名可用之前进行评估。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句