Version 4 (modified by 3 years ago) ( diff ) | ,
---|
Напредни извештаи од базата (SQL и складирани процедури)
Прикажување на најдискутираните филмови за секоја година
select year,title, (num_discussions + num_replies) as total from ( select godini.year,m.title , count(d1.discussion_id) as num_discussions, count(r.reply_id) as num_replies from movies m left join discussions d1 on d1.movie_id = m.movie_id left join replies r on r.discussion_id = d1.discussion_id join( select distinct extract(year from d.date) as year from discussions d ) as godini on godini.year = extract(year from d1.date) or extract(year from r.date) = godini.year group by m.title, godini.year ) as sum_by_year where (num_discussions + num_replies) = ( select max(num_discussions + num_replies) from ( select godini.year1,m.title as title1 , count(d1.discussion_id) as num_discussions, count(r.reply_id) as num_replies from movies m left join discussions d1 on d1.movie_id = m.movie_id left join replies r on r.discussion_id = d1.discussion_id join( select distinct extract(year from d.date) as year1 from discussions d ) as godini on godini.year1 = extract(year from d1.date) or extract(year from r.date) = godini.year1 group by m.title, godini.year1 ) as diskusii_repliki where diskusii_repliki.year1 = year group by diskusii_repliki.year1 );
Предложување на филмови на корисник врз база на корисниците со кои стапил во интеракција
select distinct m.title from replies r join discussions d on r.discussion_id = d.discussion_id join replies r2 on r2.discussion_id = d.discussion_id and r2.user_id != 1 join users u on u.user_id = r2.user_id join movie_likes ml on ml.user_id = u.user_id join movies m on m.movie_id = ml.movie_id where r.user_id = 1;
- при реализација на прашалникот, наместо 1, ќе биде искористено соодветниот идентификатор на најавениот корисник
Прикажување на бројот на филмови за секој жанр во секоја година, сортирани според бројот на филмови.
select g.genre_type, extract(year from m.airing_date), count(m.movie_id) as movie_count from genres as g join movie_genres as mg on mg.genre_id = g.genre_id join movies as m on m.movie_id = mg.movie_id group by g.genre_type, extract(year from m.airing_date) order by movie_count desc
Прикажување на бројот на дискусии и реплики за сите корисници кои се во базата, сортирани според бројот на реплики и дискусии.
select u.user_id, u.name, u.surname, count(d.discussion_id) as discussion_count, count (r.reply_id) as reply_count from users u left join discussions as d on d.user_id = u.user_id left join replies as r on r.user_id = u.user_id group by u.user_id, u.name, u.surname order by count(d.discussion_id) + count (r.reply_id) desc
Note:
See TracWiki
for help on using the wiki.