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 101 102 103 104 105 106 107
| --------------
SET AUTOCOMMIT = 0
--------------
--------------
START TRANSACTION
--------------
--------------
DROP DATABASE IF EXISTS `base`
--------------
--------------
CREATE DATABASE `base`
DEFAULT CHARACTER SET `latin1`
DEFAULT COLLATE `latin1_general_ci`
--------------
--------------
DROP TABLE IF EXISTS `test`
--------------
--------------
CREATE TABLE IF NOT EXISTS `test`
( `no_inc` integer unsigned not null auto_increment primary key,
`employer_id` smallint unsigned not null,
`date` date not null,
`projet` integer unsigned not null,
`equipement` smallint unsigned null,
`heure` time not null
) engine=innoDB
default charset=latin1 collate=latin1_general_ci
row_format=compressed
--------------
--------------
insert into `test` (`employer_id`,`date`,`projet`,`equipement`,`heure`) values
(54, '2016-05-10', 2139, 003, '01:00:00'),
(54, '2016-05-24', 2139, 005, '00:30:00'),
(54, '2016-05-08', 2217, 001, '00:30:00'),
(54, '2016-05-10', 2139, null, '02:00:00'),
(54, '2016-05-11', 2139, null, '01:00:10'),
(54, '2016-05-09', 2139, 003, '01:11:11'),
(54, '2016-05-25', 100, null, '01:30:00'),
(54, '2016-05-26', 100, null, '01:45:00')
--------------
--------------
select * from `test`
--------------
+--------+-------------+------------+--------+------------+----------+
| no_inc | employer_id | date | projet | equipement | heure |
+--------+-------------+------------+--------+------------+----------+
| 1 | 54 | 2016-05-10 | 2139 | 3 | 01:00:00 |
| 2 | 54 | 2016-05-24 | 2139 | 5 | 00:30:00 |
| 3 | 54 | 2016-05-08 | 2217 | 1 | 00:30:00 |
| 4 | 54 | 2016-05-10 | 2139 | NULL | 02:00:00 |
| 5 | 54 | 2016-05-11 | 2139 | NULL | 01:00:10 |
| 6 | 54 | 2016-05-09 | 2139 | 3 | 01:11:11 |
| 7 | 54 | 2016-05-25 | 100 | NULL | 01:30:00 |
| 8 | 54 | 2016-05-26 | 100 | NULL | 01:45:00 |
+--------+-------------+------------+--------+------------+----------+
--------------
select semaine, projet, equipement,
max(lundi) as lundi,
max(mardi) as mardi,
max(mercredi) as mercredi,
max(jeudi) as jeudi,
max(vendredi) as vendredi,
max(samedi) as samedi,
max(dimanche) as dimanche
from (
select date_format(date, '%Y-%v') as semaine, projet, equipement,
case weekday(date) when 0 then cast(heure as char) else '' end as lundi,
case weekday(date) when 1 then cast(heure as char) else '' end as mardi,
case weekday(date) when 2 then cast(heure as char) else '' end as mercredi,
case weekday(date) when 3 then cast(heure as char) else '' end as jeudi,
case weekday(date) when 4 then cast(heure as char) else '' end as vendredi,
case weekday(date) when 5 then cast(heure as char) else '' end as samedi,
case weekday(date) when 6 then cast(heure as char) else '' end as dimanche
from test
) as x
group by semaine, projet, equipement
--------------
+---------+--------+------------+----------+----------+----------+----------+----------+--------+----------+
| semaine | projet | equipement | lundi | mardi | mercredi | jeudi | vendredi | samedi | dimanche |
+---------+--------+------------+----------+----------+----------+----------+----------+--------+----------+
| 2016-18 | 2217 | 1 | | | | | | | 00:30:00 |
| 2016-19 | 2139 | NULL | | 02:00:00 | 01:00:10 | | | | |
| 2016-19 | 2139 | 3 | 01:11:11 | 01:00:00 | | | | | |
| 2016-21 | 100 | NULL | | | 01:30:00 | 01:45:00 | | | |
| 2016-21 | 2139 | 5 | | 00:30:00 | | | | | |
+---------+--------+------------+----------+----------+----------+----------+----------+--------+----------+
--------------
COMMIT
--------------
--------------
SET AUTOCOMMIT = 1
--------------
Appuyez sur une touche pour continuer... |
Partager