MYSQL: Join con sub-consultas con COUNT y AVG - https://es.stackoverflow.com/q/131550/29967, MySQL

Language: Layout:

/*
        MYSQL: Join con sub-consultas con COUNT y AVG - https://es.stackoverflow.com/q/131550/29967

/*
    -- ATENCIÓN NO USE DROP TABLE CON SUS TABLAS REALES
    -- YA QUE DROP TABLE BORRARÁ SUS DATOS
    -- DROP TABLE SE USA AQUÍ SÓLO PARA PODER PROBAR LOS DATOS 
    */ 
    SET FOREIGN_KEY_CHECKS=0; 
    DROP TABLE IF EXISTS level_20180116;
    DROP TABLE IF EXISTS state_20180116;
    DROP TABLE IF EXISTS video_20180116;
    DROP TABLE IF EXISTS lessons_20180116;
    DROP TABLE IF EXISTS coments_20180116;
    SET FOREIGN_KEY_CHECKS=1;

/* NO COPIE LA SENTENCIA ^ DROP TABLE ^ EN SUS DATOS REALES */

CREATE TABLE `level_20180116` (
      `ident_level` smallint(3) unsigned not null auto_increment primary key,
      `level` varchar(15) not null
    )engine=InnoDB default charset=utf8 collate=utf8_spanish_ci;

CREATE TABLE `state_20180116` (
      `ident_state` smallint(3) unsigned not null auto_increment primary key,
      `state` varchar(15) not null
    )engine=InnoDB default charset=utf8 collate=utf8_spanish_ci;

CREATE TABLE `video_20180116` (
      `ident_video` int(11) unsigned not null auto_increment primary key,  
      `title` varchar(255) not null,
      `icon` varchar(100) not null,        
      `ident_level` smallint(3) unsigned not null,
      `ident_state` smallint(3) unsigned not null,   
      foreign key(`ident_level`) references `level_20180116`(`ident_level`)
      on delete cascade on update cascade,
      foreign key(`ident_state`) references `state_20180116`(`ident_state`)
      on delete cascade on update cascade  
    )engine=InnoDB default charset=utf8 collate=utf8_spanish_ci;

CREATE TABLE `lessons_20180116` (
      `ident_lesson` smallint(11) unsigned not null auto_increment primary key,
      `title` varchar(100) not null,
      -- `status` smallint(1) not null,  
      `ident_video` int(11) unsigned not null,   
       foreign key (`ident_video`) references `video_20180116`(`ident_video`)
       on delete cascade on update cascade
    )engine=InnoDB default charset=utf8 collate=utf8_spanish_ci;

CREATE TABLE coments_20180116(
      `ident_coment` smallint(11) unsigned not null auto_increment primary key,
      `title` varchar(255) not null,
      -- `coment` text not null,
       `vote` float(3) not null,         
      `ident_video` int(11) unsigned not null,
      -- `ident_user` int(11) unsigned not null,      
      -- `status` smallint(1) unsigned not null,
       foreign key (`ident_video`) references `video_20180116`(`ident_video`)
       on delete cascade on update cascade -- ,
       -- foreign key(`ident_user`) references `user`(`ident_user`)
       -- on delete cascade on update cascade
    )engine=InnoDB default charset=utf8 collate=utf8_spanish_ci;

INSERT INTO level_20180116 (level) VALUES ('L1'),('L2');

INSERT INTO state_20180116 (state) VALUES ('st1'),('st2');

INSERT INTO video_20180116 (title,ident_level,ident_state,icon) VALUES 
    ('v1',1,1,'ic1'),
    ('v2',1,1,'ic2'),
    ('v3',2,2,'ic3')
    ;

INSERT INTO lessons_20180116 (title,ident_video) VALUES 
    ('les1',1),
    ('les2',1),
    ('les3',2)
    ;

INSERT INTO coments_20180116 (title,ident_video, vote) VALUES 
    ('com1',1, 8.5),
    ('com2',1, 9.2),
    ('com3',2, 7.6)
    ;

SELECT v.title, v.icon, lv.level, ls.total_lessons, c.avg_vote, c.votos
    FROM video_20180116 v  
     JOIN
        (
             SELECT ident_video, COUNT(*) total_lessons
                FROM lessons_20180116 GROUP BY ident_video
        )    ls ON (v.ident_video = ls.ident_video)

JOIN
        (   
            SELECT AVG(vote) avg_vote, ident_video, COUNT(*) votos
            FROM coments_20180116 GROUP BY ident_video
        )   c ON (v.ident_video = c.ident_video)

LEFT JOIN level_20180116 lv on v.ident_level = lv.ident_level
    WHERE v.ident_state=1 ORDER BY v.ident_video DESC LIMIT 5;


absolute service time: 0,49 sec

MYSQL: Join con sub-consultas con COUNT y AVG - https://es.stackoverfl...