MySql查询:从表中为每个类别选择前3行 [英] MySql Query: Select top 3 rows from table for each category
问题描述
我有一个带有记录的表,它有一个名为category
的行.我插入了太多文章,并且我只希望从每个类别中选择两篇文章.
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.
我试图做这样的事情:
我创建了一个视图:
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;
但这不起作用,只给我两个记录吗?
But this is not working and is giving me only two records?
推荐答案
LIMIT仅停止语句返回的结果数.您正在寻找的通常称为分析/窗口/排序功能-MySQL不支持该功能,但您可以使用变量进行仿真:
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:
SELECT x.*
FROM (SELECT t.*,
CASE
WHEN @category != t.category THEN @rownum := 1
ELSE @rownum := @rownum + 1
END AS rank,
@category := t.category AS var_category
FROM TBL_ARTIKUJT t
JOIN (SELECT @rownum := NULL, @category := '') r
ORDER BY t.category) x
WHERE x.rank <= 3
如果不更改SELECT x.*
,结果集将包含rank
和var_category
值-如果不是这种情况,则必须指定您真正想要的列.
If you don't change SELECT x.*
, the result set will include the rank
and var_category
values - you'll have to specify the columns you really want if this isn't the case.
这篇关于MySql查询:从表中为每个类别选择前3行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!