问题描述
在此查询中,我指定要退回(硬编码)的汽车型号.因此,下面的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
这篇关于MySQL-返回每个GROUP BY的X号的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!