SQL - ORDER BY 排序不正确

Question

我有一个疑问，工作正常。在最后一步中，我希望它根据“项目列表”列中的项目数量对整个表 DESC 进行排序，但它不起作用。

SELECT t8.username AS 'Username',
       GROUP_CONCAT(CASE WHEN t1.dup=1 AND t2.stat=0 AND t5.item_name='lamp' THEN item_id END ORDER BY item_id SEPARATOR ', ') `My Item List`,
       GROUP_CONCAT(CASE WHEN t2.dup=1 AND t1.stat=0 AND t5.item_name='lamp' THEN item_id END ORDER BY item_id SEPARATOR ', ') `Item List`
FROM table1 t1
LEFT JOIN table3 t2 USING (item_id)
JOIN table2 t5 ON t5.id = t2.user_id
JOIN accounts t8 ON t8.id = t2.user_id
WHERE t1.user_id = 23
  AND t2.user_id <> 23
  GROUP BY t2.user_id
  HAVING `Item List` is not null or `My Item List` is not null
  ORDER BY COUNT('Item List') DESC;

我很确定我已经很冷了，但我仍然缺少一些东西。

item_id 以 int(11) 形式存储在 table2 中，然后将其作为外键传递给 table3。

这是我通过 COUNT 得到的结果：

这是我得到的长度：

谢谢！

Answer 1

如果您想要按照长度项目列表的结果，则结果如下所示

我的列表项目列表 空 6,7,8,9,10,12,13,14,15,16,17,20,371 空 20,21,22,23,24,25 空 6,7,8,9,10 空 131 空 131 1,4,5 空

尝试以下查询

SELECT t8.username AS 'Username',
       GROUP_CONCAT(CASE WHEN t1.dup=1 AND t2.stat=0 AND t5.item_name='lamp' THEN item_id END ORDER BY item_id SEPARATOR ', ') `My Item List`,
       GROUP_CONCAT(CASE WHEN t2.dup=1 AND t1.stat=0 AND t5.item_name='lamp' THEN item_id END ORDER BY item_id SEPARATOR ', ') `Item List`
FROM table1 t1
LEFT JOIN table3 t2 USING (item_id)
JOIN table2 t5 ON t5.id = t2.user_id
JOIN accounts t8 ON t8.id = t2.user_id
WHERE t1.user_id = 23
  AND t2.user_id <> 23
  GROUP BY t2.user_id
  HAVING `Item List` is not null or `My Item List` is not null
  ORDER BY LEN('Item List') DESC;

Answer 2

我们可以使用与相应的 group_concat 相同的条件的 sum 来计算 Item List 中的元素计数，如下所示：

SELECT t8.username AS 'Username',
    GROUP_CONCAT(CASE WHEN t1.dup=1 AND t2.stat=0 AND t5.item_name='lamp' THEN item_id END ORDER BY item_id SEPARATOR ', ') `My Item List`,
    GROUP_CONCAT(CASE WHEN t2.dup=1 AND t1.stat=0 AND t5.item_name='lamp' THEN item_id END ORDER BY item_id SEPARATOR ', ') `Item List`
FROM table1 t1
...
ORDER BY SUM(CASE WHEN t2.dup=1 AND t1.stat=0 AND t5.item_name='lamp' THEN 1 ELSE 0 END) DESC

这比仅比较生成的字符串的长度更安全：例如，包含单个大数字（如 '10000'）的项目列表仍然比 '1 长, 2'，其中包含两项。

如果我们要使用字符串函数，我们可以计算字符串中出现了多少个逗号：

ORDER BY LENGTH(`Item List`) - LENGTH(REPLACE(`Item List`, ', ', '')) DESC

旁注：两个用户的列表中可能有相同数量的项目，因此添加另一个排序标准来打破潜在的联系可能是个好主意。