oracle 取出一年内月末,求教一个sql，取出所有半年内的每个月的第一条符合条件的记录...

沒有 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 t1

right 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 t1

right 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

具體根據自己字段調整