SQL 按同一列的两组条件排序

Question

保存在我的 SQLite 数据库表中，我有一列以人性化的形式表示文件大小。例如，大小列中的值可以是 20.5mb、350kb 或 900b。我想知道如何使用 order by query 来首先按大小排序文件大小。目前，我可以按 tb、gb、mb、kb、b 排序。所以它们的顺序通常是正确的。但是，在按文件大小单位对它们进行排序时，我需要按文件大小对它们进行完全正确的排序。这是我当前的查询SELECT * FROM filtered ORDER BY case when size like '%TB%'  then 1                 when size like '%GB%' then 2               when size like '%MB%' then 3                when size like '%kB%' then 4               when size like '%B%' then 5               else 6    end返回行的顺序是 90MB、50MB、50.5MB、62KB、135kb。这里有一些问题。第一个是 50 会在 50.5 之前出现。第二个是因为第一个数字，9 会排在 100 之前。

Answer 1

当您对它们应用算术运算（如加法）时， SQLite会将字符串文字隐式转换为数字。因此，当您执行以下操作时，

它将转换50MB为： 。 所以相加的结果将是。 所以你可以这样做：5050MB + 0

50

SELECT * FROM filtered 

ORDER BY 

case 

  when size like '%TB%' then 1 

  when size like '%GB%' then 2 

  when size like '%MB%' then 3 

  when size like '%kB%' then 4 

  when size like '%B%' then 5 

  else 6 

end,

size + 0 desc

或者只有 1 级排序：

SELECT * FROM filtered 

ORDER BY 

size * case 

  when size like '%TB%' then 1024 * 1024 * 1024 * 1024 

  when size like '%GB%' then 1024 * 1024 * 1024

  when size like '%MB%' then 1024 * 1024 

  when size like '%kB%' then 1024 

  when size like '%B%' then 1 

  else 1 

end desc

Answer 2

您可以CAST选择sizea FLOAT（因为它以数字开头），然后将其用作次要ORDER BY标准，即：

SELECT * FROM filtered
ORDER BY case when size like '%TB%'  then 1
              when size like '%GB%' then 2
              when size like '%MB%' then 3 
              when size like '%kB%' then 4
              when size like '%B%' then 5
              else 6
         end, CAST(size AS FLOAT) DESC