mysql - 如何将 order by 与 LENGTH() 函数一起使用

Question

我正在尝试执行以下查询，但是每次我都会得到错误的结果及其返回的总值 0 。请帮我解决这个问题。

SELECT content, ((LENGTH(content)-(LENGTH(REPLACE(content,'oracle',''))))*4+(LENGTH(content)-(LENGTH(REPLACE(content,'finance',''))))*3+(LENGTH(content)-(LENGTH(REPLACE(content,'functional',''))))*2+(LENGTH(content)-(LENGTH(REPLACE(content,'consultant',''))))*1)
FROM `jobs` order by ((LENGTH(content)-(LENGTH(REPLACE(content,'oracle',''))))*4+(LENGTH(content)-(LENGTH(REPLACE(content,'finance',''))))*3+(LENGTH(content)-(LENGTH(REPLACE(content,'functional',''))))*2+(LENGTH(content)-(LENGTH(REPLACE(content,'consultant',''))))*1);

Answer 1

您可以将列索引用作:

SELECT content, ((LENGTH(content)-  
(LENGTH(REPLACE(content,'oracle',''))))*4+(LENGTH(content)-  
(LENGTH(REPLACE(content,'finance',''))))*3+(LENGTH(content)-
(LENGTH(REPLACE(content,'functional',''))))*2+(LENGTH(content)-
(LENGTH(REPLACE(content,'consultant',''))))*1)
FROM `jobs` ORDER BY 2;

或者使用列别名:

SELECT content, ((LENGTH(content)-  
(LENGTH(REPLACE(content,'oracle',''))))*4+(LENGTH(content)-  
(LENGTH(REPLACE(content,'finance',''))))*3+(LENGTH(content)-
(LENGTH(REPLACE(content,'functional',''))))*2+(LENGTH(content)-
(LENGTH(REPLACE(content,'consultant',''))))*1) CONTENT_SIZE
FROM `jobs` ORDER BY CONTENT_SIZE;