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
| --------------
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 `conteneurs`
--------------
--------------
CREATE TABLE `conteneurs`
( `id` integer unsigned NOT NULL auto_increment primary key,
`nom` varchar(255) NOT NULL
) ENGINE=MyIsam
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
INSERT INTO `conteneurs` (`nom`) VALUES ('Conteneur 1'),('Conteneur 2'),('Conteneur 3'),('Conteneur 4'),('Conteneur 5')
--------------
--------------
select * from `conteneurs`
--------------
+----+-------------+
| id | nom |
+----+-------------+
| 1 | Conteneur 1 |
| 2 | Conteneur 2 |
| 3 | Conteneur 3 |
| 4 | Conteneur 4 |
| 5 | Conteneur 5 |
+----+-------------+
--------------
DROP TABLE IF EXISTS `documents`
--------------
--------------
CREATE TABLE `documents`
( `id` integer unsigned NOT NULL auto_increment primary key,
`nom` varchar(255) NOT NULL,
`conteneur` integer unsigned NOT NULL,
`destruction` smallint NOT NULL,
CONSTRAINT `FK_CONTENEURS` FOREIGN KEY (`conteneur`) REFERENCES `conteneurs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=MyIsam
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
INSERT INTO `documents` (`nom`, `conteneur`, `destruction`) VALUES
('document 1', 1, 2018),('document 2', 1, 2020),('document 3', 2, 2015),('document 4', 2, 2016),('document 5', 3, 2017),
('document 6', 3, 2018),('document 7', 4, 2017),('document 8', 4, 2015),('document 9', 5, 2014),('document 10', 5, 2020)
--------------
--------------
select * from `documents`
--------------
+----+-------------+-----------+-------------+
| id | nom | conteneur | destruction |
+----+-------------+-----------+-------------+
| 1 | document 1 | 1 | 2018 |
| 2 | document 2 | 1 | 2020 |
| 3 | document 3 | 2 | 2015 |
| 4 | document 4 | 2 | 2016 |
| 5 | document 5 | 3 | 2017 |
| 6 | document 6 | 3 | 2018 |
| 7 | document 7 | 4 | 2017 |
| 8 | document 8 | 4 | 2015 |
| 9 | document 9 | 5 | 2014 |
| 10 | document 10 | 5 | 2020 |
+----+-------------+-----------+-------------+
--------------
SELECT a.conteneur
FROM documents AS a
INNER JOIN conteneurs AS b
ON b.id = a.conteneur
GROUP BY a.conteneur
HAVING MAX(a.destruction) <= 2017
--------------
+-----------+
| conteneur |
+-----------+
| 2 |
| 4 |
+-----------+
--------------
COMMIT
--------------
--------------
SET AUTOCOMMIT = 1
--------------
Appuyez sur une touche pour continuer... |
Partager