如何将MySQL查询结果分解为类别，每个类别有特定的行数?

Problem: I want to list n number of games from each genre (order not important)

问题:我想列出每种类型的n个游戏(顺序不重要)

The following MySQL query resides inside a ColdFusion function. It is meant to list all games under a platform (for example, list all PS3 games; list all Xbox 360 games; etc...). The variable for PlatformID is passed through the URL. I have 9 genres, and I would like to list 10 games from each genre.

下面的MySQL查询驻留在ColdFusion函数中。它将列出平台下的所有游戏(例如，列出所有PS3游戏;列出所有Xbox 360游戏;等等……)。PlatformID的变量通过URL传递。我有9个流派，我想列出每种流派的10个游戏。

        SELECT
            games.GameID AS GameID,
            games.GameReleaseDate AS rDate,                
            titles.TitleName AS tName,
            titles.TitleShortDescription AS sDesc,
            genres.GenreName AS gName,
            platforms.PlatformID,
            platforms.PlatformName AS pName,
            platforms.PlatformAbbreviation AS pAbbr
        FROM
            (((games join titles on((games.TitleID = titles.TitleID))) join genres on((genres.GenreID = games.GenreID))) join platforms on((platforms.PlatformID = games.PlatformID)))
        WHERE
            (games.PlatformID = '#ARGUMENTS.PlatformID#')
        ORDER BY
            GenreName ASC,
            GameReleaseDate DESC

Once the query results come back I group them in ColdFusion as follows:

查询结果返回后，我将它们分组为ColdFusion，如下所示:

<cfoutput query="ListGames" group="gName"> (first loop which lists genres)
#ListGames.gName#
      <cfoutput> (nested loop which lists games)
      #ListGames.tName#
      </cfoutput>
</cfoutput>

The problem is that I only want 10 games from each genre to be listed. If I place a "limit" of 50 in the SQL, I will get ~ 50 games of the same genre (depending on how much games of that genre there are). The second issue is I don't want the overload of querying the database for all games when each person will only look at a few.

问题是，我只希望列出每种类型的10款游戏。如果我在SQL中设置了50的“限制”，我将会得到相同类型的50个游戏(取决于该类型的游戏有多少)。第二个问题是，我不希望在每个人只查看少数游戏的情况下，对所有游戏的数据库进行过多的查询。

What is the correct way to do this?

正确的做法是什么?

Many thanks!

很多谢谢!

5 个解决方案

#1

You could do a Coldfusion query of queries, but I believe that the solution resides in MySQL. That way you have the best control over the results you get back and frankly MySQL will sort and group this type of data much faster than Coldfusion.

您可以对查询执行Coldfusion查询，但我认为解决方案驻留在MySQL中。通过这种方式，你可以对返回的结果进行最好的控制，坦率地说，MySQL会比Coldfusion更快地对这类数据进行排序和分组。

Either you could get fancy with some grouping, limit and possibly a subselect. Alternately you can split your select statements up into one query per genre. I don't know how many people you'll have viewing this database, but 9 queries that each return 10 results is kids play for the database server. I'm sure it would be preferable to have the 90 results return in a single query for simplicity of output. That can be done, but the SELECT statement is going to be much more complicated. If you go that route, I'd recommend a stored procedure.

你可以用一些分组，极限，甚至是一个子选择。或者，您可以将select语句分割为每个类型的一个查询。我不知道有多少人会浏览这个数据库，但是有9个查询每次返回10个结果，都是孩子们为数据库服务器玩的游戏。我确信，为了简化输出，最好在一个查询中返回90个结果。这是可以做到的，但是SELECT语句要复杂得多。如果您采用这种方式，我建议您使用存储过程。

#2

I'm a little rusty so I'm sure there's some bugs and syntax errors in here. This stored procedure should create a temporary table with the data you're looking for

我有点生疏了，所以我肯定这里有一些错误和语法错误。这个存储过程应该创建一个包含您正在查找的数据的临时表

CREATE PROCEDURE topbygenre(num INT, platformID INT)
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE a INT;
  DECLARE cur CURSOR FOR SELECT GenreID FROM genres;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  DECLARE first INT DEFAULT 1;

  OPEN cur;

  REPEAT
    FETCH cur INTO a;
    IF NOT done THEN
      IF first THEN
        CREATE TEMPORARY TABLE TopGames
        SELECT
          games.GameID AS GameID,
          games.GameReleaseDate AS rDate,                
          titles.TitleName AS tName,
          titles.TitleShortDescription AS sDesc,
          genres.GenreName AS gName,
          platforms.PlatformID,
          platforms.PlatformName AS pName,
          platforms.PlatformAbbreviation AS pAbbr
        FROM
          (((games join titles on((games.TitleID = titles.TitleID))) join genres on((genres.GenreID = games.GenreID))) join platforms on((platforms.PlatformID = games.PlatformID)))
        WHERE
          (games.PlatformID = platformID)
          AND games.GenreID = a
        ORDER BY
          GenreName ASC,
          GameReleaseDate DESC
        LIMIT num;
        SET first = 0;
      ELSE
        INSERT INTO TopGames
        SELECT
          games.GameID AS GameID,
          games.GameReleaseDate AS rDate,                
          titles.TitleName AS tName,
          titles.TitleShortDescription AS sDesc,
          genres.GenreName AS gName,
          platforms.PlatformID,
          platforms.PlatformName AS pName,
          platforms.PlatformAbbreviation AS pAbbr
        FROM
          (((games join titles on((games.TitleID = titles.TitleID))) join genres on((genres.GenreID = games.GenreID))) join platforms on((platforms.PlatformID = games.PlatformID)))
        WHERE
          (games.PlatformID = platformID)
          AND games.GenreID = a
        ORDER BY
          GenreName ASC,
          GameReleaseDate DESC
        LIMIT num;
      END IF;
    END IF;
  UNTIL done END REPEAT;

  CLOSE cur;
END

Then execute tihs every time you need the data:

然后每次你需要数据时执行tihs:

topbygenre(10, '#ARGUMENTS.PlatformID#');
select * from TopGames;

#3

In Oracle I would probably play with Groups HAVING(ROWNUM) <= 10 but I do not think MYSQL supports that.

在Oracle中，我可能会使用(ROWNUM) <= 10的组，但我认为MYSQL不支持这一点。

If you have too many rows to just pull all games down and iterate in group logic then I agree with the above poster that breaking it out into separate queries while not optimum may be fine if you are not running a lot of concurrent users and use a bit of caching.

如果你有太多的行就把所有的游戏和组迭代逻辑然后我同意上面的海报,将它分解成单独的查询,而不是最优可能很好如果你不运行大量的并发用户和使用的缓存。

My first try would be something like this - while not tested may give you some ideas. so let me know if this is close - at least it is simple (note the maxrows="10" and check that it respects the Order By)...

我的第一次尝试是这样的——虽然没有经过测试，但可能会给你一些建议。因此，请让我知道这是否接近——至少它很简单(注意maxrows="10"并检查它是否符合Order By)……

 <cfquery name="Genres" datasource="#Application.DB#" cachedWithin="#createTimeSpan(0,0,30,0)#">
 SELECT 
   Distinct (GenreName) as UniqueGenreName
 FROM
   games join genres on(genres.GenreID = games.GenreID)
 WHERE
   games.PlatformID = <CFQUERYPARAM VALUE="#ARGUMENTS.PlatformID#" CFSQLTYPE="CF_SQL_VARCHAR">
 ORDER BY
   GenreName
 </cfquery>

 <!--- Table header here --->

 <cfloop query ="Genres">

   <cfquery name="SubGenres" datasource="#Application.DB#" maxrows="10" cachedWithin="#createTimeSpan(0,0,30,0)#">
    SELECT
             games.GameID AS GameID,
             games.GameReleaseDate AS rDate,                
             titles.TitleName AS tName,
             titles.TitleShortDescription AS sDesc,
             genres.GenreName AS gName,
             platforms.PlatformID,
             platforms.PlatformName AS pName,
             platforms.PlatformAbbreviation AS pAbbr
         FROM
             (((games join titles on((games.TitleID = titles.TitleID))) join genres on((genres.GenreID = games.GenreID))) join platforms on((platforms.PlatformID = games.PlatformID)))
         WHERE
             games.PlatformID = <CFQUERYPARAM VALUE="#ARGUMENTS.PlatformID#" CFSQLTYPE="CF_SQL_VARCHAR"> AND genres.GenreName = <CFQUERYPARAM VALUE="#UniqueGenreName#" CFSQLTYPE="CF_SQL_VARCHAR">
         ORDER BY
             GameReleaseDate DESC
    </cfquery>

    <cfoutput query ="SubGenres">
    <!--- Table rows here --->
    </cfoutput>

 </cfloop>

 <!--- Table footer here --->

#4

You say you want 10 games from each genre, but don't specify which 10 games you want. I assume from the ordering (GenreName, GameReleaseDate) that you want to display the 10 most recently released games.

你说你想要10个游戏类型，但不要指定你想要哪10个。我假设您想要显示最近发布的10款游戏的排序(GenreName, GameReleaseDate)。

It sounds like you want to use a SELECT TOP n - type clause. Someone else on SO asked about a database-agnostic method for selecting the top n records.

听起来您想要使用SELECT TOP n - type子句。另外有人问了一个与数据库无关的方法来选择前n条记录。

Using the answer from that question, you could try this:

用这个问题的答案，你可以试试:

    SELECT
        games.GameID AS GameID,
        games.GameReleaseDate AS rDate,                
        titles.TitleName AS tName,
        titles.TitleShortDescription AS sDesc,
        genres.GenreName AS gName,
        platforms.PlatformID,
        platforms.PlatformName AS pName,
        platforms.PlatformAbbreviation AS pAbbr
    FROM
        (((games join titles on((games.TitleID = titles.TitleID))) join genres on((genres.GenreID = games.GenreID))) join platforms on((platforms.PlatformID = games.PlatformID)))
    WHERE
        (games.PlatformID = '#ARGUMENTS.PlatformID#')
        AND (SELECT COUNT(*) FROM games AS NewerGames where NewerGames.PlatformID = games.PlatformID AND games.GenreID=NewerGames.GenreID AND (NewerGames.GameReleaseDate < Games.GameReleaseDate OR (NewerGames.GameReleaseDate = Games.GameReleaseDate AND NewerGames.GameID > games.GameID))) <= 10
    ORDER BY
        GenreName ASC,
        GameReleaseDate DESC

I haven't tested it but I'm pretty sure it should work. Give it a try, if it works it's a simple answer to the problem.

我还没有对它进行测试，但我很确定它应该可以工作。试一试，如果可行的话，这是问题的简单答案。

Note that one of the criteria -- NewerGames.GameID > games.GameID is there solely to avoid the problem of there being more than one game with the same release date. In these cases, the game that has a higher id (and therefore, in theory, slightly "younger") will get filtered out last.

注意其中一个标准——NewerGames。GameID >游戏。GameID的存在仅仅是为了避免同一个发布日期有多个游戏的问题。在这些情况下，id更高的游戏(因此，理论上，稍微“年轻一些”)将在最后被过滤掉。

#5

HTH http://blog.aharbick.com/2006/09/mysql-groupwise-limiting.html

#1

#2