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 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133
| --------------
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 `employe`
--------------
--------------
CREATE TABLE `employe`
( `id` integer unsigned NOT NULL PRIMARY KEY,
`nom` varchar(255) NOT NULL,
`adresse` varchar(255) NOT NULL,
`telephone` varchar(255) NOT NULL,
`courrier` varchar(255) NOT NULL
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
insert into `employe` (`id`,`nom`,`adresse`,`telephone`,`courrier`) values
(1212, 'Marie St-Jérome', '111 avenue Jean François, Montréal, QC', '514 4932876', 'marie-stherome@jegere.ca'),
(1876, 'Martin Rey', '345 Avenue Poirier, Montréal, QC', '514 9871245', 'martin.rey@jegere.ca'),
(2231, 'Jean-Pierre Bordeau', '2309 Boulevard Pie XII, Québec, QC', '418 6573298', 'jean-pierre-bordeau@jegere.ca'),
(4354, 'Louise Gagnon', '2101 Boulevard Bois Franc, Trois Rivières, QC', '819 6574028', 'louise-gagnon@jegere.ca')
--------------
--------------
select * from `employe`
--------------
+------+---------------------+-----------------------------------------------+-------------+-------------------------------+
| id | nom | adresse | telephone | courrier |
+------+---------------------+-----------------------------------------------+-------------+-------------------------------+
| 1212 | Marie St-Jérome | 111 avenue Jean François, Montréal, QC | 514 4932876 | marie-stherome@jegere.ca |
| 1876 | Martin Rey | 345 Avenue Poirier, Montréal, QC | 514 9871245 | martin.rey@jegere.ca |
| 2231 | Jean-Pierre Bordeau | 2309 Boulevard Pie XII, Québec, QC | 418 6573298 | jean-pierre-bordeau@jegere.ca |
| 4354 | Louise Gagnon | 2101 Boulevard Bois Franc, Trois Rivières, QC | 819 6574028 | louise-gagnon@jegere.ca |
+------+---------------------+-----------------------------------------------+-------------+-------------------------------+
--------------
DROP TABLE IF EXISTS `Ressource`
--------------
--------------
CREATE TABLE `Ressource`
( `projet` integer unsigned NOT NULL,
`employe` integer unsigned NOT NULL,
`heure` integer unsigned NOT NULL,
`prix` integer unsigned NOT NULL,
primary key (`projet`,`employe`)
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
insert into `Ressource` (`projet`,`employe`,`heure`,`prix`) values
(1, 1876, 500, 65),
(1, 4354, 2000, 31),
(2, 2231, 250, 55),
(3, 2231, 500, 65),
(3, 1212, 3000, 35),
(3, 1876, 2000, 35)
--------------
--------------
select * from `Ressource`
--------------
+--------+---------+-------+------+
| projet | employe | heure | prix |
+--------+---------+-------+------+
| 1 | 1876 | 500 | 65 |
| 1 | 4354 | 2000 | 31 |
| 2 | 2231 | 250 | 55 |
| 3 | 1212 | 3000 | 35 |
| 3 | 1876 | 2000 | 35 |
| 3 | 2231 | 500 | 65 |
+--------+---------+-------+------+
--------------
select r.employe,
e.nom,
e.adresse,
e.telephone,
e.courrier,
r.cumul,
r.total
from ( select employe,
sum(heure) as cumul,
sum(heure*prix) as total
from `ressource`
group by employe
) as r
inner join `employe` as e
on e.id = r.employe
order by r.employe
--------------
+---------+---------------------+-----------------------------------------------+-------------+-------------------------------+-------+--------+
| employe | nom | adresse | telephone | courrier | cumul | total |
+---------+---------------------+-----------------------------------------------+-------------+-------------------------------+-------+--------+
| 1212 | Marie St-Jérome | 111 avenue Jean François, Montréal, QC | 514 4932876 | marie-stherome@jegere.ca | 3000 | 105000 |
| 1876 | Martin Rey | 345 Avenue Poirier, Montréal, QC | 514 9871245 | martin.rey@jegere.ca | 2500 | 102500 |
| 2231 | Jean-Pierre Bordeau | 2309 Boulevard Pie XII, Québec, QC | 418 6573298 | jean-pierre-bordeau@jegere.ca | 750 | 46250 |
| 4354 | Louise Gagnon | 2101 Boulevard Bois Franc, Trois Rivières, QC | 819 6574028 | louise-gagnon@jegere.ca | 2000 | 62000 |
+---------+---------------------+-----------------------------------------------+-------------+-------------------------------+-------+--------+
--------------
COMMIT
--------------
--------------
SET AUTOCOMMIT = 1
--------------
Appuyez sur une touche pour continuer... |
Partager