包括使用限制的所有天数? [英] Include all the days when using limit?

问题描述

我想获取从 8 月 1 日到 8 月 31 日的所有行，但限制为 1500 行，但是，它必须包括从 1 到 31 的所有可能的天数.所以从每天开始，我预计大约有 48 行.

I want want to get all rows from 1st August to 31 August but limit to 1500 rows, however, it must include all the possible days from 1 to 31. So from each day I expected around 48 rows.

SQL 查询怎么可能?

How is that possible with SQL Query?

我已经尝试过这个 SQL 查询，但它不会包括所有的日子，因为我使用的是 limit.

I have tried this SQL query but it wont include all the days because I am using limit.

SELECT * FROM table1 
      WHERE `submit` >= '2014-08-01' AND  `submit` <=  '2014-08-31' 
order by `submit`
      LIMIT 1500

更新:

它应该得到从 1 到 31 的所有可能的天数，如果任何天没有足够的行 - 然后包括任何天的剩余行.例如，如果 8 月 7 日没有记录，则任何其他日期都应包含更多行.

It should get all the possible days from 1 to 31, if any days does not have enough rows - then include the remaining rows from any days. If there is no record on the 7th Aug for example, then any other days should include more rows.

更新 2:

假设 submit >='2014-08-01' AND submit <='2014-08-31' 有 3000 行，但我只想要 1000 行(如果可能)来自01 到 31.

Let say submit >= '2014-08-01' AND submit <= '2014-08-31' there are 3000 rows but I only want 1000 rows (if possible) from 01 to 31.

每天应该有大约 32 行.

Each day should have approx 32 rows.

例如

8 月 1 日有 32 行

Aug 01 have 32 rows

8 月 2 日有 32 行

Aug 02 have 32 rows

8 月 3 日只有 0 行

Aug 03 only 0 rows

8 月 4 日只有 0 行

Aug 04 only 0 rows

8 月 5 日有 32 行

Aug 05 have 32 rows

到

8 月 31 日有 32 行

Aug 31 have 32 rows

推荐答案

步骤 1. 数据表...

Step 1. The data table...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table 
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,dt DATE NOT NULL
);

INSERT INTO my_table (dt) VALUES
('2014-07-28'),
('2014-07-29'),
('2014-08-01'),
('2014-08-01'),
('2014-08-02'),
('2014-08-03'),
('2014-08-05'),
('2014-08-05'),
('2014-08-05'),
('2014-08-05'),
('2014-08-07'),
('2014-08-07'),
('2014-08-09'),
('2014-08-10'),
('2014-08-10'),
('2014-08-10'),
('2014-08-11'),
('2014-08-13'),
('2014-08-13'),
('2014-08-13'),
('2014-08-13'),
('2014-08-13'),
('2014-08-13'),
('2014-08-14'),
('2014-08-14'),
('2014-08-15'),
('2014-08-17'),
('2014-08-17'),
('2014-08-18'),
('2014-08-18'),
('2014-08-18'),
('2014-08-19'),
('2014-08-19'),
('2014-08-21'),
('2014-08-21'),
('2014-08-21'),
('2014-08-21'),
('2014-08-22'),
('2014-08-23'),
('2014-08-25'),
('2014-08-25'),
('2014-08-26'),
('2014-08-26'),
('2014-08-26'),
('2014-08-27'),
('2014-08-28'),
('2014-08-29'),
('2014-08-29'),
('2014-08-29'),
('2014-08-29'),
('2014-08-29'),
('2014-08-30'),
('2014-08-31'),
('2014-08-31');

SELECT * FROM my_table;
+----+------------+
| id | dt         |
+----+------------+
|  1 | 2014-07-28 |
|  2 | 2014-07-29 |
|  3 | 2014-08-01 |
|  4 | 2014-08-01 |
|  5 | 2014-08-02 |
|  6 | 2014-08-03 |
|  7 | 2014-08-05 |
|  8 | 2014-08-05 |
|  9 | 2014-08-05 |
| 10 | 2014-08-05 |
| 11 | 2014-08-07 |
| 12 | 2014-08-07 |
| 13 | 2014-08-09 |
| 14 | 2014-08-10 |
| 15 | 2014-08-10 |
| 16 | 2014-08-10 |
...
...
| 44 | 2014-08-26 |
| 45 | 2014-08-27 |
| 46 | 2014-08-28 |
| 47 | 2014-08-29 |
| 48 | 2014-08-29 |
| 49 | 2014-08-29 |
| 50 | 2014-08-29 |
| 51 | 2014-08-29 |
| 52 | 2014-08-30 |
| 53 | 2014-08-31 |
| 54 | 2014-08-31 |
+----+------------+

第 2 步.一个包含所有可能日期的日历实用程序表......(还有其他方法可以做到这一点，但 - 不寻常 - 这个问题需要在这里完成，在 MySQL 中，而不是在后处理中)

Step 2. A calendar utility table holding all conceivable dates... (there are other ways of doing this, but - unusually - this problem requires that this is done here, in MySQL, rather than in post-processing)

SELECT *
  FROM calendar
 WHERE dt BETWEEN '2014-07-28' AND '2014-09-02';
+------------+
| dt         |
+------------+
| 2014-07-28 |
| 2014-07-29 |
| 2014-07-30 |
| 2014-07-31 |
| 2014-08-01 |
| 2014-08-02 |
| 2014-08-03 |
| 2014-08-04 |
| 2014-08-05 |
| 2014-08-06 |
| 2014-08-07 |
| 2014-08-08 |
| 2014-08-09 |
| 2014-08-10 |
| 2014-08-11 |
| 2014-08-12 |
| 2014-08-13 |
| 2014-08-14 |
| 2014-08-15 |
| 2014-08-16 |
| 2014-08-17 |
| 2014-08-18 |
| 2014-08-19 |
| 2014-08-20 |
| 2014-08-21 |
| 2014-08-22 |
| 2014-08-23 |
| 2014-08-24 |
| 2014-08-25 |
| 2014-08-26 |
| 2014-08-27 |
| 2014-08-28 |
| 2014-08-29 |
| 2014-08-30 |
| 2014-08-31 |
| 2014-09-01 |
| 2014-09-02 |
+------------+
37 rows in set

步骤 3. 查询(保证每天至少有一个结果，总体不超过 50 个结果)

Step 3. The query (guaranteeing at least one result per day and no more than 50 results overall)

SELECT dt FROM 
(
SELECT a.dt
     , COALESCE(rank,0)
  FROM calendar a
  LEFT
  JOIN 
     ( SELECT x.dt,COUNT(*) rank
         FROM my_table x 
         JOIN my_table y 
           ON y.dt = x.dt 
          AND y.id <= x.id
        GROUP   
           BY x.id
     ) b
    ON b.dt = a.dt
 WHERE a.dt BETWEEN '2014-08-01' AND '2014-08-31'
 ORDER 
    BY rank,dt
 LIMIT 50
 )n
 ORDER BY dt; 
 +------------+
 | dt         |
 +------------+
 | 2014-08-01 |
 | 2014-08-01 |
 | 2014-08-02 |
 | 2014-08-03 |
 | 2014-08-04 |
 | 2014-08-05 |
 | 2014-08-05 |
 | 2014-08-05 |
 | 2014-08-06 |
 | 2014-08-07 |
 | 2014-08-07 |
 | 2014-08-08 |
 | 2014-08-09 |
 | 2014-08-10 |
 | 2014-08-10 |
 | 2014-08-10 |
 | 2014-08-11 |
 | 2014-08-12 |
 | 2014-08-13 |
 | 2014-08-13 |
 | 2014-08-13 |
 | 2014-08-14 |
 | 2014-08-14 |
 | 2014-08-15 |
 | 2014-08-16 |
 | 2014-08-17 |
 | 2014-08-17 |
 | 2014-08-18 |
 | 2014-08-18 |
 | 2014-08-18 |
 | 2014-08-19 |
 | 2014-08-19 |
 | 2014-08-20 |
 | 2014-08-21 |
 | 2014-08-21 |
 | 2014-08-21 |
 | 2014-08-22 |
 | 2014-08-23 |
 | 2014-08-24 |
 | 2014-08-25 |
 | 2014-08-25 |
 | 2014-08-26 |
 | 2014-08-26 |
 | 2014-08-27 |
 | 2014-08-28 |
 | 2014-08-29 |
 | 2014-08-29 |
 | 2014-08-30 |
 | 2014-08-31 |
 | 2014-08-31 |
 +------------+
 50 rows in set (0.00 sec)

这篇关于包括使用限制的所有天数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！