在SQL Server中按大小写顺序使用select语句中使用的函数时出错

Question

我有下面的表结构，我想使用case关键字按顺序使用select语句中使用的函数。但我得到的错误是FullName是无效列。

CREATE TABLE STUDENTS
(
     ID INT IDENTITY(1, 1) ,
     FirstName VARCHAR(50) ,
     LastName VARCHAR(50)
)

在学生表中插入一些数据

INSERT  INTO STUDENTS
VALUES  ( 'kamal', 'sharma' )
INSERT  INTO STUDENTS
VALUES  ( 'john', 'jack' )
INSERT  INTO STUDENTS
VALUES  ( 'Rahul', 'Thomson' )

此语句正在中工作

SELECT 
       ID
     , FirstName + ' ' + LastName AS FullName
FROM STUDENTS
ORDER BY FullName ASC

我需要这个，但它说FullName是无效列。

DECLARE @VAL INT  
SET @VAL = 1
SELECT ID
    ,FirstName + ' ' + LastName AS FullName
FROM STUDENTS
ORDER BY CASE 
        WHEN @VAL = 1
            THEN FullName
        END ASC
    ,CASE 
        WHEN @VAL = 2
            THEN FullName
        END DESC
Msg 207, Level 16, State 1, Line 10
Invalid column name 'FullName'.
Msg 207, Level 16, State 1, Line 14
Invalid column name 'FullName'.

提前感谢您的帮助。

Answer 1

在SELECT中定义的列别名不能在相应ORDER BY的表达式中使用。

要么重复基本定义。

SELECT ID
    ,FirstName + ' ' + LastName AS FullName
FROM STUDENTS
ORDER BY CASE 
        WHEN @VAL = 1
            THEN FirstName + ' ' + LastName
        END ASC
    ,CASE 
        WHEN @VAL = 2
            THEN FirstName + ' ' + LastName
        END DESC

或者在不同级别定义别名。例如派生表或CTE。

WITH T
     AS (SELECT ID,
                FirstName + ' ' + LastName AS FullName
         FROM   STUDENTS)
SELECT *
FROM   T
ORDER  BY CASE
            WHEN @VAL = 1 THEN FullName
          END ASC,
          CASE
            WHEN @VAL = 2 THEN FullName
          END DESC 

Answer 2

将其更改为：

SELECT ID
    ,FirstName + ' ' + LastName AS FullName
FROM STUDENTS
ORDER BY CASE 
        WHEN @VAL = 1
            THEN FirstName + ' ' + LastName
        END ASC
    ,CASE
        WHEN @VAL = 2
            THEN FirstName + ' ' + LastName
        END DESC

Answer 3

试试这个-

SELECT 
       ID
     , FullName
FROM (
     SELECT 
            ID
          , FullName = FirstName + ' ' + LastName
     FROM dbo.STUDENTS
) t
ORDER BY 
      CASE WHEN @VAL = 1 THEN FullName END
    , CASE WHEN @VAL = 2 THEN FullName END DESC

另一种变体：

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = '
SELECT 
       ID
     , FullName
FROM (
     SELECT 
            ID
          , FullName = FirstName + '' '' + LastName
     FROM dbo.STUDENTS
) t
ORDER BY ' + 
     CASE WHEN @VAL = 1 
          THEN 'FullName' 
          ELSE 'FullName DESC' 
     END
EXEC sys.sp_executesql @SQL

Answer 4

将订单按部分包裹在实际查询周围：

SELECT ID, FullName
FROM (
    SELECT  ID, 
            FirstName + ' ' + LastName AS FullName, 
            CASE WHEN @VAL = 1 THEN FullName END AS OrderNumber1,
            CASE WHEN @VAL = 2 THEN FullName END AS OrderNumber2
    FROM STUDENTS 
) data
ORDER BY OrderNumber1 ASC, OrderNumber2 DESC

Answer 5

只需尝试以下脚本：

 SELECT ID
            ,FirstName + ' ' + LastName AS FullName
        FROM STUDENTS
        ORDER BY FirstName + ' ' + LastName ASC