SQLite ORDER BY字符串,包含以0开头的数字

我有一个选择查询,我试图“按顺序”包含数字的字段,事实是这些数字实际上是以0开头的字符串,所以“order by”就是这样做的…

...
10
11
12
01
02
03
...

有什么想法吗？

编辑：如果我这样做：“… ORDER BY(字段1)”我可以解决这个问题,因为不知何故,字符串在内部被转换为整数.这是一种像C’s atoi一样“正式”转换它的方法吗？

sqlite> CREATE TABLE T (value VARCHAR(2));
sqlite> INSERT INTO T (value) VALUES ('10');
sqlite> INSERT INTO T (value) VALUES ('11');
sqlite> INSERT INTO T (value) VALUES ('12');    
sqlite> INSERT INTO T (value) VALUES ('01');
sqlite> INSERT INTO T (value) VALUES ('02');
sqlite> INSERT INTO T (value) VALUES ('03');
sqlite> SELECT * FROM T ORDER BY CAST(value AS INTEGER);
01
02
03
10
11
12
sqlite>

if I do this: “…ORDER BY (field+1)” I can workaround this,because somehow the string is internally being converted to integer. Is the a way to “officially” convert it like C’s atoi?

这很有意思,虽然我不知道有多少DBMS支持这样的操作所以我不推荐它,以防你需要使用不支持它的不同系统,更不用说你正在添加额外的操作,这可能会影响性能,但你也这样做ORDER BY(字段0)我将调查性能

取自sqlite3文档：

A CAST expression is used to convert the value of to a different storage class in a similar way to the conversion that takes place when a column affinity is applied to a value. Application of a CAST expression is different to application of a column affinity,as with a CAST expression the storage class conversion is forced even if it is lossy and irrreversible.

4.0 Operators
All mathematical operators (+,-,*,/,%,<<,>>,&,and |) cast both operands to the NUMERIC storage class prior to being carried out. The cast is carried through even if it is lossy and irreversible. A NULL operand on a mathematical operator yields a NULL result. An operand on a mathematical operator that does not look in any way numeric and is not NULL is converted to 0 or 0.0.

我很好奇所以我跑了一些基准：

>>> setup = """
... import sqlite3
... import timeit
... 
... conn = sqlite3.connect(':memory:')
... c = conn.cursor()
... c.execute('CREATE TABLE T (value int)')
... for index in range(4000000,-1):
...     _ = c.execute('INSERT INTO T (value) VALUES (%i)' % index)
... conn.commit()
... """
>>> 
>>> cast_conv = "result = c.execute('SELECT * FROM T ORDER BY CAST(value AS INTEGER)')"
>>> cast_affinity = "result = c.execute('SELECT * FROM T ORDER BY (value + 0)')"
>>> timeit.Timer(cast_conv,setup).timeit(number = 1)
18.145697116851807
>>> timeit.Timer(cast_affinity,setup).timeit(number = 1)
18.259973049163818
>>>

我们可以看到它有点慢,虽然不是很多,有趣.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!