MySQL查询数字排序 [英] MySQL Query Sorting with Numbers

问题描述

我有以下查询:

$result = $mysqli->query('SELECT DISTINCT SKU_SIZE_PART1 
                          FROM SKU_DATA 
                          WHERE SKU_BRANDNAME = "'.$brand.'" 
                          ORDER BY SKU_SIZE_PART1 DESC');

while( $row = $result->fetch_assoc()){
    $sku_size1 = $row['SKU_SIZE_PART1'];

    echo $sku_size1;
}

基本上发生了什么..订单都弄乱了.出现的是这样:

Basically what is happening is.. the order is all messed up. This is what comes up:

9.50, 8.75, 8.00, 7.50, 7.00, 37, 35, 33, 325, 32, 315, 31, 305, 30, 295

325应该首先出现，然后是315，依此类推.

325 should come up first, then 315 then so on.

我该怎么做才能做到这一点?

What can I do to make this happen?

推荐答案

您需要将sku_size_part1强制转换为浮点数.

You need to cast sku_size_part1 into a float.

这会使查询速度变慢，但它会起作用:

This will slow your query down, but it will work:

$brand = mysqli_real_escape_string($brand);
$result = $mysqli->query("SELECT DISTINCT sku_size_part1
                          FROM sku_data 
                          WHERE sku_brandname = '$brand' 
                          ORDER BY CAST(sku_size_part1 AS FLOAT) DESC");

这将减慢查询速度，因为MySQL将无法使用索引进行排序，使用函数可以防止这种情况.

This will slow the query down, because MySQL will not be able to use an index to do the sorting, using a function prevents that.

更好的解决方案(如果可能)是将sku-size_part1重新定义为十进制(10,2).

A better solution (if possible) would be to redefine sku-size_part1 as a decimal(10,2).

-- Make a backup first --
ALTER TABLE sku_data CHANGE sku_size_part1 DECIMAL(10,2); 

(确保第一个参数(10)和第二个参数(2)足够大以容纳所有可能的值.)
参见: http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html

(Make sure the first parameter (10) and the second parameter (2) are large enough to hold all possible values.)
See: http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html

这篇关于MySQL查询数字排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！