SQL版本MySQL 5.5中的排序列

Question

我正在使用MySQL5.5版本编写SQL查询来进行排序。我的问题是无法正确使用SQL查询下面的列name排序。

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

这是我的数据库链接，显示我的排序错误https://dbfiddle.uk/？rdbms=mysql_5.5&fiddle=bcb32a6b47d0d5b061fd401d0888bdc3

实际上，我想对列name模式进行排序，如下所示：

id  |  name
33     100 PENTADBIRAN
40     100-1 Perundangan
46     100-1-1 Penggubalan/Penyediaan/Pindaan Undang-Undang/Peraturan
48     100-1/1/1 Undang-Undang Kecil Kerja Di Jalan 1996
49     100-1/1/2 Undang-Undang Kecil Peruntukan Mengenai Lesen Bersesama (Majlis Perbandaran Klang) 1992
264    100-6 Pengurusan Mesyuarat
265    100-6-1 Mesyuarat Dalam Jabatan
266    100-6-1-1 Mesyuarat Penuh Bulanan  SUP
267    100-6-1-2 Mesyuarat Khas Belanjawan JKEW
303    100-6-2 Mesyuarat Luar Jabatan
304    (S)100-6-2-1 Mesyuarat Dewan Undangan Negeri JKK
305    (R)100-6-2-2 Mesyuarat Majlis Kerajaan Negeri (MMKN) JKK
306    (S)100-6-2-3 Mesyuarat Tindakan Ekonomi Selangor (MTES) SUP
455    100-7 Kemudahan (Fasiliti)
456    100-7-1 Tempahan/Penggunaan Premis Ibu Pejabat dan Cawangan JKP
457    100-7-1-1 Bilik Mesyuarat/Auditorium

我的错误排序在MySQL5.5的结果，它不能正确排序在表。

错误

希望有人能指导我解决这个问题。多谢了。

Answer 1

这里有一个查询，我认为它将按您所希望的顺序排序。最里面的子查询在第一个空格处拆分name字段；删除结果开头()中包含的任何文本，然后用-替换/。外部的子查询将结果字符串拆分为-上的1到4个部分，条件聚合用于将这些值放入col1、col2、col3和col4中。然后将此子查询连接到主表的id字段，结果按col1到col4排序：

select t.*
from filing_code_management t
join (
select id,
       CAST(MAX(CASE WHEN f.n = 1 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(modified_name, '-', f.n), '-', -1) END) AS UNSIGNED) col1,
       CAST(MAX(CASE WHEN f.n = 2 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(modified_name, '-', f.n), '-', -1) END) AS UNSIGNED) col2,
       CAST(MAX(CASE WHEN f.n = 3 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(modified_name, '-', f.n), '-', -1) END) AS UNSIGNED) col3,
       CAST(MAX(CASE WHEN f.n = 4 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(modified_name, '-', f.n), '-', -1) END) AS UNSIGNED) col4
from
(
select t.id, replace(substr(substring_index(t.name,' ',1), locate(')', substring_index(t.name,' ',1))+1), '/', '-') modified_name
  from filing_code_management t
) t
join (select 1 n union all select 2 union all select 3 union all select 4) f
  on length(replace(t.modified_name, '-', '')) <= length(t.modified_name) - f.n + 1
group by id
) c on t.id = c.id
order by c.col1, c.col2, c.col3, c.col4

dbfiddle演示