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 108 109 110 111 112 113 114
| --------------
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 `test`
(
`id` integer unsigned NOT NULL AUTO_INCREMENT primary key,
`id_perso` integer unsigned NOT NULL,
`date` date NOT NULL,
`time_depart` time NOT NULL,
`time_retour` time NOT NULL,
`ville` varchar(255) NOT NULL,
index `idx` (`time_depart`,`time_retour`)
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
INSERT INTO `test` (`id_perso`,`date`,`time_depart`,`time_retour`,`ville`) VALUES
( 1, '2016-05-01', '00:00:00', '00:00:00', 'bou azer'),
( 4, '2016-05-02', '04:04:00', '24:00:00', 'casablanca'),
( 4, '2016-05-03', '00:00:00', '24:00:00', 'casablanca'),
( 4, '2016-05-04', '00:00:00', '20:00:00', 'casablanca'),
( 5, '2016-05-05', '00:00:00', '20:11:00', 'bou azer'),
( 6, '2016-05-06', '00:00:00', '24:00:00', 'bou azer'),
( 7, '2016-05-07', '00:59:00', '24:00:00', 'bou azer'),
( 7, '2016-05-08', '00:00:00', '23:59:00', 'bou azer'),
( 8, '2016-05-10', '00:00:00', '24:00:00', 'casablanca'),
( 8, '2016-05-11', '00:00:00', '23:59:00', 'casablanca'),
( 9, '2016-05-12', '00:00:00', '24:00:00', 'bou azer'),
( 9, '2016-05-13', '00:00:00', '24:00:00', 'bou azer'),
(10, '2016-05-29', '11:00:00', '17:59:00', 'bou azer'),
(11, '2016-05-15', '00:00:00', '21:30:00', 'marrakech'),
(12, '2016-05-28', '00:00:00', '14:00:00', 'biougra')
--------------
--------------
select * from test
--------------
+----+----------+------------+-------------+-------------+------------+
| id | id_perso | date | time_depart | time_retour | ville |
+----+----------+------------+-------------+-------------+------------+
| 1 | 1 | 2016-05-01 | 00:00:00 | 00:00:00 | bou azer |
| 2 | 4 | 2016-05-02 | 04:04:00 | 24:00:00 | casablanca |
| 3 | 4 | 2016-05-03 | 00:00:00 | 24:00:00 | casablanca |
| 4 | 4 | 2016-05-04 | 00:00:00 | 20:00:00 | casablanca |
| 5 | 5 | 2016-05-05 | 00:00:00 | 20:11:00 | bou azer |
| 6 | 6 | 2016-05-06 | 00:00:00 | 24:00:00 | bou azer |
| 7 | 7 | 2016-05-07 | 00:59:00 | 24:00:00 | bou azer |
| 8 | 7 | 2016-05-08 | 00:00:00 | 23:59:00 | bou azer |
| 9 | 8 | 2016-05-10 | 00:00:00 | 24:00:00 | casablanca |
| 10 | 8 | 2016-05-11 | 00:00:00 | 23:59:00 | casablanca |
| 11 | 9 | 2016-05-12 | 00:00:00 | 24:00:00 | bou azer |
| 12 | 9 | 2016-05-13 | 00:00:00 | 24:00:00 | bou azer |
| 13 | 10 | 2016-05-29 | 11:00:00 | 17:59:00 | bou azer |
| 14 | 11 | 2016-05-15 | 00:00:00 | 21:30:00 | marrakech |
| 15 | 12 | 2016-05-28 | 00:00:00 | 14:00:00 | biougra |
+----+----------+------------+-------------+-------------+------------+
--------------
select id_perso, sum(taux) as taux
from (
select id_perso, count(*) as taux from test where time_depart <= '00:00:00' and time_retour >= '05:00:00' group by id_perso
union all
select id_perso, count(*) as taux from test where time_depart <= '11:00:00' and time_retour >= '14:00:00' group by id_perso
union all
select id_perso, count(*) as taux from test where time_depart <= '18:00:00' and time_retour >= '21:00:00' group by id_perso
) as x
group by id_perso
--------------
+----------+------+
| id_perso | taux |
+----------+------+
| 4 | 7 |
| 5 | 2 |
| 6 | 3 |
| 7 | 5 |
| 8 | 6 |
| 9 | 6 |
| 10 | 1 |
| 11 | 3 |
| 12 | 2 |
+----------+------+
--------------
COMMIT
--------------
--------------
SET AUTOCOMMIT = 1
--------------
Appuyez sur une touche pour continuer... |
Partager