我正在使用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的结果，它不能正确排序在表。
错误
希望有人能指导我解决这个问题。多谢了。
这里有一个查询，我认为它将按您所希望的顺序排序。最里面的子查询在第一个空格处拆分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演示
