Mysql中有varchar类型字段,并且为:123,456 形式,需要对其进行排序,并根据条件筛选出前5条
表名:table_a
t_id(int) | t_year(int) | t_value(varchar) |
---|---|---|
1 | 2019 | 123,456 |
使用到的函数:REPLACE(str,from_str,to_str)
这里把t_value值为:123,456中的”,” 去掉,也就是替换成空。
SQL:
SELECT REPLACE(t_value,",","") FROM table_a;
使用到的函数: CAST(Filed AS UNSIGNED INTEGER)
这里需要先把t_value中的”,”去掉然后再转成int
SQL:
SELECT CAST(REPLACE(t_value,",","") AS UNSIGNED INTEGER) FROM table_a;
需求:对table_a表中的t_year=2019的数据按照t_value字段从大到小排序并且只去前5条
SQL:
SELECT * FROM table_a WHERE t_year = 2019 ORDER BY CAST(REPLACE(t_value,",","") AS UNSIGNED INTEGER) DESC LIMIT 5;