如何将 SQL 结果集限制为不太常见的项目

本文介绍了如何将 SQL 结果集限制为不太常见的项目的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

问题:我有一份姓名和地址列表.一些名称(人)与其他名称(人)具有相同的地址(街道、邮政编码、城镇).我想选择所有地址不超过三个的名称，其余的前三个名称中的每一个都指向同一个地址.示例:

Problem: I have a list of names and addresses. Some names (persons) have the same address (street, zip code, town) like others. I want to select all those names with addresses with no more than three occurrences and from the rest the first three names each of a bunch pointing to the same address. Example:

Albert | Adr1 Berta | Adr1 Cesar | Adr1 Donald | Adr1 Eric | Adr2 Fritz | Adr2 Gerd | Adr2 Henry | Adr3

结果集应该是

Albert | Adr1 Berta | Adr1 Cesar | Adr1 Eric | Adr2 Fritz | Adr2 Gerd | Adr2 Henry | Adr3

Donald 失踪了，因为他是同一个地址组中的第四个.这个结果可以用 UNION 和子查询来实现吗?类似的东西

Donald is missing because he is the 4th of a group with the same address. Can this result be achieved with UNIONs and subqueries? Something like

select * from addresses where address in (select address from addresses group by address having count(address) <= 3) UNION select * from addresses where address in (select address from addresses group by address having count(address) > 3 limit 3)

我知道这个查询是错误的，因为它限制了超过 3 次出现的地址的完整结果集.我想知道这是否可以在带有 UNION 和子查询的单个 SELECT 中完成.我现在将在程序上使用 PHP/MySQL 来完成，但只是为了好玩，我会对仅 SQL 的解决方案感兴趣.

I know that this query is wrong because it limits the complete result set of addresses with more than 3 occurences. I wonder if this can be done in a single SELECT with UNIONs and subqueries. I will do it now procedurally with PHP/MySQL, but just for fun would be interested in an SQL only solution.

我查看了 SQL 查询，其中限制了一个表中的行，而不是结果集，但这并不反映我的情况 - 是吗?

I had a look at SQL query with limit on rows from one table, not the result set, but this does not reflect my situation - or does it?

推荐答案

你可以试试类似

SELECT PersonName, Address FROM ( SELECT *, (SELECT COUNT(1) FROM addresses WHERE Address = a.Address AND PersonName < a.PersonName) CountLess FROM addresses a ) sub WHERE sub.CountLess <= 2