mysql - SQL : How to select multiple records per day, 假设每天包含超过 1 个值，MySQL

Question

所以我有一个巨大的表，其中包含每天的多条记录。我想每天获得 5 或 10 条记录。每行都有 created_at 字段，时间戳格式示例:2015-01-01 00:00:01。每行都有一个唯一的 ID 字段。

使用 MySQL 5.5.44

我找到了这个(SQL: How to select one record per day...):

select * from value_magnitudes
where id in 
(
    SELECT min(id)
    FROM value_magnitudes
    WHERE magnitude_id = 234
    and date(reading_date) >= '2013-04-01'
    group by date(reading_date)
)

它每天给我一条记录。我尝试了几次修改它以满足我的需要，但我想它不能用那个简单的查询来完成。

然后我发现了这个(How to select more than 1 record per day?):

SELECT date_time, other_column
FROM  (
   SELECT *, row_number() OVER (PARTITION BY date_time::date) AS rn
   FROM   tbl
   WHERE  date_time >= '2012-11-01 0:0'
   AND    date_time <  '2012-12-01 0:0'
   ) x
WHERE  rn < 4;

不能让这个工作(当然改变了字段的名称)。它给了我这个错误:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(PARTITION BY created_at::date) AS rn FROM tbl WHERE created_at >= '201' at line 3

所以你们能不能给我指出正确的方向，谢谢。

更新:期望的结果看起来像这样(同一日期只有两个，但你明白了):

id                                      company_id                          created_at
0001c73d-8f95-49b8-a380-275c3863974d    90f87a37-801f-4f05-b86d-b25574aa457d    2015-12-01 22:36:20
000074d3-0116-4d13-bea9-7256d522ea88    53171f70-7b65-4e17-a222-8d9952351b88    2015-12-01 14:09:19
00002a24-c14a-4584-8657-ad62a62d8bb2    ad83edbc-fe82-443f-96fb-8188b91fdd62    2015-12-02 09:15:22
000023b7-9a32-4ac9-9f8d-e18712c0a940    3713bc34-bffa-458d-98bc-266acfcacc6c    2015-12-02 14:57:24
0000d51e-dea7-446c-9974-4536501d77ec    23d1556c-0719-4260-9740-a8882badf2db    2015-12-03 13:19:22
0020e450-866f-4467-b5fe-892d1b9565a0    90f87a37-801f-4f05-b86d-b25574aa677d    2015-12-03 13:46:01
00001bc4-6d39-494c-8b4c-43050ebe537e    90f87a37-801f-4f05-b86d-b25574aa478d    2015-12-04 22:09:50
000035cf-409f-4cfb-adcd-412eb5def840    90f87a37-801f-4f05-b86d-b25574aa434d    2015-12-04 22:50:43

INSERT 就像:

INSERT INTO `invoices` (`id`, `company_id`, `created_at`)
VALUES
    ('0001c73d-8f95-49b8-a380-275c3863974d', '90f87a37-801f-4f05-b86d-b25574aa457d', '2015-12-01 22:36:20'),
    ('000074d3-0116-4d13-bea9-7256d522ea88', '53171f70-7b65-4e17-a222-8d9952351b88', '2015-12-01 14:09:19'),
    ('00002a24-c14a-4584-8657-ad62a62d8bb2', 'ad83edbc-fe82-443f-96fb-8188b91fdd62', '2015-12-02 09:15:22'),
    ('000023b7-9a32-4ac9-9f8d-e18712c0a940', '3713bc34-bffa-458d-98bc-266acfcacc6c', '2015-12-02 14:57:24'),
    ('0000d51e-dea7-446c-9974-4536501d77ec', '23d1556c-0719-4260-9740-a8882badf2db', '2015-12-03 13:19:22'),
    ('0020e450-866f-4467-b5fe-892d1b9565a0', '90f87a37-801f-4f05-b86d-b25574aa677d', '2015-12-03 13:46:01'),
    ('00001bc4-6d39-494c-8b4c-43050ebe537e', '90f87a37-801f-4f05-b86d-b25574aa478d', '2015-12-04 22:09:50'),
    ('000035cf-409f-4cfb-adcd-412eb5def840', '90f87a37-801f-4f05-b86d-b25574aa434d', '2015-12-04 22:50:43');

Answer 1

您可以使用变量来获取结果集行号，因为 mysql 不支持 ROWNUM 作为 oracle。

请试试这个:

select * 
from (
    select id,date(date_time) as date_time,other_columns,
    case when @date = date(date_time) then 
        @rn := @rn + 1 
    else 
        @rn := 1 
    end as x, -- row numbers for each date_time
    @date := date(date_time)
    from your_table
    inner join(select @date := '', @rn:=1) as tmp
    order by date(date_time) asc
) as tbl
where x <= 5 --5 records for each date_time

上面的查询将为您提供每天 5 条记录(如果有的话)。