自定义函数排序列问题 [英] User-defined function sorting column problem

问题描述

我从互联网上参考了一个用户定义的函数来定位第 n 次出现的字符串来对数据库中的列名进行排序.我使用的是 MySQL 5.5 版本，而不是最新版本.这是我的示例数据库链接 https://dbfiddle.uk/?rdbms=mysql_5.5&fiddle=bcb32a6b47d0d5b061fd401d0888bdc3

I have taken reference from the internet about one user-defined function to locate 'nth occurrence of a string to do the sort column name in the database. I am using MySQL 5.5 version, not the latest version. Here is my sample database link https://dbfiddle.uk/?rdbms=mysql_5.5&fiddle=bcb32a6b47d0d5b061fd401d0888bdc3

我的问题是我想按照前缀编号对数据库中的列name进行排序，但我在SQL查询下方使用，它不起作用.

My problem is I want to sort column name in the database follow the prefix number, but I am using below the SQL query, it doesn't work.

select t.id,t.name
from
(
select t.*, cast((case when col1_col2_ref > 0 
                      then 
                        substring_index(modified_name,'-',1)
                  else 
                     modified_name
                  end
                  ) as unsigned) col1
          , cast((case when    col1_col2_ref > 0
                           and col3_ref > 0
                       then 
                          substr(modified_name,(col1_col2_ref + 1),(col3_ref - (col1_col2_ref + 1)))
                       when col1_col2_ref > 0
                       then 
                         substr(modified_name,(col1_col2_ref + 1))
                  end) as unsigned) col2
          , cast((case when    col3_ref > 0
                           and col4_ref > 0
                       then 
                         substr(modified_name,(col3_ref + 1),(col4_ref - (col3_ref + 1)))
                       when col3_ref > 0
                       then 
                         substr(modified_name,(col3_ref + 1))
                  end) as unsigned) col3
          , cast((case when col4_ref > 0
                        then 
                         substr(modified_name,(col4_ref + 1))
                   end) as unsigned) col4
from
(
select t.*,substring_index(name,' ',1) modified_name
          ,locate('-',name,1) col1_col2_ref
          ,locate('/',name,1) col3_ref
          ,locate('/',name,locate('/',name,1)+1) col4_ref
  from filing_code_management t
) t
) t
order by col1,col2,col3,col4

它显示在结果下方，无法正确排序.

It shows me below the result, it cannot sort properly.

输出 1

实际上我想要如下输出示例:

Actually I want the output sample like below:

输出 2

输出 3

这是在我可以对列进行排序之前 name 链接，https://dbfiddle.uk/?rdbms=mysql_5.5&fiddle=6b12a4d42359cb30f27a5bfb9d0c8210.在我插入新数据后，它对我不起作用.如果我把 () 放在前面，也许是新数据中的一个例子，比如这个错误 (R)100-6-2-2 Mesyuarat Majlis Kerajaan Negeri (MMKN) JKK.或者在像这个错误的新数据中 100-1-1 Penggubalan/Penyediaan/Pindaan Undang-Undang/Peraturan 如果我把/放在单词之间.

This is before I can sort the column name link, https://dbfiddle.uk/?rdbms=mysql_5.5&fiddle=6b12a4d42359cb30f27a5bfb9d0c8210. After I am inserted into new data, it cannot work for me. Maybe an example in new data like this error (R)100-6-2-2 Mesyuarat Majlis Kerajaan Negeri (MMKN) JKK if I put () in front. Or in new data like this error 100-1-1 Penggubalan/Penyediaan/Pindaan Undang-Undang/Peraturan if I put / in between the word.

希望有人能指导我解决这个问题.谢谢.

Hope someone can guide me to solve this problem. Thanks.

推荐答案

您应该能够根据您的需要调整以下代码(在您的 DB Fiddle 中测试！).我使用 file_name 列而不是 name 列来稍微简化排序字段的构建，因为文件名似乎总是在 name 字段的第一部分重复.

You should be able to adapt the following code to your needs (tested at your DB Fiddle!). I've used the file_name column instead of the name column to slightly simplify building the sort fields, as it seems the file name is always repeated in the first part of the name field anyway.

使用正则表达式支持会简单一些，但我注意到您使用的 MySQL 版本没有此功能(如果我没记错的话，我认为它在 SQL 8.0 中出现).

This would be quite a bit simpler using regular expression support, but I note that the version of MySQL you are using doesn't have this feature (I think it arrives in SQL 8.0, if I'm not mistaken).

SELECT id, 
    num_hyphens, 
    CAST(SUBSTRING_INDEX(CONCAT(file_name_adj,'-'), '-', 1) AS UNSIGNED) AS sort1,
    CAST(CASE WHEN num_hyphens = 0 
          THEN '0' 
          ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(file_name_adj,'-', 2), '-',-1) 
          END AS UNSIGNED) AS sort2,
    CAST(CASE WHEN num_hyphens <= 1 
          THEN '0' 
          ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(file_name_adj,'-', 3), '-',-1) 
          END AS UNSIGNED) AS sort3,
    CAST(CASE WHEN num_hyphens <= 2 
          THEN '0' 
          ELSE SUBSTRING_INDEX(file_name_adj, '-', -1) 
          END AS UNSIGNED) AS sort4,
    file_name,
    name
FROM (
SELECT id, name, MID(file_name, instr(file_name, ')') + 1) AS file_name_adj, file_name, 
        LENGTH(file_name) - LENGTH(REPLACE(file_name, '-', '')) AS num_hyphens 
FROM filing_code_management
) t1
ORDER BY sort1, sort2, sort3, sort4

这篇关于自定义函数排序列问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！