如何将SQL结果集限制为不太常见的项目 [英] How to limit an SQL result set to not too common items

问题描述

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

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

结果集应为

The result set should be

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

这篇关于如何将SQL结果集限制为不太常见的项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！