MySQL:変相実装ソート関数
MySQL 8.0以前はSQL Serverソート関数が不足していましたが、相を変えてしか実現できませんでした.
関連投稿:https://bbs.csdn.net/topics/392500595?page=1#post-403630664
関連投稿:https://bbs.csdn.net/topics/392500595?page=1#post-403630664
drop table if exists t;
create table t(
id int not null AUTO_INCREMENT primary key,
wd DECIMAL(10,2),
addTime datetime
);
INSERT t (wd,addTime) values(10,'2018-12-27 03:00');
INSERT t (wd,addTime) values(10,'2018-12-27 03:01');
INSERT t (wd,addTime) values(11,'2018-12-27 03:10');
INSERT t (wd,addTime) values(12,'2018-12-27 03:12');
INSERT t (wd,addTime) values(13,'2018-12-27 03:13');
INSERT t (wd,addTime) values(14,'2018-12-27 03:20');
INSERT t (wd,addTime) values(10,'2018-12-27 04:00');
INSERT t (wd,addTime) values(10,'2018-12-27 05:01');
INSERT t (wd,addTime) values(11,'2018-12-27 06:10');
INSERT t (wd,addTime) values(12,'2018-12-27 07:12');
INSERT t (wd,addTime) values(13,'2018-12-27 08:13');
INSERT t (wd,addTime) values(23,'2018-12-27 09:20');
#
/*
1. 8 ,8 , 16 , ,
*/
select * from (
select
(
SELECT COUNT(*) FROM t t2 WHERE
1=1
AND DATE_FORMAT(t1.addTime,'%Y-%m-%d %H')= DATE_FORMAT(t2.addTime,'%Y-%m-%d %H')
AND t1.id >= t2.id
) as rid
,id
,wd
,addTime
from t as t1 where t1.addTime>DATE_ADD(t1.addTime,INTERVAL -8 hour)
) as tt
where rid in (1,6);
/*
+-----+----+-------+---------------------+
| rid | id | wd | addTime |
+-----+----+-------+---------------------+
| 1 | 1 | 10.00 | 2018-12-27 03:00:00 |
| 6 | 6 | 14.00 | 2018-12-27 03:20:00 |
| 1 | 7 | 10.00 | 2018-12-27 04:00:00 |
| 1 | 8 | 10.00 | 2018-12-27 05:01:00 |
| 1 | 9 | 11.00 | 2018-12-27 06:10:00 |
| 1 | 10 | 12.00 | 2018-12-27 07:12:00 |
| 1 | 11 | 13.00 | 2018-12-27 08:13:00 |
| 1 | 12 | 23.00 | 2018-12-27 09:20:00 |
+-----+----+-------+---------------------+
*/
/*
2. 8 , 23 , 23 , 23.1 , 24 , 、 、 ,
*/
drop TEMPORARY TABLE if exists tmp;
drop TEMPORARY TABLE if exists tmp2;
CREATE TEMPORARY TABLE tmp(
rid int,
id int,
wd DECIMAL(10,2),
addTime datetime,
timeSeg int
);
CREATE TEMPORARY TABLE tmp2(
rid int,
id int,
wd DECIMAL(10,2),
addTime datetime,
timeSeg int
);
insert into tmp
select * from (
select
(
SELECT COUNT(*) FROM t t2 WHERE
1=1
AND DATE_FORMAT(t1.addTime,'%Y-%m-%d %H')= DATE_FORMAT(t2.addTime,'%Y-%m-%d %H')
AND t1.id >= t2.id
) as rid
,id
,wd
,addTime
,DATE_FORMAT(t1.addTime,'%Y%m%d%H') as timeSeg
from t as t1 where t1.addTime>DATE_ADD(t1.addTime,INTERVAL -8 hour)
) as tt
;
insert into tmp2
select * from tmp;
select
id
,wd
,addTime
from (
select a.*,case when a.rid=1 or (b.wd is not null and a.wd!=b.wd) then 1 else 0 end as r from tmp as a
left join tmp2 as b on a.timeSeg=b.timeSeg and a.rid=b.rid+1
) as tt
where tt.r=1
order by tt.timeSeg,tt.rid
;
/*
+----+-------+---------------------+
| id | wd | addTime |
+----+-------+---------------------+
| 1 | 10.00 | 2018-12-27 03:00:00 |
| 3 | 11.00 | 2018-12-27 03:10:00 |
| 4 | 12.00 | 2018-12-27 03:12:00 |
| 5 | 13.00 | 2018-12-27 03:13:00 |
| 6 | 14.00 | 2018-12-27 03:20:00 |
| 7 | 10.00 | 2018-12-27 04:00:00 |
| 8 | 10.00 | 2018-12-27 05:01:00 |
| 9 | 11.00 | 2018-12-27 06:10:00 |
| 10 | 12.00 | 2018-12-27 07:12:00 |
| 11 | 13.00 | 2018-12-27 08:13:00 |
| 12 | 23.00 | 2018-12-27 09:20:00 |
+----+-------+---------------------+
*/