mysql:先按项目数排序,然后按内容排序

flmtquvp  于 2021-06-15  发布在  Mysql
关注(0)|答案(3)|浏览(289)

我使用的是MySQL5.7,我需要对列表进行排序,这些列表的存储方式如下:

| list_id | item   | item_index |
| ------- | ------ | ---------- |
| 0       | apple  | 0          |
| 0       | bread  | 1          |
| 1       | apple  | 0          |
| 1       | banana | 1          |
| 2       | orange | 0          |

但这些项目不一定是字符串,它们可以是整数或布尔值,这只是一个简化。
列表应按以下顺序排列:
列表中的项目数
如果两个列表的项目数相同,则应按项目索引的顺序比较它们的项目
所以这个例子的结果应该是:
2-橙色
1-苹果、香蕉
0-苹果、面包
我用 group by 以及 count(*) 按列表长度排序,但问题是-如果列表中的最大项数未知,如何按列表内容排序?
我想出的唯一解决办法就是 N 同一个表上的左联接,其中 N 是未知的最大列表长度,每个可能的列表项的每个连接。请看表和我尝试在db fiddle上排序。
在不知道列表中项目的最大数量的情况下,有什么方法可以这样排序吗?

xv8emn3q

xv8emn3q1#

你可以按 count(*) 然后 group_concat(item) 按列表长度排序,然后比较项目:

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, items asc

更新:
如果您想对数字进行排序,那么groupconcat()仍然有效,因为mysql隐式地将数字转换为字符串。只需用0填充数字,以确保正确排序,因为与字符串比较19小于2。因此,按以下顺序更改组\u concat(整数不能超过10位):

group_concat(LPAD(item, 10, '0') order by item_index asc)
bwitn5fc

bwitn5fc2#

像这样,如果你想从小提琴中抽出5行:

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

如果列表项是int(并且需要5行)

我想你需要这样做:

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宽,这样排序就可以工作,因为“0000000120000000124”<“0000000120000000125”
我选了10宽,因为int max是45亿;10位数字。如果你的整数要小,你可以少垫
如果你比较布尔值,一个类似的策略,可能会把它们转换成整数(真=0,假=1?),这样它们就可以直接排序,即使聚合成一个字符串。。
如果t,t,f的列表排序在t,f,f之前,那么将t=0,f=1。。例如

如果你想从小提琴里抽出三排。。

从阴影中借来,并调整为 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
2izufjch

2izufjch3#

这就是你想要的吗?我不确定订单是什么

SELECT list_id,GROUP_CONCAT(item SEPARATOR ' ') aggregate_items ,SUM(item_index) aggregate_item_index 
FROM grocery_list
GROUP BY list_id
ORDER BY list_id DESC,COUNT(item) ASC,SUM(item_index) ASC

相关问题