每组最多n个，具有多个联接 [英] Greatest n-per-group With Multiple Joins

问题描述

晚上

我试图在MySQL中获取每组限制为 n 的行的输出.我可以在不使用联接的情况下正常工作，但是我很害羞.我在这里粘贴了相关表的转储:

I am trying to get an output of rows that are limited to n per group in MySQL. I can get it to work without joins, but with it I am just shy. I've pasted a dump of the relevant tables here:

http://pastebin.com/6F0v1jhZ

我正在使用的查询是:

SELECT
   title, catRef, RowNum, pCat, tog
FROM
(
    SELECT
        title, catRef,
        @num := IF(@prevCat=catRef,@num+1,1) AS RowNum,
        @prevCat AS tog,
        @prevCat := catRef AS pCat
    FROM (select @prevCat:=null) AS initvars
    CROSS JOIN 
    (
        SELECT p.title, oi.catRef
        FROM resources p
        INNER JOIN placesRel v ON (p.resId = v.refId)
        INNER JOIN catRel oi ON (p.resId = oi.refId)
        WHERE p.status = 'live' AND v.type = 'res' AND oi.type = 'res'
    ) AS T
) AS U
WHERE RowNum <= 5
ORDER BY catRef

我只是无法增加行数.否则将不胜感激.

I just can't get the row count to go up. Or any other solution would be greatly appreciated.

我正在寻找这样的结果:

I'm looking for a result like this:

title        catRef        RowNum
Title1       1             1
Title2       1             2
Title3       1             3
Title4       2             1
Title5       2             2
Title6       3             1

目前，RowNum列始终为1.

At the moment, the RowNum column is always 1.

推荐答案

这有效:

SET @num := 1, @prevCat := 0;
SELECT title, start, end, type, description, linkOut, outType, catRef, row_number
FROM (
SELECT title, start, end, type, description, linkOut, outType, catRef,
@num := if(@prevCat = catRef, @num + 1, 1) as row_number,
@prevCat AS tog,
@prevCat := catRef AS dummy
FROM (
    SELECT title, start, end, resources.type, description, linkOut, outType, catRef
    FROM resources LEFT JOIN placesRel ON placesRel.refId = resId LEFT JOIN catRel ON catRel.refId = resId
    WHERE status = 'live' AND placesRel.type = 'res' AND catRel.type = 'res'
    ORDER BY catRef
) AS w
) AS x WHERE x.row_number <= 4;

您需要将合并的查询放在子查询中，并按要分组的列对其进行排序.使用其父查询来添加行号.然后，顶级查询将它们粘合在一起.

You need to put your joined query in a sub-query and order it by the column you want to group by. Use it's parent query to add row numbers. Then, the top-level query glues it all together.

如果不将联接的查询放在自己的子查询中，则结果将不会按您希望的顺序排序，而是按照它们在数据库中的顺序显示.这意味着数据未分组，因此行号将不会应用于有序行.

If you don't put your joined query in it's own sub-query, the results won't be ordered as you wish, but instead will come out in the order they are in the database. This means the data is not grouped, so row numbers will no be applied to ordered rows.

这篇关于每组最多n个，具有多个联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！