All geek questions in one place

How to break MySQL query results into categories, each of which has a certain number of rows?

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

The following MySQL query is inside the ColdFusion function. It is intended to list all games under the platform (for example, a list of all games for the PS3, a list of 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.

        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

As soon as the query results return, I group them in ColdFusion as follows:

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

, 10 . "" 50 SQL, ~ 50 ( , ). , , .

?

!

+3
5

Coldfusion, , MySQL. , , , , , MySQL , Coldfusion.

- , , , . . , ​​ , 9 , 10 , - . , , 90 . , SELECT . , .

+1

, , . ,

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

, :

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

Oracle , , HAVING (ROWNUM) <= 10, , MYSQL .

, , , , , , .

- - - . , - , ( maxrows = "10" , 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 --->
+1

, 10 , , 10 . (GenreName, GameReleaseDate), 10 .

, SELECT TOP n - type. - SO - n .

, :

    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

, , . , , .

, - NewerGames.GameID > games.GameID , , . , id (, , , "" ), .

+1

Source: https://habr.com/ru/post/1735745/

More articles:


All Articles