Stored-Procedures
具有多列和不同排序選項的 CASE ORDER BY
我試圖
CASE ORDER BY
在儲存過程中使用 T-SQL,我將 @OrderBy 參數作為 TINYINT 傳遞。
@Orderby = 1 Then Date column should be ASC
@Orderby = 2 Then Date column should be DESC
我的問題是:當我為該參數傳遞 2 時,如何讓日期列對 desc 進行排序,並在同一個 CASE ORDER BY 語句中讓字元串列按 asc 排序?
這就是我現在所擁有的
CASE ORDER BY
ORDER BY CASE WHEN @OrderBy = 1 THEN CONVERT(NVARCHAR(30) , ccd.CertEndDate) + tp.LastName + tp.FirstName END , CASE WHEN @OrderBy = 2 THEN CONVERT(NVARCHAR(30) , ccd.CertEndDate) + tp.LastName + tp.FirstName END DESC
此程式碼解析並返回一個沒有錯誤的結果集,但是當我更喜歡 ccd.CertEndDate DESC , tp.LastName ASC , tp.FirstName ASC 時,第二個 CASE ORDER BY 全部按 DESC 排序順序
提前致謝。
再分解一下:
ORDER BY CASE WHEN @orderby = 1 THEN CONVERT(NVARCHAR(30) , ccd.CertEndDate) END ASC, CASE WHEN @orderby = 2 THEN CONVERT(NVARCHAR(30) , ccd.CertEndDate) END DESC, tp.lastname ASC, tp.firstname ASC
您只需要更改第一個欄位的排序順序,因此不要將其他欄位包含在
CASE
.應該注意的是,我們不包含
ELSE
for eachCASE
,這意味著任何其他值都將返回NULL
並從ORDER BY
.
除了 JNK 的回答,您還可以考慮:
DECLARE @Example TABLE ( first_name NVARCHAR(50) NOT NULL, last_name NVARCHAR(50) NOT NULL, cert_end_date DATE NOT NULL, other_columns NCHAR(100) NOT NULL DEFAULT (N'') UNIQUE (cert_end_date ASC, first_name, last_name), UNIQUE (cert_end_date DESC, first_name, last_name) ) INSERT @Example (first_name, last_name, cert_end_date) VALUES ('a', 'w', '2008-12-31'), ('b', 'x', '2009-12-31'), ('c', 'y', '2010-12-31'), ('d', 'z', '2011-12-31') DECLARE @order_date_ascending BIT = CONVERT(BIT, 'true') -- 1. May require an explicit sort despite useful indexes SELECT e.first_name, e.last_name, e.cert_end_date FROM @Example AS e ORDER BY CASE WHEN @order_date_ascending = 1 THEN e.cert_end_date END ASC, CASE WHEN @order_date_ascending = 0 THEN e.cert_end_date END DESC, e.first_name ASC, e.last_name ASC -- 2. Conditional statements IF @order_date_ascending = CONVERT(BIT, 'true') BEGIN SELECT e.first_name, e.last_name, e.cert_end_date FROM @Example AS e ORDER BY e.cert_end_date ASC, e.first_name ASC, e.last_name ASC END ELSE IF @order_date_ascending = CONVERT(BIT, 'false') BEGIN SELECT e.first_name, e.last_name, e.cert_end_date FROM @Example AS e ORDER BY e.cert_end_date DESC, e.first_name ASC, e.last_name ASC END -- 3. Union All & Start-up Filters SELECT * FROM ( SELECT TOP (9223372036854775807) e.first_name, e.last_name, e.cert_end_date FROM @Example AS e WHERE @order_date_ascending = CONVERT(BIT, 'true') ORDER BY e.cert_end_date ASC, e.first_name ASC, e.last_name ASC ) AS sort_asc UNION ALL SELECT * FROM ( SELECT TOP (9223372036854775807) e.first_name, e.last_name, e.cert_end_date FROM @Example AS e WHERE @order_date_ascending = CONVERT(BIT, 'false') ORDER BY e.cert_end_date DESC, e.first_name ASC, e.last_name ASC ) AS sort_desc
第一種和第三種方法可以受益於 SQL Server 2008 SP1 CU5 上可用的參數嵌入優化,如果發生語句級編譯,或者使用
OPTION (RECOMPILE)
查詢提示強制發生。有關詳細資訊,請參閱此MSDN 部落格條目。第三種方法不需要每次都編譯,因為計劃中的過濾器操作符有一個啟動表達式。每個過濾器下方的計劃子樹僅在條件評估為真時執行。