如何先用字母对字符串列进行排序？

Question

比如说，我想按名字对记录进行排序，名字也可以从数字字母开始。

如果我这么做

Something.query.order_by(Something.name)

我有以下命令：

• Zesty

• 排行
• 33
• Beta版

如何将查询更改为将数字放在字母之后：

version

• Zesty

• 1st
• Beta
• 33

Answer 1

添加一个初始排序子句来将前导-数字与前导-非数字分隔起来似乎是有效的：

engine = sa.create_engine(connection_url)
metadata = sa.MetaData()

mytable = sa.Table('mytable', metadata, autoload=True, autoload_with=engine)

# 1. default sort
stmt = sa.sql.select([mytable.c.thing])\
    .select_from(mytable)\
    .order_by(mytable.c.thing)
with engine.begin() as conn:
    print(conn.execute(stmt).fetchall())
    # [('1st in a row',), ('99 beers',), ('Beta version',), ('Zesty',)]

# 2. non-numeric first
sort_boundary = '9' * mytable.c.thing.type.length
print(sort_boundary)
# 99999999999999999999999999999999999999999999999999
#
stmt = sa.sql.select([mytable.c.thing])\
    .select_from(mytable)\
    .order_by(mytable.c.thing <= sort_boundary, mytable.c.thing)
with engine.begin() as conn:
    print(conn.execute(stmt).fetchall())
    # [('Beta version',), ('Zesty',), ('1st in a row',), ('99 beers',)]