MySQL的-需要两个限制? [英] mysql - need two limits?

问题描述

给定一个包含由多个地理区域组成的组织的成员在过去几年中获得的奖励的表，什么样的mysql查询将显示每个区域中收入最高的获奖者?通过以下查询，我可以轻松获得所有地区的前十名获奖者:

SELECT Membername,  count(Award)as Number FROM awards 
GROUP BY Membername
ORDER BY Number desc 
LIMIT 10

但是我需要列出每个地区收入最高的列表(其中大约有90个)，而我还没有得到正确的答案.

我尝试过:

SELECT Membername,  District, count(Award)as Number FROM awards 
GROUP BY Membername, District
ORDER BY Number desc, District 
LIMIT 90

它具有准确的成员计数，但每个区域未显示一个，因此某些区域显示不止一次.如何获得列出每个地区收入最高的收入，而每个地区仅出现一次的信息?

解决方案

您必须通过在每个分区上应用等级"来实现，然后仅按照等级= 1进行抓取...连接位置处的@LastDistrict如果地区基于ID，则默认为零.如果district是基于char的，则只需将其更改为="即可匹配数据类型.

说明正在发生的事情. "AwardCounts"预查询对每个地区和成员执行整个查询，但是奖励计数却很多.然后，按地区和成员奖励计数(降序)排序，从而将最高奖励计数置于每个区域的第一位.

与另一个伪造的别名"SQLVars"连接在一起，该别名仅为名为@RankSeq和@LastDistrict的查询创建内联变量.因此，第一次进入时，"DistRankSeq"将成为第一个区的1，然后将"@LastDistrict"与该区的值相加.相同分区的下一个条目(因为顺序将是正确的)将被分配为2，然后是3，等等，以此类推.经过测试后，排名将重新设置为1，然后重新开始.因此，您可以使一个区有100个成员，另一个区有5个成员，另一个区有17个成员……

因此，您的最终查询将所有这些对象都具有各自的排名...现在，应用HAVING最终的地区排名= 1 ...这样做，您还可以调整必须获取每个地区的前3名成员的权限(例如)...

select
      AwardCounts.District,
      AwardCounts.MemberName,
      AwardCounts.memberAwards,
      @RankSeq := if( @LastDistrict = AwardCounts.District, @RankSeq +1, 1 ) DistRankSeq,
      @LastDistrict := AwardCounts.District as ignoreIt
   from
      ( select 
              a.district,
              a.membername,
              count(*) as memberAwards
           from
              Awards a
           group by
              a.district,
              a.membername
           order by
              a.district,
              memberAwards desc ) AwardCounts

      JOIN (select @RankSeq := 0, @LastDistrict = 0 ) SQLVars
   HAVING
      DistRankSeq = 1

按反馈编辑 如果是花费时间的聚合，那么我将执行以下操作.创建一个新表，其中仅包含每个分区的聚合，该分区的名称和初始等级.在将任何新记录添加到该表中时，触发器将在汇总表计数中添加一个，然后检查该人在其所在区域内的位置并重新更新其新的排名位置.您可以更进一步，并在每个分区表中创建另一个仅由"TOP"成员组成的表，该表在每个分区中均带有该人的名字.当一个新的人达到最高职位时，他们的名字就会放到桌子上，覆盖最后一个人.

Given a table containing awards earned over several years by members of an organization that consists of multiple geographic districts, what mysql query would show the top award earner in each district? I can easily get the top ten award earners across all districts with this query:

SELECT Membername,  count(Award)as Number FROM awards 
GROUP BY Membername
ORDER BY Number desc 
LIMIT 10

But I need a list with the top earner for each district (there are about 90 of them), and I haven't gotten it right yet.

I tried this:

SELECT Membername,  District, count(Award)as Number FROM awards 
GROUP BY Membername, District
ORDER BY Number desc, District 
LIMIT 90

It has accurate counts for the members, but isn't showing one per district, so some districts show up more than once. How do I get it to list the top earner per district, with each district showing up just once?

解决方案

You'll have to do it by applying a "rank" per district, then only grab per rank = 1... The @LastDistrict at the join location is defaulted to zero, in case the district is based on an ID. If district is char based, you can just change it to = "" instead to match the data type.

To clarify what is happening. The "AwardCounts" pre query does the entire query per district and member with however many award counts. Then, ordered by district and member award count (descending), thus putting the highest award count at the first position per district.

That is joined to another bogus alias "SQLVars" which just creates inline variables to the query called @RankSeq and @LastDistrict. So, the first time in, the "DistRankSeq" will become a 1 for the first district, then prime the "@LastDistrict" with the value of the district. The next entry for the same district (since it will be in proper sequence order) will be assigned the rank of 2, then 3, etc... When there is a change from whatever the "LAST" District was to the new record being tested, the rank gets set back to 1 and starts over again. So you could have one district with 100 members, another with 5, another with 17...

So, your final query has all of them with their respective ranks... Now, apply the HAVING the final district rank = 1... Doing this, you could also adjust the having to get the top 3 members per district (for example)...

select
      AwardCounts.District,
      AwardCounts.MemberName,
      AwardCounts.memberAwards,
      @RankSeq := if( @LastDistrict = AwardCounts.District, @RankSeq +1, 1 ) DistRankSeq,
      @LastDistrict := AwardCounts.District as ignoreIt
   from
      ( select 
              a.district,
              a.membername,
              count(*) as memberAwards
           from
              Awards a
           group by
              a.district,
              a.membername
           order by
              a.district,
              memberAwards desc ) AwardCounts

      JOIN (select @RankSeq := 0, @LastDistrict = 0 ) SQLVars
   HAVING
      DistRankSeq = 1

EDIT PER FEEDBACK If its the aggregation thats taking the time, then I would do the following. Create a new table with nothing but the aggregations per district, name and initial rank for the district. As any new record is added to this table, the trigger then adds one to the aggregate table count, then checks where that person is within their district and re-updates its new rank position. You could take it a step further and have another table of just "TOP" member per district table that is one per district with the person's name. When a new person hits the top position, their name is put in the table, overwriting whoever was there last.

这篇关于MySQL的-需要两个限制?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！