Mysql usage tips: view-like subquery

The search description information includes the category name and the number of movies corresponding to robot movies, and the number of movies corresponding to the category is also required >= 5

Recommendation: "mysql video tutorial 》

The film table is a movie table, the category table is a movie classification table, and the film_category table is an intermediate table between the movie table and the movie classification table (a many-to-many intermediate table)

film Table

##

CREATE TABLE IF NOT EXISTS film (
film_id smallint(5)  NOT NULL DEFAULT '0',
title varchar(255) NOT NULL,
description text,
PRIMARY KEY (film_id));

                                                                                                     

category table

CREATE TABLE category  (
category_id  tinyint(3)  NOT NULL ,
name  varchar(25) NOT NULL, `last_update` timestamp,
PRIMARY KEY ( category_id ));

film_category table

CREATE TABLE film_category  (
film_id  smallint(5)  NOT NULL,
category_id  tinyint(3)  NOT NULL, `last_update` timestamp);

SELECT c.name, COUNT(f.film_id) AS amount
FROM film AS f, film_category AS fc, category AS c,
(SELECT category_id FROM film_category GROUP BY category_id HAVING COUNT(category_id) >= 5) AS cc
WHERE f.description LIKE '%robot%'
AND f.film_id = fc.film_id
AND fc.category_id = c.category_id
AND c.category_id = cc.category_id

The above is the detailed content of Mysql usage tips: view-like subquery. For more information, please follow other related articles on the PHP Chinese website!