T-Sql中的按问题排序

所以下面的事情不起作用

DECLARE @DATA VARCHAR(20)

SET @DATA = ''

SELECT CASE WHEN @DATA IS NULL THEN NULL
            WHEN @DATA = ''    THEN 'Data is empty'
            WHEN 0=1           THEN 1
            END

更多解决方案: http://pranayamr.blogspot.com/2010/11/sql-server-casewhen-return-type.html

在order by子句中用逗号分隔列名。

ORDER BY 
        CASE WHEN @sortby = 'A1' THEN String2 END,
        CASE WHEN @sortby = 'B1' THEN String1 END,
        CASE WHEN @sortby = 'Date1' THEN Date1 END 

试试这个。。

DECLARE @sortby VARCHAR(10) 
SET @sortby='Date1' 

SELECT String1, String2, Date1   
FROM ( 
        SELECT 'A1', 'B1', CONVERT(datetime,'1 July 2010 00:01:00') 
        UNION ALL 
        SELECT 'A2', 'B2', CONVERT(datetime,'2 July 2010 00:02:00') 
        ) AS a(String1,String2,Date1) 
ORDER BY 
        CASE  
                WHEN @sortby = 'A1' THEN String2  
                WHEN @sortby = 'B1' THEN String1 
                WHEN @sortby = 'Date1' THEN convert(datetime,cast(Date1 as varchar(20))) 
        END 

必须将最后一个值强制转换为varchar,否则order by子句的类型将解释为日期:

DECLARE @sortby VARCHAR(10)
SET @sortby='A1'

SELECT String1, String2, Date1  
FROM (
        SELECT 'A1', 'B1', CONVERT(datetime,'1 July 2010 00:01:00')
        UNION ALL
        SELECT 'A2', 'B2', CONVERT(datetime,'2 July 2010 00:02:00')
        ) AS a(String1,String2,Date1)
ORDER BY
        CASE 
                WHEN @sortby = 'A1' THEN String2 
                WHEN @sortby = 'B1' THEN String1
                WHEN @sortby = 'Date1' THEN CONVERT(VARCHAR(10), Date1, 112)
        END