查询按字符串的数值大小排序问题
CREATE TABLE tb(Item varchar(10),Model varchar(10))
INSERT tb SELECT 'Table','10.5CM'
UNION ALL SELECT 'Table','10CM'
UNION ALL SELECT 'Table','12CM'
Go
--怎么查询得到以下结果:(Model列的数字长度不一定,相当于按数字大小排序)
Item Model
---------------------
Table10CM
Table10.5CM
Table12CM
---------------------------
select * from tb order by Model
以上语句只能得到:
Item Model
---------------------
Table10.5CM
Table10CM
Table12CM
[最优解释]
CREATE TABLE tb(Item varchar(10),Model varchar(10))
INSERT tb
SELECT 'Table','10.5CM'
UNION ALL SELECT 'Table','10CM'
UNION ALL SELECT 'Table','12CM'
select * from tb order by cast(replace(Model,'CM','') as decimal)
/*
Item Model
---------- ----------
Table 10CM
Table 10.5CM
Table 12CM
(3 row(s) affected)
*/