MySQL の派生テーブル

SELECT COLUMN_LIST
FROM (SELECT COLUMN_LIST FROM TABLE_NAME) AS ALIAS_NAME;

SELECT f.title, f.length, r.mean_for_rating
FROM film AS f
INNER JOIN (
  SELECT rating, AVG(length) AS mean_for_rating
  FROM film
  GROUP BY rating
) AS r ON r.rating = f.rating
WHERE f.length > r.mean_for_rating
ORDER BY f.rating, f.title
LIMIT 5;

+------------------+--------+-----------------+
| title            | length | mean_for_rating |
+------------------+--------+-----------------+
| AFFAIR PREJUDICE |    117 |        111.0506 |
| AFRICAN EGG      |    130 |        111.0506 |
| ALAMO VIDEOTAPE  |    126 |        111.0506 |
| ATLANTIS CAUSE   |    170 |        111.0506 |
| BAKED CLEOPATRA  |    182 |        111.0506 |
+------------------+--------+-----------------+
5 rows in set (0.00 sec)

SELECT rating, films
FROM (SELECT rating, COUNT(*) AS films FROM film GROUP BY rating)
WHERE films > 200;

ERROR 1248 (42000): Every derived table must have its own alias