SQL语句——按姓氏排序

--按姓氏笔画排序
select StaffDisplayName from DAS_Staff order by StaffDisplayName
Collate Chinese_PRC_Stroke_ci_as


---按姓氏字母排序
SELECT ISNULL(b.PY, UPPER(LEFT(a.StaffDisplayName, 1))) AS py, a.StaffDisplayName
FROM (SELECT 'a3' AS StaffDisplayName
UNION
SELECT StaffDisplayName
FROM DAS_Staff) AS a LEFT OUTER JOIN
(SELECT 'A' AS PY, N'驁' AS word, N'啊' AS sword
UNION
SELECT 'B' AS Expr1, N'簿' AS Expr2, N'驁' AS Expr3
UNION
SELECT 'C' AS Expr1, N'錯' AS Expr2, N'簿' AS Expr3
UNION
SELECT 'D' AS Expr1, N'鵽' AS Expr2, N'錯' AS Expr3
UNION
SELECT 'E' AS Expr1, N'樲' AS Expr2, N'鵽' AS Expr3
UNION
SELECT 'F' AS Expr1, N'鰒' AS Expr2, N'樲' AS Expr3
UNION
SELECT 'G' AS Expr1, N'腂' AS Expr2, N'鰒' AS Expr3
UNION
SELECT 'H' AS Expr1, N'夻' AS Expr2, N'腂' AS Expr3
UNION
SELECT 'J' AS Expr1, N'攈' AS Expr2, N'夻' AS Expr3
UNION
SELECT 'K' AS Expr1, N'穒' AS Expr2, N'攈' AS Expr3
UNION
SELECT 'L' AS Expr1, N'鱳' AS Expr2, N'穒' AS Expr3
UNION
SELECT 'M' AS Expr1, N'旀' AS Expr2, N'鱳' AS Expr3
UNION
SELECT 'N' AS Expr1, N'桛' AS Expr2, N'旀' AS Expr3
UNION
SELECT 'O' AS Expr1, N'漚' AS Expr2, N'桛' AS Expr3
UNION
SELECT 'P' AS Expr1, N'曝' AS Expr2, N'漚' AS Expr3
UNION
SELECT 'Q' AS Expr1, N'囕' AS Expr2, N'曝' AS Expr3
UNION
SELECT 'R' AS Expr1, N'鶸' AS Expr2, N'囕' AS Expr3
UNION
SELECT 'S' AS Expr1, N'蜶' AS Expr2, N'鶸' AS Expr3
UNION
SELECT 'T' AS Expr1, N'籜' AS Expr2, N'蜶' AS Expr3
UNION
SELECT 'W' AS Expr1, N'鶩' AS Expr2, N'籜' AS Expr3
UNION
SELECT 'X' AS Expr1, N'鑂' AS Expr2, N'鶩' AS Expr3
UNION
SELECT 'Y' AS Expr1, N'韻' AS Expr2, N'鑂' AS Expr3
UNION
SELECT 'Z' AS Expr1, N'咗' AS Expr2, N'韻' AS Expr3) AS b ON LEFT(a.StaffDisplayName, 1) BETWEEN b.sword AND b.word
WHERE (a.StaffDisplayName <> 'a3')