我使用 MySql 5.7,我需要对这样存储的列表进行排序:
| list_id | item | item_index |
| ------- | ------ | ---------- |
| 0 | apple | 0 |
| 0 | bread | 1 |
| 1 | apple | 0 |
| 1 | banana | 1 |
| 2 | orange | 0 |
但是这些项目不一定是字符串,它们可以是整数或布尔值,这只是一种简化。
列表应按以下顺序排序:
所以这个例子的结果应该是:
我使用group by
和count(*)
按列表长度排序,但问题是 - 如果列表中的最大项目数未知,如何按列表内容排序?
我想出的唯一解决方案是N
在同一个表上进行左连接,N
这个未知的最大列表长度在哪里,每个可能的列表项的每个连接。请参阅 table 和我尝试在DB Fiddle上对其进行排序。
有没有办法在不知道列表中项目的最大数量的情况下以这种方式对其进行排序?
select * from
--the main data
grocery_lists gl
--joined with
inner join
--the count of items in each list
(
select list_id, group_concat(item order by item_index asc) as grouped_items, count(*) as total_count
from grocery_lists gl
group by list_id
) ct
on gl.list_id = ct.list_id
--ordered by the count of items, then the index
order by ct.total_count, ct.grouped_items, gl.item_index
因此,您将获得如下行:
2, orange, 0 --sorts first because count - 1
1, apple, 0 --sorts ahead of list 0 because "apple, banana" < "apple, bread"
1, banana, 1
0, apple, 0
0, bread, 1
我认为你需要这样做:
select * from
--the main data
grocery_lists gl
--joined with
inner join
--the count of items in each list
(
select list_id, group_concat(LPAD(item, 10, '0') order by item_index asc) as grouped_items, count(*) as total_count
from grocery_lists gl
group by list_id
) ct
on gl.list_id = ct.list_id
--ordered by the count of items, then by padded aggregate ints, then index
order by ct.total_count, ct.grouped_items, gl.item_index
如果你的项目是整数,用 0 将它们填充到例如 10 宽会使排序工作,因为 "0000000123, 00000000124" < "0000000123, 0000000125"
我选择了 10 宽,因为 int max 是 45 亿;10 位数字。如果你的整数更小,你可以减少填充
如果您正在比较布尔值,类似的策略,可能会将它们转换为 INT(true=0,false=1 ?),以便它们正确排序,即使聚合为字符串时也是如此。
如果 T,T,F 的列表排在 T,F,F 之前,则使 T=0 和 F=1.. 例如
从 Shadow 借来,并调整为item
int:
select list_id, group_concat(item order by item_index asc) as items, count(*) as list_length
from yourtable
group by list_id
order by list_length asc, group_concat(LPAD(item, 8, '0') order by item_index asc) asc
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句