动态列排序
【问题】
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
