原表
排序后为
要求对表排序:
排序规则:
1. 按照列 Last排序
2. 数字类型的排在一块,按升或降排序
3. 数字类型小数点保留两位,前面要加$, 如$23.98
4. 字符类型的排在一块,按升或降排序
5. 能够对数字型和字符型的排序
方法一:对5的要求并不灵活,只用UNION
SELECT FIRST,ALERT FROM
(
SELECT TOP 100 FIRST,('$'+CONVERT(varchar,CONVERT(money,Last,1)) ) AS ALERT
FROM NUM
WHERE PATINDEX('%[^0-9|.]%',Last)=0
ORDER BY CONVERT(float,LAST)
)AS M
UNION ALL
SELECT FIRST,ALERT FROM
(
SELECT TOP 100 FIRST,( Last ) AS ALERT
FROM NUM
WHERE PATINDEX('%[^0-9|.]%',Last)<>0
ORDER BY ALERT
)AS N
注意:必须用UNION ALL, 不能只有UNION, 否则字符和数字型的会交叉在一起
方法二:采用UNION 和CASE,子查询中多添加列来最字符和数字型的排序
SELECT FIRST,ALERT FROM
(
SELECT 1 AS YAO,FIRST,('$'+CONVERT(varchar,CONVERT(money,Last,1)) ) AS ALERT
FROM NUM
WHERE PATINDEX('%[^0-9|.]%',Last)=0
UNION ALL
SELECT 2 AS YAO,FIRST,LAST AS ALERT
FROM NUM
WHERE PATINDEX('%[^0-9|.]%',Last)<>0
) AS U
ORDER BY YAO,
CASE WHEN YAO=1 THEN CONVERT(float,SUBSTRING(ALERT,2,LEN(ALERT)-1)) END,
CASE WHEN YAO=2 THEN ALERT END
关于UNION
如果UNION 联合的每一个查询有ODER BY则需要加TOP,
上述两个方法中SELECT 出的表后一定要有AS