RESEARCH NOTES / 关注与练习

研究

Database · Text-to-SQL

从一句问题，
到一个可信的答案。

关注查询如何表达意图，也关心结果为什么成立。

打开 SQL 实验

问题 / 表达 / 验证CC 的研究笔记

SQL 教学示例 · 在浏览器中运行与验证。

QUERY / RESULT

能运行，然后呢？

SQLite · 浏览器内运行

列出每位创作者的曲目数，包含 0 首，按 id 排序。

artists3 rows
id	name
1	Aster
2	Lumen
3	Moss

tracks3 rows
id	artist_id	title
101	1	Blue
102	1	Gold
103	2	Echo

候选 SQL

SELECT a.name, COUNT(*) AS tracks
FROM artists AS a
LEFT JOIN tracks AS t ON t.artist_id = a.id
GROUP BY a.id, a.name
ORDER BY a.id

实际结果

尚未运行

运行查询，看看哪些行不同。

参考 SQL

SELECT a.name, COUNT(t.id) AS tracks
FROM artists AS a
LEFT JOIN tracks AS t ON t.artist_id = a.id
GROUP BY a.id, a.name
ORDER BY a.id

预期结果

3 行

根据题意和全部示例数据得到的预期结果
比较状态	name	tracks
	Aster	2
	Lumen	1
	Moss	0

为什么会不同？

LEFT JOIN 为 Moss 留下一行，t.id 是 NULL。COUNT(*) 数行；COUNT(t.id) 只数非空值。 SQLite 文档 ↗

列出所有最高分曲目，保留并列，按曲名排序。

scores3 rows
track	points
Blue	95
Gold	95
Echo	88

候选 SQL

SELECT track, points
FROM scores
ORDER BY points DESC, track
LIMIT 1

实际结果

尚未运行

运行查询，看看哪些行不同。

参考 SQL

SELECT track, points
FROM scores
WHERE points = (SELECT MAX(points) FROM scores)
ORDER BY track

预期结果

2 行

根据题意和全部示例数据得到的预期结果
比较状态	track	points
	Blue	95
	Gold	95

为什么会不同？

LIMIT 1 只保留一行。先取最高分，再筛选同分曲目，才能保留全部并列。 SQLite 文档 ↗

列出尚未排练的曲目，忽略未指定曲目的记录，按 id 排序。

tracks3 rows
id	title
101	Blue
102	Gold
103	Echo

rehearsals2 rows
id	track_id
1	102
2	NULL

候选 SQL

SELECT t.id, t.title
FROM tracks AS t
WHERE t.id NOT IN (SELECT track_id FROM rehearsals)
ORDER BY t.id

实际结果

尚未运行

运行查询，看看哪些行不同。

参考 SQL

SELECT t.id, t.title
FROM tracks AS t
WHERE NOT EXISTS (
  SELECT 1 FROM rehearsals AS r WHERE r.track_id = t.id
)
ORDER BY t.id

预期结果

2 行

根据题意和全部示例数据得到的预期结果
比较状态	id	title
	101	Blue
	103	Echo

为什么会不同？

本例曲目 id 非空。子查询含 NULL，未命中的 NOT IN 得到未知值，WHERE 不保留；已命中的也被排除。NOT EXISTS 只检查匹配行，本例保留 Blue 和 Echo。 SQLite 文档 ↗

列出每位创作者的曲目数与排练次数，包含 0，按创作者 id 排序；排练按创作者记录，不关联具体曲目。

artists2 rows
id	name
1	Aster
2	Moss

tracks2 rows
id	artist_id	title
101	1	Blue
102	1	Gold

rehearsals2 rows
id	artist_id
1	1
2	1

候选 SQL

SELECT a.name, COUNT(t.id) AS tracks, COUNT(r.id) AS rehearsals
FROM artists AS a
LEFT JOIN tracks AS t ON t.artist_id = a.id
LEFT JOIN rehearsals AS r ON r.artist_id = a.id
GROUP BY a.id, a.name
ORDER BY a.id

实际结果

尚未运行

运行查询，看看哪些行不同。

参考 SQL

SELECT a.name, COUNT(DISTINCT t.id) AS tracks, COUNT(DISTINCT r.id) AS rehearsals
FROM artists AS a
LEFT JOIN tracks AS t ON t.artist_id = a.id
LEFT JOIN rehearsals AS r ON r.artist_id = a.id
GROUP BY a.id, a.name
ORDER BY a.id

预期结果

2 行

根据题意和全部示例数据得到的预期结果
比较状态	name	tracks	rehearsals
	Aster	2	2
	Moss	0	0

为什么会不同？

Aster 的 2 首曲目与 2 次排练组合成 4 行，直接 COUNT 会重复计数。本例按各表主键 DISTINCT，分别数曲目与排练；同名曲目仍是不同实体。这只修复本例的实体计数，不能推广为 SUM 的通用修复。 SQLite 文档 ↗

只读 SELECT / WITH · 不含分号 · 最多 100 行 · Ctrl / ⌘ + Enter 运行

运行边界与来源

本站原创小数据，比较只对本例成立。列按位置对应，值类型、重复次数和顺序都参与比较。

首次运行按需下载约 705 KB 的本地 SQLite 资源；需要支持 WebAssembly 和 Worker 的浏览器。查询仅在当前浏览器运行，不自动保存或上传；导出会保存到本机文件。导入只恢复数据集与 SQL，不自动运行；仅支持匹配版本的内置数据集。随机数、当前时间或未指定排序的查询不保证结果相同。离线时不保证可用。

跨数据集验证依次检查最多 8 个固定数据集；每个数据集限时 1.5 秒，载入另计。比较值、重复次数与行顺序；全部一致也不代表 SQL 普遍等价。

查询限 4,000 字符；所有分号都需删除，包括注释和字符串中的分号。执行超过 1.5 秒即停止；SQLite 分配限 16 MiB，结果限 16 列，每格最多 512 字符。

sql.js 1.14.2 / MIT ↗

用自己的表比较 SQLCSV / TSV · 只在当前页面

选一张小表，分别运行 A / B，看看当前数据上的结果是否一致。

分隔符选择文件

UTF-8 · 首行为表头 · 最多 256 KiB、500 行、16 列。选择分隔符后再选文件。

选择文件后先预览，不会自动运行。

SQL A SQL B

只读 SELECT / WITH · 不含分号 · Ctrl / ⌘ + Enter 运行

等待选择文件。

数据、比较与隐私边界

原表头仅用于显示，不作为 SQL 标识符。空单元格保留为空字符串；数字、日期和 NULL 字样也都是 TEXT，需要时显式转换类型。每格最多 512 字符，表头最多 128 字符。

按列位置比较类型、值、重复次数与行顺序；列标题不参与判断；INTEGER 1 与 REAL 1.0 属于不同类型。REAL 使用二进制双精度，并非任意精度小数。最多显示每侧 100 行，任一侧截断时不作一致判断。当前数据一致不代表 SQL 普遍等价，也不代表 A 或 B 符合你的题意。没有 ORDER BY、随机数或当前时间可能产生不稳定结果。

文件数据与 SQL 只保留在当前页面内存，不进入分享链接、历史、导入导出或剪贴板，也不会作为遥测上传。首次运行可能载入本站 SQLite 资源；离线时不保证可用。关闭此折叠面板不会运行查询；清除或刷新页面会丢弃数据。

需要支持 Worker 和 WebAssembly 的浏览器。SQLite 载入最多等待 10 秒，建表与完整 A / B 执行合计最多 1.5 秒。可停止后修改再试。每条 SQL 最多 4,000 字符，结果最多 16 列、每格 512 字符；SQLite 分配限 16 MiB，不是浏览器总内存上限。

研究

能运行，然后呢？

列出每位创作者的曲目数，包含 0 首，按 id 排序。

候选 SQL

实际结果

参考 SQL

预期结果

列出所有最高分曲目，保留并列，按曲名排序。

候选 SQL

实际结果

参考 SQL

预期结果

列出尚未排练的曲目，忽略未指定曲目的记录，按 id 排序。

候选 SQL

实际结果

参考 SQL

预期结果

列出每位创作者的曲目数与排练次数，包含 0，按创作者 id 排序；排练按创作者记录，不关联具体曲目。

候选 SQL

实际结果

参考 SQL

预期结果

local_data

A 的结果

B 的结果

样本 A

样本 B

去哪里？

搜索

图像来源

节拍

后台操作小抄