MySQL-返回每个GROUP BY的X号 [英] MySQL - Returning X number of each GROUP BY

问题描述

在此查询中，我指定要退回(硬编码)的汽车型号.因此，下面的SQL为每个模型返回一个记录:

In this query I specify which models of cars I want to return (hard-coded). So the SQL below returns one record for each model:

SELECT
        *
    FROM
        main
    WHERE
        (
            (marka_name = 'SUBARU' AND model_name = 'IMPREZA' AND (kuzov = 'GC8' OR kuzov = 'GF8')) OR
            (marka_name = 'MAZDA' AND model_name = 'RX-7' AND kuzov = 'FD3S') OR
            (marka_name = 'MITSUBISHI' AND model_name = 'LANCER' AND (kuzov = 'CN9A' OR kuzov = 'CP9A')) OR
            (marka_name = 'NISSAN' AND model_name = 'SKYLINE' AND (kuzov = 'ER34' OR kuzov = 'BCNR33')) OR
            (marka_name = 'NISSAN' AND model_name = 'SILVIA' AND kuzov = 'S14') OR
            (marka_name = 'TOYOTA' AND model_name = 'CELICA' AND kuzov = 'ST205') OR
            (marka_name = 'TOYOTA' AND model_name = 'ARISTO' AND kuzov = 'JZS161') OR
            (marka_name = 'MITSUBISHI' AND model_name = 'DELICA' AND (kuzov = 'PE8W' OR kuzov = 'PD8W' OR kuzov = 'PF8W'))
        )
        AND
        (rate != 'RA' AND rate != 'RR' AND rate != 'A1' AND rate != 'A' AND rate != 'R' AND rate >= '3')
        AND
        (mileage >= 0 AND mileage <= 150000)
        AND
        (year >= 1990 AND year <= 1998)
    GROUP BY
        model_name
    ORDER BY
        mileage ASC,
        rate DESC

现在可以在不使用联合的情况下使此SQL返回多个由某个字段指定的模型.示例:

Now is it possible, without unions, to have this SQL return more than one model specified by some field. Example:

GROUP BY
    model_name
HAVING COUNT(model_name) = 2
ORDER BY
    mileage ASC,
    rate DESC

我知道拥有数量"是没有道理的，但是我需要一种方法来指定每个模型要返回多少辆车.

I know that HAVING COUNT doesn't make sense, but I need a way to specify how many cars per model to return.

http://sqlfiddle.com/#!2/421e4/1/0

推荐答案

尝试像下面那样为每个组生成行号，然后将行数限制为i m以下，以显示每组2个结果.

Try like below genrate row numbers for each group and than limit the row number below i m showing 2 results per group.

   Select
    `marka_name`, `model_name`, `kuzov`, `mileage`, `year`, `rate`from
    (

    SELECT
     @row_num := IF(@prev_value=main.Model_Name,@row_num+1,1) AS RowNumber,
                main.*,
    @prev_value := main.Model_Name
            FROM
                main,
      (SELECT @row_num := 1) x,
          (SELECT @prev_value := '') y
            WHERE
                (
                    (marka_name = 'SUBARU' AND model_name = 'IMPREZA' AND (kuzov = 'GC8' OR kuzov = 'GF8')) OR
                    (marka_name = 'MAZDA' AND model_name = 'RX-7' AND kuzov = 'FD3S') OR
                    (marka_name = 'MITSUBISHI' AND model_name = 'LANCER' AND (kuzov = 'CN9A' OR kuzov = 'CP9A')) OR
                    (marka_name = 'NISSAN' AND model_name = 'SKYLINE' AND (kuzov = 'ER34' OR kuzov = 'BCNR33')) OR
                    (marka_name = 'NISSAN' AND model_name = 'SILVIA' AND kuzov = 'S14') OR
                    (marka_name = 'TOYOTA' AND model_name = 'CELICA' AND kuzov = 'ST205') OR
                    (marka_name = 'TOYOTA' AND model_name = 'ARISTO' AND kuzov = 'JZS161') OR
                    (marka_name = 'MITSUBISHI' AND model_name = 'DELICA' AND (kuzov = 'PE8W' OR kuzov = 'PD8W' OR kuzov = 'PF8W'))
                )
                AND
                (rate != 'RA' AND rate != 'RR' AND rate != 'A1' AND rate != 'A' AND rate != 'R' AND rate >= '3')
                AND
                (mileage >= 0 AND mileage <= 150000)
                AND
                (year >= 1990 AND year <= 1998)
            ORDER BY
                model_name,
                mileage ASC,
                rate DESC) A where A.RowNumber<=2

要获得每组2条以上的记录，只需更改Clause的最后一个位置(如果您每组要获得10条结果).写在A.RowNumber< = 10

To get more than 2 records per group just chnage the last where Clause like if you ant 10 results per group than. write where A.RowNumber<=10

Sql Fiddle演示

这篇关于MySQL-返回每个GROUP BY的X号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！