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

Question

保存在我的SQLite数据库表中，我有一列以人性化的形式表示文件大小。例如，大小列中的值可以是 20.5mb 或 350kb 或 900b。我想知道如何使用按查询排序首先按大小对文件大小进行排序。

目前，我可以按 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转换为50：50MB + 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

您可以将sizeCAST到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