AI编程（五十三）：排序和分页 — ORDER BY+LIMIT，让答案排好队、分页端上来

数据库里躺着 20 条价格记录，可”最便宜的3个””最新的一条””第2页翻出来看看”这些问题，数据库不会自动懂你。今天配齐查询的最后一块基础零件：ORDER BY 负责排队（谁前谁后），LIMIT 负责限量（只要前几名），俩人一搭档——排行榜、翻页浏览、”每个商品只看最新”全搞定。外加一个数据世界的经典暗坑：翻页时同一行出现两次，或者总有一行怎么都翻不到。

一、真实场景：排行榜、翻页，和”只看最新一条”

接着价格监控工具的故事讲。数据库上线之后，你发现自己问的问题悄悄变了——不再是”机械键盘现在多少钱”，而是这种：

排行榜：”全部记录里最便宜的3条是哪些？”——等打折时你想盯的就是这些；
追新：”每个商品各自最新的一条记录是什么？”——画价格走势图前得先拿到”当前价”；
翻页：”20 条记录一页显示 5 条，第2页翻出来看看”——记录涨到 2000 条时，你不可能让程序把 2000 行全打印到屏幕上。

这三个需求，恰好就是今天两位主角的分工：

ORDER BY：给查询结果排队——按价格从低到高、按时间从新到旧，规则你定；
LIMIT（+OFFSET）：排队之后限量端上——只要前 3 条、一次只看一页。

先说个好消息：这两句 SQL 是整个 SQL 世界里最好学的部分之一，没有任何新概念，全是”在已有查询后面加半句”。再说个预警：它们有个非常隐蔽的坑，藏在”翻页”里——同值并列时，翻页会重复或漏行。第一次撞上的人几乎都以为程序闹鬼了。今天把疫苗也一起打了。

二、先备好料：20 条价格记录的练习场

为了把排序和翻页都演示清楚，这次把数据加量到 20 条：5 个商品，各查了 4 次价（10月的 4 个时间点）。上一篇修好的那条 299 保持修好的状态，”键盘膜”已经被删了，换成了新商品”人体工学椅”：

# setup.py — 备好练习用的 20 条价格记录
import sqlite3

conn = sqlite3.connect("prices.db")
cur = conn.cursor()

cur.execute("""
CREATE TABLE IF NOT EXISTS prices (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    product    TEXT    NOT NULL,
    price      REAL    NOT NULL,
    checked_at TEXT    NOT NULL
)
""")

cur.execute("DELETE FROM prices WHERE id > 0")

rows = [
    ("机械键盘",  399.0, "2026-10-08 09:00"),
    ("机械键盘",  359.0, "2026-10-15 09:00"),
    ("机械键盘",  329.0, "2026-10-22 09:00"),
    ("机械键盘",  299.0, "2026-10-25 09:00"),
    ("无线鼠标",  129.0, "2026-10-08 09:00"),
    ("无线鼠标",  119.0, "2026-10-15 09:00"),
    ("无线鼠标",   99.0, "2026-10-22 09:00"),
    ("无线鼠标",  109.0, "2026-10-29 09:00"),
    ("显示器",   1299.0, "2026-10-08 09:00"),
    ("显示器",   1199.0, "2026-10-15 09:00"),
    ("显示器",   1099.0, "2026-10-22 09:00"),
    ("显示器",    899.0, "2026-10-29 09:00"),
    ("耳机",      199.0, "2026-10-08 09:00"),
    ("耳机",      189.0, "2026-10-15 09:00"),
    ("耳机",      199.0, "2026-10-22 09:00"),
    ("耳机",      179.0, "2026-10-29 09:00"),
    ("人体工学椅", 1899.0, "2026-10-08 09:00"),
    ("人体工学椅", 1799.0, "2026-10-15 09:00"),
    ("人体工学椅", 1699.0, "2026-10-22 09:00"),
    ("人体工学椅", 1599.0, "2026-10-29 09:00"),
]
cur.executemany("INSERT INTO prices (product, price, checked_at) VALUES (?, ?, ?)", rows)
conn.commit()
conn.close()
print("练习数据就位：20 条价格记录")

跑完，20 条就位。注意所有价格列都是 REAL（数字型），这个细节在坑点环节有大用——先卖个关子。

三、ORDER BY：给查询结果排队

语法还是”在老查询后面加半句”：

SELECT 列 FROM 表 WHERE 条件 ORDER BY 列名 [ASC|DESC]

翻译成人话：”查完之后，按我说的规则给结果排个队再给我。”ASC 是升序（从小到大，默认，可省略），DESC 是降序（从大到小）。

排队1：最便宜的先看到。按价格从低到高：

# sort_basic.py — 三种最常见的排队规则
import sqlite3

conn = sqlite3.connect("prices.db")
cur = conn.cursor()

# 规则1：按价格从低到高（ASC 是默认，可省略）
cur.execute("SELECT product, price, checked_at FROM prices ORDER BY price ASC")
print("价格从低到高（前5条）：")
for row in cur.fetchmany(5):
    print(row)

输出：

价格从低到高（前5条）：
('无线鼠标', 99.0, '2026-10-22 09:00')
('无线鼠标', 109.0, '2026-10-29 09:00')
('无线鼠标', 119.0, '2026-10-15 09:00')
('无线鼠标', 129.0, '2026-10-08 09:00')
('耳机', 179.0, '2026-10-29 09:00')

fetchmany(5) 是”取前 5 条就走”（fetchall 的小弟，顺手认识一下）。最便宜的 4 条全是鼠标和耳机——符合直觉。

排队2：最新的排最前。“追新”场景，时间从新到旧：

# 规则2：按检查时间从新到旧（DESC）
cur.execute("SELECT product, price, checked_at FROM prices ORDER BY checked_at DESC")
print("最新在前（前5条）：")
for row in cur.fetchmany(5):
    print(row)

输出：

最新在前（前5条）：
('无线鼠标', 109.0, '2026-10-29 09:00')
('显示器', 899.0, '2026-10-29 09:00')
('耳机', 179.0, '2026-10-29 09:00')
('人体工学椅', 1599.0, '2026-10-29 09:00')
('机械键盘', 299.0, '2026-10-25 09:00')

注意前 4 行：都是 10-29，并列的几行谁先谁后没保证（坑点环节回来算这笔账）。但整体”新的在前”是对的——顺便留意，时间存在文本列里也能正确排序，因为 '2026-10-29 09:00' 这种格式字典序恰好等于时间序，这是第 49 篇埋下的福利。

排队3：多级排序——先按商品分组，再按价格排队。ORDER BY 后面可以列多个列，逗号隔开，先按第一个排，第一个相同的再按第二个排：

# 规则3：先按商品名排（分组效果），同商品内再按价格从低到高
cur.execute("""
    SELECT product, price, checked_at FROM prices
    ORDER BY product ASC, price ASC
""")
print("每个商品的全部价格（从低到高）：")
for row in cur.fetchall():
    print(row)

输出（截取片段）：

('人体工学椅', 1599.0, '2026-10-29 09:00')
('人体工学椅', 1699.0, '2026-10-22 09:00')
('人体工学椅', 1799.0, '2026-10-15 09:00')
('人体工学椅', 1899.0, '2026-10-08 09:00')
('显示器', 899.0, '2026-10-29 09:00')
('显示器', 1099.0, '2026-10-22 09:00')
...

同一个商品的价格整整齐齐排好队，商品之间按名字分块。这一招在”给每个商品出一份价格清单”时特别好用。想反着来？每个列后面单独标方向：ORDER BY product ASC, price DESC（商品名正序、商品内价格从高到低）——方向是每个列各管各的，别以为 DESC 会传染给全句。

四、LIMIT+OFFSET：限量端上来，一页一页翻

排队会了，”只要前几名”就简单了：

SELECT 列 FROM 表 ORDER BY 列 LIMIT 条数

限量1：最便宜的 3 条。排队 + 限量，两半句拼起来就是 TOP-N 查询：

# top3.py — 最便宜的3条：ORDER BY + LIMIT 的经典组合
import sqlite3

conn = sqlite3.connect("prices.db")
cur = conn.cursor()

cur.execute("""
    SELECT product, price, checked_at FROM prices
    ORDER BY price ASC
    LIMIT 3
""")
print("最便宜的3条：")
for row in cur.fetchall():
    print(row)
conn.close()

输出：

最便宜的3条：
('无线鼠标', 99.0, '2026-10-22 09:00')
('无线鼠标', 109.0, '2026-10-29 09:00')
('无线鼠标', 119.0, '2026-10-15 09:00')

就这么两行的事。“最贵的5件””最新的1条””销量前十”全是同一个模板：换个排序列、换个方向、换个条数。

限量2：翻页。“一页 5 条，翻到第 N 页”——LIMIT 只管”端几条”，翻页还得配一个 OFFSET（跳过几条）：

SELECT 列 FROM 表 ORDER BY 列 LIMIT 每页条数 OFFSET 要跳过的条数

人话：”先跳过前 X 条，再端 Y 条上来。”第 1 页跳过 0 条端 5 条，第 2 页跳过 5 条端 5 条，第 3 页跳过 10 条端 5 条……规律：跳过的条数 = (页码 – 1) × 每页条数。

# page.py — 翻页函数：一页5条，想看第几页看第几页
import sqlite3

def show_page(page, per_page=5):
    """显示第 page 页（页码从1开始）"""
    offset = (page - 1) * per_page
    conn = sqlite3.connect("prices.db")
    cur = conn.cursor()
    cur.execute("""
        SELECT id, product, price FROM prices
        ORDER BY price ASC, id ASC
        LIMIT ? OFFSET ?
    """, (per_page, offset))
    rows = cur.fetchall()
    conn.close()
    print(f"--- 第{page}页 ---")
    for row in rows:
        print(row)
    return rows

show_page(1)   # 第1页：最便宜的5条
show_page(2)   # 第2页：第6~10便宜的
show_page(4)   # 第4页：第16~20条，正好最后一页

输出：

--- 第1页 ---
(7, '无线鼠标', 99.0)
(8, '无线鼠标', 109.0)
(6, '无线鼠标', 119.0)
(5, '无线鼠标', 129.0)
(16, '耳机', 179.0)
--- 第2页 ---
(14, '耳机', 189.0)
(13, '耳机', 199.0)
(15, '耳机', 199.0)
(4, '机械键盘', 299.0)
(3, '机械键盘', 329.0)
--- 第4页 ---
(9, '显示器', 1299.0)
(20, '人体工学椅', 1599.0)
(19, '人体工学椅', 1699.0)
(18, '人体工学椅', 1799.0)
(17, '人体工学椅', 1899.0)

翻页三件套就齐了：排序（ORDER BY 确定顺序）→ 算偏移（(页码-1)×每页条数）→ 限量（LIMIT+OFFSET）。三件缺一不可——尤其第一件，待会儿坑点环节你会看到，不排序的翻页等于开盲盒。

顺便说个反面教材：MySQL 风格的 LIMIT 5, 10（逗号写法，意思是”跳5条取10条”）SQLite 也能吃，但参数顺序和人的直觉相反，忘了哪个是哪个就出事故。统一用 LIMIT n OFFSET m，每回都写明白，不省这个字。

五、上一篇的扣子：每个商品只看最新一条

上一篇预告里留的悬念，现在揭晓。需求：”5 个商品各自最新的一条记录”——先看笨办法，再看正解。

笨办法（你自己都能想到）：把 20 条按时间从新到旧排出来，肉眼/代码里每个商品挑第一条。20 条行，2000 条呢？

正解：一句 SQL，思路是”对每一行，问问它是不是自己商品里时间最新的那条“：

SELECT * FROM prices p
WHERE checked_at = (SELECT MAX(checked_at) FROM prices WHERE product = p.product)
ORDER BY product

拆开看：MAX(checked_at) 就是”挑出最大的时间”（MAX=最大值，聚合函数，先会用就行，后面系统讲）；括号里那半句对每一行分别算一次”这个商品的最新时间”；外层 WHERE 只留下”时间正好等于自家最新”的行。跑一下：

# latest.py — 每个商品只看最新一条
import sqlite3

conn = sqlite3.connect("prices.db")
cur = conn.cursor()
cur.execute("""
    SELECT product, price, checked_at FROM prices p
    WHERE checked_at = (SELECT MAX(checked_at) FROM prices WHERE product = p.product)
    ORDER BY product
""")
for row in cur.fetchall():
    print(row)
conn.close()

输出：

('人体工学椅', 1599.0, '2026-10-29 09:00')
('无线鼠标', 109.0, '2026-10-29 09:00')
('显示器', 899.0, '2026-10-29 09:00')
('机械键盘', 299.0, '2026-10-25 09:00')
('耳机', 179.0, '2026-10-29 09:00')

5 个商品，各一条，机械键盘停在 10-25（它就查到那次）——正是你要的”当前价”。看不懂没关系，能照着用就行；它的亲戚（GROUP BY 分组取极值、窗口函数）是 SQL 的进阶大招，混个眼熟，以后遇到”每组最好的一条”类需求，把这句话连同需求一起丢给 AI，让它照这个思路写。

六、三个必踩的坑（先打疫苗）

坑1：文本排序的”’10’ 比 ‘9’ 小”。这是排序坑里的头号选手。如果价格列偷懒存成了 TEXT，ORDER BY price 按字典序排：’1099.0′ 的第一个字符是 ‘1’，’899.0′ 的第一个字符是 ‘8’，于是 ‘1099’ 排在 ‘899’ 前面——便宜的显示器”垫底”，最贵的椅子可能排第一，整个排行榜全是错的，而且程序不报任何错。疫苗一句话：数值就用 REAL/INTEGER 列，排序交给数字规则。怎么自查？SELECT typeof(price) FROM prices LIMIT 1 看一眼列里存的是什么类型（SQLite 特有的 typeof，混个脸熟）。同理，金额、数量、成绩……凡是”要比大小的”，都不许存文本。

坑2：并列的行，翻页会”重复出现”或”怎么都翻不到”。这是今天最阴的坑，只在翻页时发作。看第 2 节的数据：耳机有两条 199.0（10-08 和 10-22）。如果你的翻页排序是 ORDER BY price（只有价格），这两条 199 并列、先后顺序不保证——数据库两次查询（第 1 页、第 2 页）完全可能给出不同的并列顺序，结果就是：某条 199 在第1页出现过、第2页又冒出来，而另一条 199 哪页都没有。数据没丢，是排序没钉死，OFFSET 按”跳过几条”算位置时踩了空。疫苗一句话：翻页的 ORDER BY 末尾永远追加一个唯一列兜底：

ORDER BY price ASC, id ASC   -- 价格并列时用 id 定先后，顺序就钉死了

第 4 节 page.py 里我已经悄悄写了 ORDER BY price ASC, id ASC——不是强迫症，是防这个坑。记住：分页排序必须唯一，哪怕排序列本身看起来”应该没有重复”。

坑3：SQL 三兄弟的出场顺序是定死的：先 WHERE，再 ORDER BY，最后 LIMIT。写 SQL 的语句顺序可以随意（SELECT ... ORDER BY ... WHERE 有些数据库也认），但执行顺序永远是筛选 → 排序 → 限量，理解反了就会写出这样的错：”最便宜的 3 个显示器”——是先挑出显示器再取前 3，不是先取全表前 3 再从里面找显示器（可能 3 条里一个显示器都没有，你得到空结果还以为没货）。顺手记两条衍生纪律：① OFFSET 算术从 0 起跳——第 1 页是 OFFSET 0，不是 1，页码忘减 1 会直接跳过第一页的 5 条；② 翻到不存在的页（比如 20 条数据要第 10 页）不报错、只是返回空列表，判断”是不是翻过头了”要靠 len(rows) == 0。

七、跟AI协作的正确姿势（排序分页特供版）

让需求带上”顺序 + 数量 + 口径”。“把 prices 表按价格从低到高排，只要前3条”是个好需求；”帮我查几个便宜的”是差需求——”便宜”是价格升序还是打折力度？”几个”是 3 还是 5？AI 只能瞎猜。三个要素齐了，SQL 基本就是白送。
让 AI 把排序规则”钉死到唯一”。凡是让它写分页/排名的代码，验收时盯一眼 ORDER BY 末尾有没有唯一列（id）。没有就打回去：”排序有并列值时翻页会重复或漏行，加 id 兜底。”——这句话说出去，你已经比一半的初级程序员严谨了。
TOP-N 需求让 AI 给”带方向的”答案。“最便宜的3个”和”最贵的3个”只差一个 DESC，但正是这类”差不多”最容易搞反。让 AI 给的代码里必须显式写出 ASC/DESC（不许省略默认值），你验收时口头复述一遍：”这是价格从低到高取前3，对吧？”对上再跑。
出错回炉带”数据 + 页码 + 排序”。“翻页翻乱了””第2页有重复”这类故障，把 ORDER BY 全文 + 页码和每页条数 + 两三条样例数据 贴给 AI，一次定位。只说”分页不对”，AI 也只能猜。

八、今日小练习

练习1：排行榜三连。用练习场的 20 条数据，各写一句 SQL：①价格从高到低排，列出前 5 条（想想这查的是什么——”历史最高价”们）；②按”商品名正序 + 时间倒序”排列全部记录，让每个商品的记录按时间从新到旧聚在一起；③只用 ORDER BY + LIMIT（不用 MAX），查出”整个表里最新的一条记录”是哪个商品的。

提示：①换个方向就换了个需求；②两级排序，ASC/DESC 各管各的；③时间倒序排第 1 条就是最新的——想想 LIMIT 几。

练习2：把翻页函数写扎实。照第 4 节写一个 show_page(page, per_page=5)，要求：① ORDER BY 末尾用 id 兜底（坑2的疫苗）；② 翻到空页时打印”这页没有数据了”而不是默默输出空；③ 打印每条记录时带上下标”第 (page-1)*per_page + i 条 / 共20条”，让你肉眼确认翻页无重复无遗漏。

提示：②判断 rows 是不是空列表；③用 enumerate(rows, 1) 给行编页内序号，再加偏移算全局序号。跑一遍 4 页，人工核对 20 条一条不多一条不少。

练习3（坑点自考）：先诊断，再对答案。下面每段都有毛病，不看文章说出病在哪、怎么改：①价格列建表时写的是 price TEXT，ORDER BY price 出来的榜单显示器排在鼠标前面；②分页语句是 ORDER BY checked_at LIMIT 5 OFFSET 5 翻第2页，发现有条记录第1页出现过、第2页又出现；③想要”最便宜的3个显示器”，写了 SELECT * FROM prices ORDER BY price LIMIT 3 WHERE product = '显示器'。

提示：一句对一个坑（文本排序、并列不钉死、三兄弟顺序）；③还附赠一个语法错误，一起找出来。

下篇预告

下一篇（第54篇）：多表关联 — JOIN，把两张表”对上号”拼成一张。到目前为止，所有数据都住在同一张表里。可现实是：商品信息一张表、价格记录一张表、订单一张表——”每个商品的当前价和它的品牌放一起看”就得让两张表手拉手。第 34 篇用 pandas merge 干过”两张表拼一张”，下一篇换 SQL 的 JOIN 再干一次：JOIN 的几种牵手方式、”对不上号”的行去哪了，还有那个著名的”关联之后行数暴增”之谜。

AI编程（五十三）：排序和分页 — ORDER BY+LIMIT，让答案排好队、分页端上来

一、真实场景：排行榜、翻页，和”只看最新一条”

二、先备好料：20 条价格记录的练习场

三、ORDER BY：给查询结果排队

四、LIMIT+OFFSET：限量端上来，一页一页翻

五、上一篇的扣子：每个商品只看最新一条

六、三个必踩的坑（先打疫苗）

七、跟AI协作的正确姿势（排序分页特供版）

八、今日小练习

下篇预告

评论

发表回复 取消回复

更多文章

攀岩运动简介

44.Semantic路由架构分析

AI编程（五十三）：排序和分页 — ORDER BY+LIMIT，让答案排好队、分页端上来

43.Moltbot架构深度分析

AI编程（五十二）：更新和删除 — UPDATE+DELETE，让数据改得了、删得掉

发表回复取消回复