如何按数字将varchar字段排序? [英] How to ORDER BY varchar field as number?

问题描述

我的排序/排序方式有问题，不能按我的需要工作.

I have problem with sort/order by, not working like I need.

SELECT `proc` FROM `table` ORDER BY `proc` DESC;

结果:

80.0 proc
70.0 proc
60.0 proc
50.0 proc
40.0 proc
200.0 proc
20.0 proc
190.0 proc
180.0 proc
170.0 proc
160.0 proc
150.0 proc
140.0 proc
130.0 proc
120.0 proc
110.0 proc
100.0 proc

我需要的是:

200.0 proc
190.0 proc
180.0 proc
170.0 proc
160.0 proc
150.0 proc
140.0 proc
130.0 proc
120.0 proc
110.0 proc
100.0 proc
90.0 proc
80.0 proc
70.0 proc
60.0 proc
50.0 proc
40.0 proc
20.0 proc

该怎么做?

推荐答案

看起来"proc"是一个字符串(varchar字段)，因此按词法进行排序.如果是这样，您可以通过以下方式订购

It looks like "proc" is a string (varchar field), so it gets ordered lexically. If it is so, you can probably order it by

SELECT `proc` FROM `table` ORDER BY convert(`proc`, decimal) DESC;

请注意，这样的查询将非常慢，对于任何严重的使用，最好使用数字列来存储数字数据.

Please note that such queries will be very slow, and for any serious usage it's better to use numeric columns for storing numeric data.

这篇关于如何按数字将varchar字段排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！