sqlite 中的自定义排序
有没有办法在 sqlite 中通过查询自定义订单？
例如，我本质上有一个枚举
_id|Name|Key ------------ 1 | One | Named 2 | Two | Contributing 3 | Three | Named 4 | Four | Key 5 | Five | Key 6 | Six | Contributing 7 | Seven | Named
，并且“键”列有顺序。说键>命名>贡献。
有没有办法让
SELECT * FROM table ORDER BY Key
返回达到此效果的内容
_id|Name|Key ------------ 4 | Four | Key 5 | Five | Key 1 | One | Named 3 | Three | Named 7 | Seven | Named 2 | Two | Contributing 6 | Six | Contributing
？
Is there a way to have a custom order by query in sqlite?
For example, I have essentially an enum
_id|Name|Key ------------ 1 | One | Named 2 | Two | Contributing 3 | Three | Named 4 | Four | Key 5 | Five | Key 6 | Six | Contributing 7 | Seven | Named
And the 'key' columns have ordering. Say Key > Named > Contributing.
Is there a way to make
SELECT * FROM table ORDER BY Key
return something to the effect of
_id|Name|Key ------------ 4 | Four | Key 5 | Five | Key 1 | One | Named 3 | Three | Named 7 | Seven | Named 2 | Two | Contributing 6 | Six | Contributing
this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果您有很多 CASE（或复杂的条件集），Adam 的解决方案可能会导致非常大的查询。
SQLite 确实允许您编写自己的函数（用 C++）。您可以编写一个函数来返回与 Adam 类似的值，但由于您使用的是 C++，因此您可以使用更大的条件集（或单独的表等）。
编写函数后，您可以在 SELECT 中引用它，就像它是内置函数一样：
SELECT * FROM my_table ORDER BY MyOrder(Key)
If you have a lot of CASE's (or complicated set of conditions), Adam's solution may result in an extremely large query.
SQLite does allow you to write your own functions (in C++). You could write a function to return values similar to the way Adam does, but because you're using C++, you could work with a much larger set of conditions (or separate table, etc).
Once the function is written, you can refer to it in your SELECT as if it were a built-in function:
SELECT * FROM my_table ORDER BY MyOrder(Key)
你尝试过吗（我这边没有测试过，但依赖于我以前使用过的技术）：
Did you try (not tested on my side but relying on a technique I previously used):