close

(把字串轉數字排序)欄位varchar型態，用數字型態排序，使用CAST()函式


drop table table_a;
CREATE TABLE table_a (
id bigint(10) NOT NULL AUTO_INCREMENT,
number varchar(255)DEFAULT NULL,
PRIMARY KEY (id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
insert into table_a(number) VALUES(1),(10),(5),(3),(2),(11),(14),(12),(13);


SELECT * FROM table_a AS a ORDER BY CAST(a.number AS UNSIGNED);

SELECT * FROM table_a AS a ORDER BY CAST(a.number AS decimal(10,0))



 
















其它文章

arrow
arrow
    文章標籤
    db sql 用法 程式 測試 varchar cast 型態 排序 unsigned increment default myisam table mysql
    全站熱搜

    PG Levin Li 發表在 痞客邦 留言(0) 人氣()

    E-mail轉寄