blocks|key|813041|text|要使用你提到的帖子中的技巧，你需要一种方法来订购游戏。他们用的是文章日期。然后，他们为该公司选择较老的文章数量，并表示不能超过三篇。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|813042|如果您的游戏表有一个名为id的自动递增列，您可以选择每个类别的前10个游戏，如下所示：|offset|length|style|CODE|813043|SELECT+++*
FROM+++++games+g1
WHERE++++status+=+'game_published'++
+++++++++AND+featured+=+'1'+
+++++++++AND+10+>+
+++++++++(
+++++++++SELECT+++COUNT(*)
+++++++++FROM+++++games+g2
+++++++++WHERE++++g2.status+=+'game_published'++
++++++++++++++++++AND+g2.featured+=+'1'+
++++++++++++++++++AND+g1.category+=+g2.category
++++++++++++++++++AND+g2.id+>+g1.id
+++++++++)|code-block|syntax|javascript|813044|where条件规定，具有相同类别和更高ID的行不能超过10行。|813045|entityMap^0|0|C|2|0|0|0|5|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@$D|S|E|T|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|U|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|V|8|@$D|W|E|X|F|G]]|9|@]|A|$]]|$1|O|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|P|$]]

To use the technique from the posts you mention, you need a way to order the games. They're using article date. Then they select the number of older articles for that company, and say there can't be more than three.

If your games table has an auto-increment column called <code>id</code>, you can select the top 10 games per category like:

<pre><code>SELECT *
FROM games g1
WHERE status = 'game_published' 
 AND featured = '1' 
 AND 10 &gt; 
 (
 SELECT COUNT(*)
 FROM games g2
 WHERE g2.status = 'game_published' 
 AND g2.featured = '1' 
 AND g1.category = g2.category
 AND g2.id &gt; g1.id
 )
</code></pre>

The <code>where</code> condition says that there can't be more than 10 rows with the same category and a higher ID.

blocks|key|2504322|text|可能有一个更好的解决方案，但您可以只为每个类别执行一个查询。首先获取类别列表：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2504323|SELECT+DISTINCT(category)+FROM++`games`;|code-block|syntax|javascript|2504324|然后获取每个结果并查询12行：|2504325|SELECT+*+FROM+games+WHERE+status+=+'game_published'++
AND+`featured`+=++'1'+AND+`category`+=+$category+LIMIT+12;|2504326|当然，您需要添加某种排名行(并按其排序)才能获得top+12。|offset|length|style|BOLD|2504327|注意:可能有一种方法可以用一个查询就能做到这一点，但我现在想不起来。|2504328|entityMap^0|0|0|0|0|O|3|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|Y|8|@$M|Z|N|10|O|P]]|9|@]|A|$]]|$1|Q|3|R|5|6|7|11|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

There may be a more elegant solution, but you can just execute a query for each category. First get a list of categories:

<pre><code>SELECT DISTINCT(category) FROM `games`;
</code></pre>

Then take each of the results and query for 12 rows:

<pre><code>SELECT * FROM games WHERE status = 'game_published' 
AND `featured` = '1' AND `category` = $category LIMIT 12;
</code></pre>

Of course you need to add some kind of ranking row (and order by it) to get the top 12.

Note: There may be a way to do this with a single query, but it escapes me at the moment.

blocks|key|66691|text|这个怎么样？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|66692|SELECT+*+FROM+(
++++SELECT
+++++++games.*,
+++++++@rn+:=+CASE+WHEN+@category=category+THEN+@rn+%2B+1+ELSE+1+END+AS+rn,
+++++++@category+:=+category
++++FROM+games,+(SELECT+@rn+:=+0,+@category+:=+NULL)+AS+vars
++++WHERE+status+=+'game_published'+AND+featured+=+'1'
++++ORDER+BY+category
)+AS+T1
WHERE+rn+<=+12|code-block|syntax|javascript|66693|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

How about this?

<pre><code>SELECT * FROM (
 SELECT
 games.*,
 @rn := CASE WHEN @category=category THEN @rn + 1 ELSE 1 END AS rn,
 @category := category
 FROM games, (SELECT @rn := 0, @category := NULL) AS vars
 WHERE status = 'game_published' AND featured = '1'
 ORDER BY category
) AS T1
WHERE rn &lt;= 12
</code></pre>

blocks|key|814023|text|你可以使用UNION，如果我们谈论的不是百万类型的话...|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|814024|pseudoSQL：|814025|(SELECT+*+FROM+table+WHERE+condition+AND+category+=+'action'+ORDER+BY+id+LIMIT+10)
UNION
(SELECT+*+FROM+table+WHERE+condition+AND+category+=+'action'+ORDER+BY+id+LIMIT+10)
UNION
(SELECT+*+FROM+table+WHERE+condition+AND+category+=+'action'+ORDER+BY+id+LIMIT+10)|code-block|syntax|javascript|814026|如果您的PHP/ASP中有类别数组，则可以动态生成此联合。|814027|更多信息：http://dev.mysql.com/doc/refman/5.0/en/union.html|814028|编辑:这里可能是最有用的资源：http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/|814029|好好使用它%5E%5E|814030|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/union.html|1|http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/^0|5|5|0|0|0|0|5|1D|0|0|F|2I|1|0|0^^$0|@$1|2|3|4|5|6|7|14|8|@$9|15|A|16|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|17|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|18|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|19|8|@]|D|@]|E|$]]|$1|O|3|P|5|6|7|1A|8|@]|D|@$9|1B|A|1C|1|1D]]|E|$]]|$1|Q|3|R|5|6|7|1E|8|@]|D|@$9|1F|A|1G|1|1H]]|E|$]]|$1|S|3|T|5|6|7|1I|8|@]|D|@]|E|$]]|$1|U|3|-4|5|6|7|1J|8|@]|D|@]|E|$]]]|V|$W|$5|X|Y|Z|E|$10|11]]|12|$5|X|Y|Z|E|$10|13]]]]

you could use <code>UNION</code>, if we are not talking about million of types...

pseudoSQL:

<pre><code>(SELECT * FROM table WHERE condition AND category = 'action' ORDER BY id LIMIT 10)
UNION
(SELECT * FROM table WHERE condition AND category = 'action' ORDER BY id LIMIT 10)
UNION
(SELECT * FROM table WHERE condition AND category = 'action' ORDER BY id LIMIT 10)
</code></pre>

If you have array of categories in your PHP/ASP, you can generate this union on the fly.

More:
<a href="http://dev.mysql.com/doc/refman/5.0/en/union.html" rel="nofollow noreferrer">http://dev.mysql.com/doc/refman/5.0/en/union.html</a>

EDIT:
Here's probably most useful resource: <a href="http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/" rel="nofollow noreferrer">http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/</a>

Use it well ^^

I have a MYSQL5 database and PHP 5. I need a query for a games websites index page that only selects the first 12 from each category of games. Here is what I have so far.

<pre><code>$db-&gt;query("SELECT * FROM `games` WHERE status = 'game_published' AND `featured` = '1' ORDER BY `category`");
</code></pre>

The php code then groups games of the same category together and displays them. But yeah it doesn't limit the number of games from each category like I want.

Here is exactly what the structure of the table looks like: i49.tinypic.com/aysoll.png

Here is a blog post which sounds like what I am trying to do: <a href="http://www.e-nformation.net/content/view/title/MySQL+Top+N+in+each+group+(group+inner+limit)" rel="noreferrer">http://www.e-nformation.net/content/view/title/MySQL+Top+N+in+each+group+(group+inner+limit)</a> But I can't make sense of it. 

Any help is appreciated.

Advanced SQL query. Top 12 from each category (MYSQL)

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有一个MYSQL5数据库和PHP5。我需要一个游戏网站索引页，只选择从每个类别的前12个游戏查询。这是我到目前为止所拥有的。$db->query("SELECT * FROM  `games` WHERE status = 'game_published'  AND `featured` =  '1' ORDER B...

问高级SQL查询。每个类别的前12名(MYSQL)
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问高级SQL查询。每个类别的前12名(MYSQL)EN