1.创建表(movie,category,m_category)
create table movie ( id int primary key auto_increment, name varchar(20), movie_info varchar(100)); insert into movie (name,movie_info) values ("肖申克的救赎","希望让人自由"), ("霸王别姬","风华绝代"), ("阿甘正传","一部美国近现代史"), ("机器人总动员","机器人小瓦力,大人生"), ("这个杀手不太冷","怪蜀黍和小萝莉不得不说的故事"), ("美丽人生","最美的谎言"), ("阿凡达","绝对意义上的美轮美奂"), ("盗梦空间","诺兰给了我们一场无法盗取的梦"), ("楚门的世界","如果再也不能见到你,祝你早安,午安,晚安"), ("星际穿越","爱是一种力量,让我们超越时空感知它的存在"); create table category ( category_id int primary key auto_increment, category_name varchar(20), category_time date); insert into category (category_name,category_time) values ("犯罪","2020-05-05"), ("爱情","2020-05-06"), ("科幻","2020-05-10"); create table m_category ( id int primary key auto_increment, category_id int, m_time date); insert into m_category (category_id,m_time) values (1,"2020-05-01"), (2,"2020-05-02"), (2,"2020-05-03"), (3,"2020-05-04"), (1,"2020-05-05"), (2,"2020-05-06"), (3,"2020-05-07"), (3,"2020-05-08"), (3,"2020-05-09"), (3,"2020-05-10");
2.题目:
--查找“电影表”中电影描述信息包含“机器人”的电影,
--以及对应的电影类别名称和电影数目(count(电影表.电影编号))。
--同时,还需要该电影类别名称对应电影数量(count(电影类别表.电影类别编号))>=5部。
注意:而题目中的上述分类对应电影数量>=5部,是指该电影类别在原始表中的电影数量>= 5,
而不是先用where子句筛选以后的表。
select movie.name,category.category_name,count(movie.id) as 电影数目 from movie inner join m_category on movie.id = m_category.id inner join category on m_category.category_id = category.category_id right join (select category_id from m_category group by category_id having count(category_id) >= 5) as cc on m_category.category_id = cc.category_id where movie.movie_info like '%机器人%' group by category.category_name;
其他建议(https://blog.csdn.net/yangzhongblog/article/details/107551671)