SQL – ORDER BY-Sortierung ist falsch

Question

Ich habe eine Frage, es funktioniert gut. Im letzten Schritt möchte ich, dass die gesamte Tabelle DESC nach der Anzahl der Elemente in der Spalte „Elementliste“ sortiert wird, aber das funktioniert nicht.

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;

Ich bin mir ziemlich sicher, dass mir schon kalt ist, aber mir fehlt noch etwas.

item_id wird in Tabelle2 als int(11) gespeichert und dann als Fremdschlüssel an Tabelle3 übergeben.

Das ist das Ergebnis, das ich über COUNT bekomme:

Das ist die Länge, die ich bekommen habe:

Danke!

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

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