动态列排序

【问题】

I have this data in my table and I want to sort it using the numbers in the data.

Data unsorted

5 team team team team

team team team 2 team

team team team team 4

team 1 team team team

team team 3 team team

Data Sorted

team 1 team team team

team team team 2 team

team team 3 team team

team team team team 4

5 team team team team

有人给出解答,楼主没回复

Create this function:

CREATE FUNCTION IsNumeric (val varchar(255)) RETURNS tinyint

 RETURN val REGEXP '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$';

 

CREATE FUNCTION GetNumericOnly (val VARCHAR(255))

 RETURNS VARCHAR(255)

BEGIN

 DECLARE idx INT DEFAULT 0;

 IF ISNULL(val) THEN RETURN NULL; END IF;

 

 IF LENGTH(val) = 0 THEN RETURN ""; END IF;

 

 SET idx = LENGTH(val);

  WHILE idx > 0 DO

  IF IsNumeric(SUBSTRING(val,idx,1)) = 0 THEN

   SET val = REPLACE(val,SUBSTRING(val,idx,1),"");

   SET idx = LENGTH(val)+1;

  END IF;

  SET idx = idx - 1;

  END WHILE;

  RETURN val;

 END;

Then use it like this:

SELECT * FROM mixedvalues

ORDER BY GetNumericOnly(value)

 

【回答】

SQL做这种动态列的运算很不方便,如果没特别要求,建议读出来用SPL来做,把记录转成数组再取了数值类型的字段排序即可,代码简单也易懂:


A

1

$select * from tb

2

=A1.sort(~.array().select(ifnumber(~)))

A1:从表tb取数

A2:将每条记录的字段值转为序列,从该序列中取出数值成员作为A1的排序表达式进行排序

写好的脚本如何在应用程序中调用,可以参考Java 如何调用 SPL 脚本

 更多动态列的例子可参考http://blog.raqsoft.com/?p=5010