MySql 查询:从表中为每个类别选择前 3 行
我有一个包含记录的表,其中有一行名为category
。我插入了太多文章,我只想从每个类别中选择两篇文章。
我尝试做这样的事情:
我创建了一个视图:
CREATE VIEW limitrows AS
SELECT * FROM tbl_artikujt ORDER BY articleid DESC LIMIT 2
然后我创建了这个查询:
SELECT *
FROM tbl_artikujt
WHERE
artikullid IN
(
SELECT artikullid
FROM limitrows
ORDER BY category DESC
)
ORDER BY category DESC;
但这不起作用并且只给了我两条记录?
I have a table with records and it has a row called category
. I have inserted too many articles and I want to select only two articles from each category.
I tried to do something like this:
I created a view:
CREATE VIEW limitrows AS
SELECT * FROM tbl_artikujt ORDER BY articleid DESC LIMIT 2
Then I created this query:
SELECT *
FROM tbl_artikujt
WHERE
artikullid IN
(
SELECT artikullid
FROM limitrows
ORDER BY category DESC
)
ORDER BY category DESC;
But this is not working and is giving me only two records?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
LIMIT 仅停止语句返回的结果数。您正在寻找的通常称为分析/窗口/排名函数 - MySQL 不支持这些函数,但您可以使用变量进行模拟:
如果您不更改 SELECT x.*,结果集将包含
rank
和var_category
值 - 如果不是这种情况,您必须指定您真正想要的列。LIMIT only stops the number of results the statement returns. What you're looking for is generally called analytic/windowing/ranking functions - which MySQL doesn't support but you can emulate using variables:
If you don't change
SELECT x.*
, the result set will include therank
andvar_category
values - you'll have to specify the columns you really want if this isn't the case.使用分组依据而不是排序依据。
Use group by instead of order by.