按预算从高到低或从低到高排序

for high to low:

从高到低:

$query="select * from sponsors order by budget DESC";

for low to high:

从低到高:

$query="select * from sponsors order by budget ";

When executing this query, it only orders by the first digit. For example:

执行此查询时,它仅按第一个数字排序。例如:

budget: 95,00,000 6,00,000 3,00,000 29,58,000 22,78,000

预算:95,00,000 6,00,000 3,00,000 29,58,000 22,78,000

What am I doing wrong?

我究竟做错了什么?

5 个解决方案

#1

The "correct" way to solve this problem is to re-architect the table and split budget into two fields, one int and one varchar which describes what the int refers to e.g. "p.a" or "ongoing" or "per month" or whatever. That way you can properly order your data by the int field.

解决此问题的“正确”方法是重新构建表并将预算拆分为两个字段,一个int和一个varchar,用于描述int所指的内容,例如: “p.a”或“正在进行”或“每月”或其他什么。这样您就可以通过int字段正确地对数据进行排序。

In the interim, you should be able to order the data by stripping the commas out of the numeric part of the field and then casting it to an unsigned integer:

在此期间,您应该能够通过从字段的数字部分中删除逗号然后将其转换为无符号整数来对数据进行排序:

SELECT *
  FROM sponsors
  ORDER BY CAST(REPLACE(budget, ',', '') AS UNSIGNED)

This should work as long as all the budget values start with numbers which may or may not contain commas.

只要所有预算值都以可能包含或不包含逗号的数字开头,这应该有效。

#2

I think your column data type is char so please make ensure you use int datatype for column.

我认为您的列数据类型是char,因此请确保使用int数据类型作为列。

#3

ORDER BY in ANSI SQL (you didn't specify your particular variant of sql; that would help :-) is dependent on the column definition.

ANSI SQL中的ORDER BY(您没有指定您的特定sql变体;这将有助于:-)取决于列定义。

If you want to order by numerical value (instead of string-based - if we're guessing, it looks like you have a char/varchar/text column here), you'll need to use a numerical column type, e.g., INT, FLOAT, DECIMAL, etc.

如果你想按数值排序(而不是基于字符串 - 如果我们猜测,看起来你在这里有一个char / varchar / text列),你需要使用数值列类型,例如INT ,FLOAT,DECIMAL等

In other words, you need to tell SQL (via your schema) that you have numerical data specifically. 10 is only less than 3 in base 10. :-)

换句话说,您需要告诉SQL(通过您的架构)您具体具有数字数据。基数10中10小于3 :-)

#4

You need to store budget as numerical datatype such as unsigned INT or BIGINT in database.

您需要将预算存储为数字数据类型,例如数据库中的unsigned INT或BIGINT。

For now, ORDER will treat all the data as TEXT. That is why they are ordering by first digits.

目前,ORDER会将所有数据视为TEXT。这就是他们按第一个数字排序的原因。

#5

You can try this one

你可以尝试这个

SELECT * 
FROM sponsors 
ORDER BY cast(budget as int)

if this is you column value (6,00,00 p.a)

如果这是你的列值(6,00,00 p.a)

  SELECT * 
    FROM sponsors 
    ORDER BY cast(LEFT(budget,length(budget)-3) as int)

#1