mysql - 求教一个sql，取出所有半年内的每个月的第一条符合条件的记录

问题描述

如题呢。有一张表记录了一些数据和数据的时间戳

问题解答

select * from tbl; tstamp| data---------------------+------ 2016-02-03 00:00:00 | 200 2016-02-04 00:00:00 | 300 2015-07-06 00:00:00 | 202 2016-09-01 00:00:00 | 300 2015-07-06 00:00:00 | 324 2015-07-15 00:00:00 | 299(6 行记录)

先把数据按月份划分窗口，再把每个月份窗口内部排序，最后选出序号为1的记录。

select tstamp, data from (select tstamp, data, row_number() over (partition by date_trunc(’month’, tstamp) order by tstamp) as row from tbl) as tbl_windowswhere row = 1; tstamp| data---------------------+------ 2015-07-06 00:00:00 | 202 2016-02-03 00:00:00 | 200 2016-09-01 00:00:00 | 300(3 行记录)

如果表有自增的id列，还可以用更简单的

select tstamp, data from tbl where id in (select min(id) from tbl group by date_trunc(’month’, tstamp));回答2：

沒有 mysql 環境, 在 sqlserver 給試了下

create table td(tstamp datetime,data int); insert into td (tstamp, data) values(’2016-02-03 00:00:00’, 200),(’2016-02-04 00:00:00’, 300),(’2015-07-06 00:00:00’, 202),(’2016-09-01 00:00:00’, 300),(’2015-07-06 00:00:00’, 324),(’2015-07-15 00:00:00’, 299),(’2016-03-05 00:00:00’, 499);select * from td as t1right join (select convert(varchar(7), tstamp, 126) as ts1, min(tstamp) as ts2 from td where tstamp between ’2016-01-01’ and ’2016-07-01’ group by convert(varchar(7), tstamp, 126)) as t2 on t1.tstamp=t2.ts2

tstamp data ts1 ts2 ------------------------------------------------------------------------2016-02-03 00:00:00.0 200 2016-02 2016-02-03 00:00:00.0 2016-03-05 00:00:00.0 499 2016-03 2016-03-05 00:00:00.0

下面是翻譯成 mysql 的語句(未測試)

select * from td as t1right join ( select date_format(tstamp, ’%y-%m’) as ts1, min(tstamp) as ts2 from td where tstamp between ’2016-01-01’ and ’2016-07-01’ group by date_format(tstamp, ’%y-%m’)) as t2 on t1.tstamp=t2.ts2

具體根據自己字段調整

select record from dataset where time.month in ('Jan','Feb',...'Nov') limit 1

大概是这样的感觉，然而我并不知道怎么从时间中提取出月份，自行解决吧。

select `data` as data,MONTH(FROM_UNIXTIME(`dateline`)) AS M from table where dateline<半年 group by M ;回答5：

用时间函数转换 可以的