SELECT I.* ,ROUND(C.mg,2) AS moyenne FROM inscription I LEFT JOIN tb_moy_general C ON C.id_elv = I.id_inscri WHERE C.promo = '{$promo}' AND C.perode ='{$trim}' AND C.classe = '{$classe}'
-- -------------------------------------------------------- -- Hôte : 127.0.0.1 -- Version du serveur: 10.1.16-MariaDB - mariadb.org binary distribution -- SE du serveur: Win32 -- HeidiSQL Version: 9.4.0.5125 -- -------------------------------------------------------- /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET NAMES utf8 */; /*!50503 SET NAMES utf8mb4 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; -- Export de la structure de la table msi. classe DROP TABLE IF EXISTS `classe`; CREATE TABLE IF NOT EXISTS `classe` ( `id` int(11) NOT NULL AUTO_INCREMENT, `nom` varchar(128) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; -- Export de données de la table msi.classe : ~2 rows (environ) DELETE FROM `classe`; /*!40000 ALTER TABLE `classe` DISABLE KEYS */; INSERT INTO `classe` (`id`, `nom`) VALUES (1, 'classe 1'), (2, 'classe 2'); /*!40000 ALTER TABLE `classe` ENABLE KEYS */; -- Export de la structure de la table msi. classe_eleve DROP TABLE IF EXISTS `classe_eleve`; CREATE TABLE IF NOT EXISTS `classe_eleve` ( `id` int(11) NOT NULL AUTO_INCREMENT, `id_classe` int(11) NOT NULL, `id_eleve` int(11) NOT NULL, `periode_scolaire_deb` date NOT NULL, `periode_scolaire_fin` date NOT NULL, PRIMARY KEY (`id`), KEY `id_classe` (`id_classe`), KEY `id_eleve` (`id_eleve`), KEY `annee_scolaire` (`periode_scolaire_deb`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; -- Export de données de la table msi.classe_eleve : ~2 rows (environ) DELETE FROM `classe_eleve`; /*!40000 ALTER TABLE `classe_eleve` DISABLE KEYS */; INSERT INTO `classe_eleve` (`id`, `id_classe`, `id_eleve`, `periode_scolaire_deb`, `periode_scolaire_fin`) VALUES (1, 1, 5, '2016-09-01', '2017-07-01'), (2, 2, 15, '2016-09-01', '2017-07-01'); /*!40000 ALTER TABLE `classe_eleve` ENABLE KEYS */; -- Export de la structure de la table msi. inscription DROP TABLE IF EXISTS `inscription`; CREATE TABLE IF NOT EXISTS `inscription` ( `id_inscri` int(11) NOT NULL AUTO_INCREMENT, `num` varchar(60) NOT NULL, `annee_scolaire` varchar(15) NOT NULL, `matricule` varchar(15) NOT NULL, `affecte` varchar(80) NOT NULL, `nom` varchar(200) NOT NULL, `prenom` varchar(220) NOT NULL, `date_naiss` varchar(30) NOT NULL, `sexe` varchar(5) NOT NULL, `nationalite` varchar(80) NOT NULL, `etabl_orgin` varchar(200) NOT NULL, `drenet` varchar(150) NOT NULL, `niv_precdt` varchar(30) NOT NULL, `decision_admissibilite` varchar(60) NOT NULL, `tel` varchar(30) NOT NULL, `fax` varchar(30) NOT NULL, `mail` varchar(150) NOT NULL, `site` varchar(150) NOT NULL, `pere` varchar(200) NOT NULL, `telP` varchar(15) NOT NULL, `mere` varchar(200) NOT NULL, `telM` varchar(15) NOT NULL, `prers_ct` varchar(200) NOT NULL, `contact` varchar(30) NOT NULL, `date` datetime NOT NULL, `session` varchar(80) NOT NULL, `color` int(5) NOT NULL, PRIMARY KEY (`id_inscri`) ) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1; -- Export de données de la table msi.inscription : ~2 rows (environ) DELETE FROM `inscription`; /*!40000 ALTER TABLE `inscription` DISABLE KEYS */; INSERT INTO `inscription` (`id_inscri`, `num`, `annee_scolaire`, `matricule`, `affecte`, `nom`, `prenom`, `date_naiss`, `sexe`, `nationalite`, `etabl_orgin`, `drenet`, `niv_precdt`, `decision_admissibilite`, `tel`, `fax`, `mail`, `site`, `pere`, `telP`, `mere`, `telM`, `prers_ct`, `contact`, `date`, `session`, `color`) VALUES (5, '16-000004', '2016-2017', '11083878F', '', 'KONE', 'CHEMISSOLO FATOUMATA', '24/12/1997', 'F', 'IVOIRIENNE', 'LMT', 'BOUNDIALI', '3ieme', 'Recale', '05886434', '36867042', 'groupeecolesdignon@gmail.com', 'http://www.groupecolesdignon.com/', 'KONE YAYA', '05886434', 'COULIBALY SALIMATA', '05886434', 'KONE YAYA', '05886434', '2016-09-22 12:40:33', 'kletio', 0), (15, '16-000014', '2016-2017', '10091531Z', 'prive', 'TRAORE', 'ZIE', '26/07/1995', 'H', 'IVOIRIENNE', 'LYCEE MODERNE TENGRELA', 'BOUNDIALI', '3ieme', 'Recale', '56941764', '36867042', 'groupeecolesdignon@gmail.com', 'http://www.groupecolesdignon.com/', 'TRAORE SINDIE', '06164645', '', '06164645', 'TRAORE DOH', '46215800', '2016-11-12 15:07:19', 'kletio', 0); /*!40000 ALTER TABLE `inscription` ENABLE KEYS */; -- Export de la structure de la table msi. matieres DROP TABLE IF EXISTS `matieres`; CREATE TABLE IF NOT EXISTS `matieres` ( `id` int(11) NOT NULL AUTO_INCREMENT, `libelle` varchar(128) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; -- Export de données de la table msi.matieres : ~2 rows (environ) DELETE FROM `matieres`; /*!40000 ALTER TABLE `matieres` DISABLE KEYS */; INSERT INTO `matieres` (`id`, `libelle`) VALUES (1, 'Français'), (2, 'Mathématiques'); /*!40000 ALTER TABLE `matieres` ENABLE KEYS */; -- Export de la structure de la table msi. notes DROP TABLE IF EXISTS `notes`; CREATE TABLE IF NOT EXISTS `notes` ( `id` int(11) NOT NULL AUTO_INCREMENT, `date` date DEFAULT NULL, `id_elv` int(11) DEFAULT NULL, `id_matiere` int(11) DEFAULT NULL, `note` float DEFAULT NULL, `exam` varchar(256) DEFAULT NULL, PRIMARY KEY (`id`), KEY `id_elv` (`id_elv`), KEY `id_matiere` (`id_matiere`), KEY `date` (`date`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; -- Export de données de la table msi.notes : ~4 rows (environ) DELETE FROM `notes`; /*!40000 ALTER TABLE `notes` DISABLE KEYS */; INSERT INTO `notes` (`id`, `date`, `id_elv`, `id_matiere`, `note`, `exam`) VALUES (1, '2017-05-03', 5, 1, 12.5, 'TD N°1'), (2, '2017-05-03', 15, 1, 15, 'TD N°1'), (3, '2017-06-03', 15, 2, 9, 'controle 1'), (4, '2017-06-07', 15, 2, 17, 'controle 2'); /*!40000 ALTER TABLE `notes` ENABLE KEYS */; /*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */; /*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
SELECT I.* ,C.* ,(SELECT AVG(N.note) as Moyenne FROM notes N WHERE N.date BETWEEN CE.periode_scolaire_deb AND CE.periode_scolaire_fin AND N.id_elv = I.id_inscri GROUP BY N.id_elv ) AS MOYENNE FROM inscription I LEFT JOIN classe_eleve CE ON CE.id_eleve = I.id_inscri LEFT JOIN classe C ON C.id = CE.id_classe WHERE C.id = 1
3 juil. 2017 à 20:37
`id_inscri` int(11) NOT NULL AUTO_INCREMENT,
`num` varchar(60) NOT NULL,
`annee_scolaire` varchar(15) NOT NULL,
`matricule` varchar(15) NOT NULL,
`affecte` varchar(80) NOT NULL,
`nom` varchar(200) NOT NULL,
`prenom` varchar(220) NOT NULL,
`date_naiss` varchar(30) NOT NULL,
`sexe` varchar(5) NOT NULL,
`nationalite` varchar(80) NOT NULL,
`etabl_orgin` varchar(200) NOT NULL,
`drenet` varchar(150) NOT NULL,
`niv_precdt` varchar(30) NOT NULL,
`decision_admissibilite` varchar(60) NOT NULL,
`tel` varchar(30) NOT NULL,
`fax` varchar(30) NOT NULL,
`mail` varchar(150) NOT NULL,
`site` varchar(150) NOT NULL,
`pere` varchar(200) NOT NULL,
`telP` varchar(15) NOT NULL,
`mere` varchar(200) NOT NULL,
`telM` varchar(15) NOT NULL,
`prers_ct` varchar(200) NOT NULL,
`contact` varchar(30) NOT NULL,
`date` datetime NOT NULL,
`session` varchar(80) NOT NULL,
`id_classe` int(11) NOT NULL,
`color` int(5) NOT NULL,
PRIMARY KEY (`id_inscri`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=19 ;