在sql中排序max到min值无法正常工作

问题描述：

i have 1 table for ex table1 and it have columns names: id, name, amount.

id name amount
1  abc  20
2  xyz  50
3  pqr  246

i want to sort it from max value to min. i have written this query:

SELECT * FROM table1 ORDER BY amount DESC;

but it gives output like this:

id name amount
2  xyz  50
3  pqr  246
1  abc  20

which is not valid output that i want, i want output like this:

id name amount
3  pqr  246
2  xyz  50
1  abc  20

so, if anyone know the solution of this problem please give me the solution.

我有1个表用于ex table1 并且它有列名：id，name，amount。 p>

  id name amount 
1 abc 20 
2 xyz 50 
3 pqr 246 
  code>  pre> 
 
 我想从最大值中对其进行排序 至min。
i已编写此查询： p> 
 
 
  SELECT * FROM table1 ORDER BY amount DESC; 
  code>  pre> 
 
  但它给出如下输出： p> 
 
 
  id name amount 
2 xyz 50 
3 pqr 246 
1 abc 20 
  code>  pre> 
 
  这是我想要的有效输出，我希望输出如下： p> 
 
 
  id name amount 
3 pqr 246 
2 xyz 50 
1 abc 20 
   pre> 
 
 所以，如果有人知道这个问题的解决方案，请给我解决方案。 p> 
  div>

答

You are storing the amount as a string, it would appear.

Here are two solutions. You can convert the values to a number for the sort:

order by amount + 0 desc

Or, order by the length first and then the value:

order by length(amount) desc, amount desc

In general, in SQL, you should make use of the built-in types and store values as the appropriate time. Numbers should be stored as numbers (integers, floats, or decimals). Dates and times should be stored as date/time types. And strings can be used for string values.

答

Its related to wrong datatype.

Make data type of amount column as INT or FLOAT.

答

See the design of the table by using a command such as

DESC TABLENAME

I suspect your Amount column is text/varchar and not numeric.

Or simply run the following command and then try your query.

ALTER TABLE TABLE1 ALTER COLUMN AMOUNT INT

答

Always use appropriate data types for the data to store.

Since the data type of amount column is string and not numeric, ORDER BY sorts it based on the ASCII Value.

SQL> WITH DATA(ID, NAME, amount) AS(
  2  SELECT 2,  'xyz',  '50' FROM dual UNION ALL
  3  SELECT 3,  'pqr',  '246' FROM dual UNION ALL
  4  SELECT 1,  'abc',  '20' FROM dual
  5  )
  6  SELECT * FROM DATA
  7  ORDER BY amount DESC;

        ID NAM AMO
---------- --- ---
         2 xyz 50
         3 pqr 246
         1 abc 20

SQL>

If you have only numeric data stored as string, then you could use TO_NUMBER to explicitly convert the string into number while sorting.

ORDER BY to_number(amount) DESC

For example,

SQL> WITH DATA(ID, NAME, amount) AS(
  2  SELECT 2,  'xyz',  '50' FROM dual UNION ALL
  3  SELECT 3,  'pqr',  '246' FROM dual UNION ALL
  4  SELECT 1,  'abc',  '20' FROM dual
  5  )
  6  SELECT * FROM DATA
  7  ORDER BY to_number(amount) DESC;

        ID NAM AMO
---------- --- ---
         3 pqr 246
         2 xyz 50
         1 abc 20

SQL>

在sql中排序max到min值无法正常工作

相关推荐