1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100
| --------------
START TRANSACTION
--------------
--------------
DROP DATABASE IF EXISTS `base`
--------------
--------------
CREATE DATABASE IF NOT EXISTS `base`
DEFAULT CHARACTER SET `latin1`
DEFAULT COLLATE `latin1_general_ci`
--------------
--------------
DROP TABLE IF EXISTS `test`
--------------
--------------
CREATE TABLE `test`
( `id` integer unsigned NOT NULL auto_increment primary key,
`date` date NOT NULL,
`time` time NOT NULL,
`HCHC` bigint unsigned NOT NULL,
`HCHP` bigint unsigned NOT NULL
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
insert into `test` (`date`,`time`,`HCHC`,`HCHP`) values
('2019-01-01', '00:00:10', 25, 30),
('2019-01-01', '01:00:15', 50, 60),
('2019-01-01', '03:00:20', 75, 90),
('2019-01-01', '05:00:25', 100, 120),
('2019-01-01', '07:00:30', 125, 150),
('2019-01-01', '09:00:35', 150, 180),
('2019-01-01', '11:00:40', 175, 210),
('2019-01-02', '14:00:00', 12, 7),
('2019-01-02', '15:00:00', 24, 14),
('2019-01-02', '16:00:00', 36, 21),
('2019-01-02', '17:00:00', 48, 28),
('2019-01-02', '18:00:00', 60, 35),
('2019-01-02', '19:00:00', 72, 42),
('2019-01-02', '20:00:00', 84, 49)
--------------
--------------
select * from `test`
--------------
+----+------------+----------+------+------+
| id | date | time | HCHC | HCHP |
+----+------------+----------+------+------+
| 1 | 2019-01-01 | 00:00:10 | 25 | 30 |
| 2 | 2019-01-01 | 01:00:15 | 50 | 60 |
| 3 | 2019-01-01 | 03:00:20 | 75 | 90 |
| 4 | 2019-01-01 | 05:00:25 | 100 | 120 |
| 5 | 2019-01-01 | 07:00:30 | 125 | 150 |
| 6 | 2019-01-01 | 09:00:35 | 150 | 180 |
| 7 | 2019-01-01 | 11:00:40 | 175 | 210 |
| 8 | 2019-01-02 | 14:00:00 | 12 | 7 |
| 9 | 2019-01-02 | 15:00:00 | 24 | 14 |
| 10 | 2019-01-02 | 16:00:00 | 36 | 21 |
| 11 | 2019-01-02 | 17:00:00 | 48 | 28 |
| 12 | 2019-01-02 | 18:00:00 | 60 | 35 |
| 13 | 2019-01-02 | 19:00:00 | 72 | 42 |
| 14 | 2019-01-02 | 20:00:00 | 84 | 49 |
+----+------------+----------+------+------+
--------------
select *,
t1.HCHC - t3.HCHC as diff_HCHC,
t1.HCHP - t3.HCHP as diff_HCHP
from `test` as t1
inner join `test` as t3
on t3.`time` = ( select min(t4.`time`)
from `test` as t4
where t4.`date` = t1.`date`
)
where t1.`time` = ( select max(t2.`time`)
from `test` as t2
where t2.`date` = t1.`date`
)
--------------
+----+------------+----------+------+------+----+------------+----------+------+------+-----------+-----------+
| id | date | time | HCHC | HCHP | id | date | time | HCHC | HCHP | diff_HCHC | diff_HCHP |
+----+------------+----------+------+------+----+------------+----------+------+------+-----------+-----------+
| 7 | 2019-01-01 | 11:00:40 | 175 | 210 | 1 | 2019-01-01 | 00:00:10 | 25 | 30 | 150 | 180 |
| 14 | 2019-01-02 | 20:00:00 | 84 | 49 | 8 | 2019-01-02 | 14:00:00 | 12 | 7 | 72 | 42 |
+----+------------+----------+------+------+----+------------+----------+------+------+-----------+-----------+
--------------
COMMIT
--------------
Appuyez sur une touche pour continuer... |
Partager