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
| --------------
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 `artiste`
--------------
--------------
CREATE TABLE `artiste`
( `id` integer unsigned NOT NULL auto_increment primary key,
`dat_maj` datetime NOT NULL,
`nom_art` varchar(255) NOT NULL,
`prenom_art` varchar(255) NOT NULL
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
insert into `artiste` (`dat_maj`,`nom_art`,`prenom_art`) values
('2018-11-01 11:09:50', 'Alpha', 'Alain'),
('2018-11-02 16:12:36', 'Bravo', 'Beatrice'),
('2018-11-03 16:12:36', 'Charlie', 'Cherif')
--------------
--------------
select * from `artiste`
--------------
+----+---------------------+---------+------------+
| id | dat_maj | nom_art | prenom_art |
+----+---------------------+---------+------------+
| 1 | 2018-11-01 11:09:50 | Alpha | Alain |
| 2 | 2018-11-02 16:12:36 | Bravo | Beatrice |
| 3 | 2018-11-03 16:12:36 | Charlie | Cherif |
+----+---------------------+---------+------------+
--------------
DROP TABLE IF EXISTS `adresse`
--------------
--------------
CREATE TABLE `adresse`
( `id` integer unsigned NOT NULL auto_increment primary key,
`dat_maj` datetime NOT NULL,
`id_artiste` integer unsigned NOT NULL,
`adresse` varchar(255) NOT NULL
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
insert into `adresse` (`dat_maj`,`id_artiste`,`adresse`) values
('2018-12-01 17:17:12', 1, '1 rue Alphonse'),
('2018-12-02 11:12:31', 2, '2 rue Bichat'),
('2018-12-03 17:15:02', 3, '3 rue Carpeaux'),
('2018-12-04 17:14:50', 3, '4 rue Daviel'),
('2018-12-05 13:37:55', 3, '5 rue Estienne')
--------------
--------------
select * from `adresse`
--------------
+----+---------------------+------------+----------------+
| id | dat_maj | id_artiste | adresse |
+----+---------------------+------------+----------------+
| 1 | 2018-12-01 17:17:12 | 1 | 1 rue Alphonse |
| 2 | 2018-12-02 11:12:31 | 2 | 2 rue Bichat |
| 3 | 2018-12-03 17:15:02 | 3 | 3 rue Carpeaux |
| 4 | 2018-12-04 17:14:50 | 3 | 4 rue Daviel |
| 5 | 2018-12-05 13:37:55 | 3 | 5 rue Estienne |
+----+---------------------+------------+----------------+
--------------
select t1.nom_art,
t1.prenom_art,
t2.adresse
from `artiste` as t1
inner join `adresse` as t2
on t2.id_artiste = t1.id
and t2.dat_maj = ( select max(dat_maj)
from `adresse` as t3
where t3.id_artiste = t1.id
)
order by t1.nom_art, t1.prenom_art
--------------
+---------+------------+----------------+
| nom_art | prenom_art | adresse |
+---------+------------+----------------+
| Alpha | Alain | 1 rue Alphonse |
| Bravo | Beatrice | 2 rue Bichat |
| Charlie | Cherif | 5 rue Estienne |
+---------+------------+----------------+
--------------
COMMIT
--------------
Appuyez sur une touche pour continuer... |
Partager