文档教程知识库【免费下载链接】til:memo: Today I Learned项目地址https://gitcode.com/gh_mirrors/ti/til点击查看免费下载本篇技术指南讲解 PostgreSQL 中一个高频实战场景在 one-to-many一对多表关联下如何通过join、group by与having三者的组合筛选出拥有多条关联记录的父表记录。以authors作者与books书籍为例读完你可以掌握一条可直接复制运行、并能在多种业务场景查重复、按类型统计、过滤聚合结果中举一反三的查询范式。一对多关联与本题的查询目标关系型数据库中最常见的表关联形态之一就是 one-to-many 关系。典型例子一个书架数据库里存在authors表每位作者可以关联多条books表中的记录。这条关系通过books表上的author_id外键列体现它指向authors.id。这种结构的建表方式与本仓库中 add-foreign-key-constraint-without-a-full-lock.md 展示的外键定义一脉相承alter table books add constraint fk_books_authors foreign key (author_id) references authors(id) not valid;需求随之而来如何找出那些不是 0 本、也不是 1 本而是拥有多本书的作者答案是先做 join再在聚合结果上追加一个having子句。核心查询join group by having直接给出可运行的查询select authors.id, authors.name, count(books.id) from authors join books on authors.id books.author_id group by authors.id having count(books.id) 2;这条查询会返回所有拥有 ≥ 2 本书的作者每一行包含作者 id、作者姓名以及他名下的书籍数量count。执行思路分三步join books 到 authors把书籍与作者按外键authors.id books.author_id连接起来得到每位作者 × 他的每一本书的宽表group by authors.id按作者 id 分组产出每位作者唯一的一组记录count 聚合用count(books.id)把每组内的多本书聚合成一个数字——注意books.id是主键、非空因此它比count(*)更精确地反映书的数量。最后一步是关键having子句之所以必要是因为它是我们在 SQL 中对聚合值aggregate进行过滤的手段。普通的where在分组发生前逐行过滤而这里要过滤的是聚合计算出来的结果只能交给having。为什么是 having 而不是 wherewhere与having的执行时序不同where在group by之前、针对每一行原始记录求值having在group by与聚合计算之后、针对每一组求值。因此count(books.id) 2这类涉及聚合函数的条件必须写在having中。这个原则在本仓库另一篇文档 find-records-that-contain-duplicate-values.md 中有同样体现——那里用having count(*) 1来抓取重复记录select email, count(*) from mailing_list group by email having count(*) 1 order by email;两者共享同一个语法骨架group by 分组键 having 聚合条件。示例数据与预期输出沿用本仓库文档中反复出现的authors/books数据模型执行上面的查询结果大致如下id | name | count ------------------- 1 | J.K.R | 7 3 | G.R.R | 5 8 | S.K. | 2只有书籍数 ≥ 2 的作者才会出现在结果集中写了 0 本或 1 本的作者比如尚未出版任何书籍的新作者不会出现——这正是join内连接与having过滤共同作用的结果内连接天然丢弃没有书的作者having再丢弃只有 1 本书的作者。阈值与变体 2、 N 与 BETWEENhaving count(...) 2中的2是阈值可自由调整以满足不同业务口径业务诉求having 写法至少 2 条关联记录having count(books.id) 2恰好 N 条关联记录having count(books.id) N35 条关联记录having count(books.id) between 3 and 5零关联记录的父表记录改用left join并having count(books.id) 0最后一种值得一提若想反向找出没有关联任何书籍的作者需要把join换成left join否则内连接会直接把这类作者过滤掉having count(books.id) 0便无从谈起。计数聚合的进阶count 之外的选择count只是聚合函数之一。当需求升级——比如想知道每位作者名下有多少本可借阅的书本仓库的 count-the-number-of-trues-in-an-aggregate-query.md 给出了用sum case实现的方案select author_id, sum(case when available then 1 else 0 end) from books group by author_id;同理如果你希望结果集中不仅展示数量还把每本书的书名一并带上可以用array_agg把关联记录的某列聚合进一个数组详见 aggregate-a-column-into-an-array.mdselect authors.id, count(books.id), array_agg(books.title) from authors join books on authors.id books.author_id group by authors.id having count(books.id) 2;对结果排序当作者数量很大时可以像 count-how-many-records-there-are-of-each-type.md 中那样用 select 列表的位置索引给聚合结果排序例如按书籍数量降序展示select authors.id, authors.name, count(books.id) from authors join books on authors.id books.author_id group by authors.id having count(books.id) 2 order by 3 desc;这里的3引用 select 中的第三个参数count(books.id)避免重复书写整段聚合表达式。性能与索引建议这条查询在数据量大时的执行路径依赖两个点join 条件与外键。建议确保books.author_id上有索引外键约束通常会自动创建否则 join 会演变成全表扫描确保group by使用的authors.id是主键PostgreSQL 可借助主键索引高效分组。若需要在大表上为author_id补建外键约束以保障数据完整性同时希望降低锁表影响可参考 add-foreign-key-constraint-without-a-full-lock.md 中先not valid后validate constraint的两步法将加锁成本控制在SHARE UPDATE EXCLUSIVE级别。小结查找拥有多条关联记录的父表记录是一个可复用的 SQL 模式其本质是用join建立关联用group by按父表主键聚合用count或其他聚合函数度量关联记录数用having对聚合值设定过滤阈值。掌握这一组合你便能同时解决查多本书的作者查重复邮箱按类型统计数量等一系列基于分组聚合的过滤问题相关兄弟主题可继续在仓库的 postgres 目录下对照阅读。赞分享文档教程知识库【免费下载链接】til:memo: Today I Learned项目地址https://gitcode.com/gh_mirrors/ti/til点击查看免费下载相关推荐RevokeMsgPatcher深度解析Windows平台微信/QQ/TIM防撤回完整方案RevokeMsgPatcher深度解析Windows平台微信/QQ/TIM防撤回完整方案 在日常使用微信、QQ、TIM等即时通讯软件时你是否经常遇到重要消桌面应用即时通讯掌握GORM分组查询从Group By到Having的完整实战指南掌握GORM分组查询从Group By到Having的完整实战指南 GORM作为Golang生态中最受欢迎的ORM库以其开发者友好的设计理念简化了数据库操作后端数据库ORMPostgreSQL 使用 GROUP BY 与 COUNT 统计每种类型Type的记录数PostgreSQL 使用 GROUP BY 与 COUNT 统计每种类型Type的记录数 导读 在 PostgreSQL 日常开发与数据分析中最常遇到的文档教程知识库创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考