requete2.sql

来自「a small application in Mysql and Delphi」· SQL 代码 · 共 29 行

CREATE  PROCEDURE  requete2()
BEGIN
CREATE VIEW  nbre_emprunt ( code_personne,nbre_emrp)  AS
SELECT  p.code_personne,count(*)  
 FROM emprunt e,personne p
 WHERE e.code_personne=p.code_personne
 GROUP BY code_personne;
CREATE VIEW duree (code_personne,duree_minimale, duree_moyenne,duree_maximale)AS
SELECT  e.code_personne,IF( date_restitution IS NOT NULL, 
 MIN(DATEDIFF(date_restitution,date_emprunt) ), 
MIN( DATEDIFF(current_date ,date_emprunt )
              )) AS duree_minimale,
IF( date_restitution IS NOT NULL, 
 AVG(DATEDIFF(date_restitution,date_emprunt) ), 
AVG( DATEDIFF(current_date ,date_emprunt )
              )) AS duree_moyenne,			  
IF( date_restitution IS NOT NULL, 			  
MAX(DATEDIFF(date_restitution,date_emprunt) ), 
MAX( DATEDIFF(current_date ,date_emprunt )
              )) AS duree_maximale		
FROM emprunt e,personne p
WHERE e.code_personne=p.code_personne
GROUP BY code_personne;
SELECT  * FROM  nbre_emprunt NATURAL JOIN duree ;
drop view nbre_emprunt;
drop view duree ;
END/