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
| --------------
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,
`ref` tinyint unsigned not null,
`val` decimal(8,2) not null
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
insert into `test` (`date`,`ref`,`val`) values
('2020-01-05', 1, 150.00),
('2020-01-05', 2, 25.00),
('2020-01-05', 3, 12.00),
('2020-01-05', 4, 17.00),
('2020-01-05', 5, 42.00),
('2020-01-08', 1, 27.00),
('2020-01-08', 2, 8.00),
('2020-01-08', 3, 8.00),
('2020-01-08', 4, 12.00)
--------------
--------------
select * from `test`
--------------
+----+------------+-----+--------+
| id | date | ref | val |
+----+------------+-----+--------+
| 1 | 2020-01-05 | 1 | 150.00 |
| 2 | 2020-01-05 | 2 | 25.00 |
| 3 | 2020-01-05 | 3 | 12.00 |
| 4 | 2020-01-05 | 4 | 17.00 |
| 5 | 2020-01-05 | 5 | 42.00 |
| 6 | 2020-01-08 | 1 | 27.00 |
| 7 | 2020-01-08 | 2 | 8.00 |
| 8 | 2020-01-08 | 3 | 8.00 |
| 9 | 2020-01-08 | 4 | 12.00 |
+----+------------+-----+--------+
--------------
select t1.`date`,
t1.val - t2.tot as calcul
from `test` as t1
left outer join ( select `date`,
sum(val) as tot
from `test`
where ref > 1
and ref < 4
group by `date`
) as t2
on t2.`date` = t1.date
where t1.ref = 1
--------------
+------------+--------+
| date | calcul |
+------------+--------+
| 2020-01-05 | 113.00 |
| 2020-01-08 | 11.00 |
+------------+--------+
--------------
COMMIT
--------------
Appuyez sur une touche pour continuer... |
