限制 MYSQL 中记录的出现次数 [英] Limit occurrence of records in MYSQL

问题描述

我创建了一个视图，它结合了两个表来显示电子邮件地址和位置详细信息的列表，即

I have created a view that combines two tables to show a list of email addresses and location details i.e.

电子邮件地址一 |位置 1
电子邮件地址一 |位置 2
电子邮件地址一 |位置 3
电子邮件地址一 |位置 4
电子邮件地址二 |位置 1
电子邮件地址二 |位置 2

Email Address One | Location 1
Email Address One | Location 2
Email Address One | Location 3
Email Address One | Location 4
Email Address Two | Location 1
Email Address Two | Location 2

这会循环处理几百封电子邮件.

This loops through a few hundred emails.

不幸的是，我需要限制每个电子邮件地址，因此每个电子邮件地址只选择了 5 条记录 - 目前某些记录返回了 70 多条记录.

Unfortunately I need to limit each email address so only 5 records for each are selected - at present there are more than 70 records being returned for some records.

例如，是否可以将每个电子邮件地址的返回数量限制为最多 5 个?

Is it possible to limit the number of each email address being returned to a maximum of 5 for example?

推荐答案

您需要为返回结果中的每个电子邮件地址添加一个排名，例如

You need to add a rank to each email address in your return result, E.g

1 | Email Address One | Location 1
2 | Email Address One | Location 2
3 | Email Address One | Location 3
4 | Email Address One | Location 4
1 | Email Address Two | Location 1
2 | Email Address Two | Location 2

一旦你有了那个排名，你就可以添加一个 where rank <5. 如果您使用 group 子句来创建排名，则必须使用 having 子句，例如 have rank <;5.

once you have that rank, you can add a where rank < 5. if you use a group clause to create the rank you will have to use a having clause e.g having rank < 5.

很遗憾，我不知道您的表格布局或您的查询来创建上述结果，因此我无法帮助您创建排名.

Unfortunately I don't know your table's layout or your query to create the said results, therefore I cannot help you create the rank.

这尚未经过测试，但可能会奏效(假设 id 是位置的唯一标识符):

This has not been tested but might do the trick (assuming id is locations's unique identifier):

select 
    user.email AS email,
    locations.locationname AS locationname,
    count(A.id) AS rank
from 
    locations
    join user on (location.department = user.department)
    join locations A on A.id < locations.id
group by
    locations.id
having
    rank < 5
order by
    locations.id

这篇关于限制 MYSQL 中记录的出现次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！