mysql - 限制每个月的行数？

Question

我需要获取 2012 年十二月到 2014 年十一月之间的数据。

每个月我只需要 1500 行。

例如:

SELECT * FROM data WHERE YEAR(submit_date) = 2012 AND MONTH(submit_date) = 12 limit 1500;
SELECT * FROM data WHERE YEAR(submit_date) = 2013 AND MONTH(submit_date) = 1 limit 1500;
SELECT * FROM data WHERE YEAR(submit_date) = 2013 AND MONTH(submit_date) = 2 limit 1500;
SELECT * FROM data WHERE YEAR(submit_date) = 2013 AND MONTH(submit_date) = 3 limit 1500;
and until Nov 2014

有没有办法把 SQL 查询写得更小？

Answer 1

这里有一些选项列表:http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

恕我直言，最好的方法之一是使用行计数器:

set @num := 0, @type := '';

select id, name, submit_date,
  @num := if(@type = CONCAT(YEAR(submit_date), MONTH(submit_date)), @num + 1, 1) as row_number,
  @type := CONCAT(YEAR(submit_date), MONTH(submit_date)) as dummy
from data force index(IX_submit_date)
group by id, name, submit_date
having row_number <= 2;

您可以在这里进行测试:http://sqlfiddle.com/#!2/e829c/13 (我切割了 2 个元素，而不是 1500 个元素)