SQLite 中不同方向的不同列上的 ORDER BY [英] ORDER BY on different columns in different directions in SQLite

问题描述

我有一个由以下定义的表:

I have a table defined by:

    CREATE TABLE bar_table (
        _id INTEGER NOT NULL PRIMARY KEY,
        index INTEGER NOT NULL DEFAULT '65535',
        _date DATE
    )

我的基本选择语句是:

SELECT * FROM bar_table ORDER BY <your-clause-here>

如何按索引升序和日期降序对我的选择进行排序?即小索引出现在大索引之前.如果两个索引相同，则以较晚的日期为准.

How do I order my selection by index ascending, and date descending? i.e. small indexes come before large indexes. In the event that two indexes are the same, the later date is to come first.

文档将我指向 COLLATion，但我不确定那是什么.

The documentation is pointing me towards COLLATion, ubut I'm not really sure what that is.

推荐答案

虽然我知道您已经在这里有了自己的答案，但我认为在这里深入了解工作中的细节是恰当的.

While I know that you already have your own answer up here, I think it's pertinent to go into the details at work here.

首先，order by 子句按照指定的列或表达式的顺序进行.在这种情况下:

First, the order by clause goes in order of columns or expressions specified. In this case:

order by index asc, _date desc

按index从小到大(asc结尾)和然后_date从大到小(desc 结尾).虽然 asc 是默认值，但当我有多个相反方向的列时，我通常会包含它，就像你在这里做的那样.

That sorts by index smallest to largest (ascending), and then _date largest to smallest (descending). While asc is the default value, I generally include it when I have multiple columns going opposite directions, like you do here.

您还可以在 order by 中包含表达式:

You can also include expressions in your order by:

order by case when index < 0 then 1 else 0 end desc, _date desc

这会将所有负的 index 行放在顶部，然后按 _date 对它们进行排序.在您的 order by 子句中使用表达式在某些情况下非常有效.

This would put all of the negative index rows at the top, and then sort those by _date. Using expressions in your order by clause is very effective in certain circumstances.

现在，您提到了 collat​​ion，但对那是什么有点困惑.排序规则是数据库在字符串比较中处理大写和重音的方式.使用 Captial-Sensitive 排序规则，'abc' != 'ABC'.但是，对于 Captial-Insensitive 归类，'abc' = 'ABC'.

Now, you mentioned collation, and a little confusion as to what that is. Collation is how the database treats capital and accents in string comparisons. With a Captial-Sensitive collation, 'abc' != 'ABC'. However, with a Captial-Insensitive collation, 'abc' = 'ABC'.

应该注意的是，排序规则不是一个字符集.这通常由数据类型决定(varchar == ASCII，nvarchar == Unicode).排序规则决定了字符串的比较方式，而不是可用的字符集.

It should be noted that collation is not a character set. That's usually determined by data type (varchar == ASCII, nvarchar == Unicode). Collation determines how strings are compared, not what character sets are available for use.

此外，校对对于某些语言也很重要.给定拉丁语排序规则，您只需要担心大小写和重音符号，但给定丹麦语排序规则，'aa' = 'å'.¹ 所以您可以看到该排序规则在确定不同语言的排序和比较方面也起着重要作用.

Moreover, collation is also important with certain languages. Given a Latin collation, you just have to worry about capitalization and accents, but given a Danish collation, 'aa' = 'å'.¹ So you can see that collation plays a big part in determining sorting and comparisons for different languages, as well.

排序时排序规则非常重要，因为它决定了字符串在不同大小写和重音的情况下如何排序.这就是为什么它会不断出现在您的搜索中.整理很重要，本周它甚至影响了 StackOverflow！

Collation is very important when ordering, because it determines how strings will be ordered given different capitalizations and accents. That's why it keeps coming up in your searches. Collation is important, and it even affected StackOverflow this week!

¹_{:感谢 Michael Madsen 指出这个具体的例子.}

这篇关于SQLite 中不同方向的不同列上的 ORDER BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！