我想找出有55到65部电影的所有电影类别。返回这些类别的名称和每个类别的电影数量,按电影数量排序。如果没有介于55和65之间的类别,则返回最高可用计数。
SELECT
c.name,
fc.category_id,
COUNT(fc.category_id) as ccc
FROM
film_category fc
JOIN category as c on
c.category_id = fc.category_id
GROUP BY
fc.category_id
HAVING CASE
WHEN ccc BETWEEN 55 AND 65 THEN ccc
WHEN ccc NOT BETWEEN 55 AND 65 THEN ccc LIMIT 3 //or we can get only the largest one
--ELSE ccc NOT BETWEEN 55 AND 65 // or we can do it with else?
END
ORDER BY
ccc DESC;那么,如何以最有效的方式做到这一点呢?
当然,我的查询结果并不完全正确,因为我得到的所有行都不在55到65之间
这是我的结果,没有限制3
+-------------+-------------+-----+
| name | category_id | ccc |
+-------------+-------------+-----+
| Sports | 15 | 74 |
| Foreign | 9 | 73 |
| Family | 8 | 69 |
| Documentary | 6 | 68 |
| Animation | 2 | 66 |
| Action | 1 | 64 |
| New | 13 | 63 |
| Drama | 7 | 62 |
| Sci-Fi | 14 | 61 |
| Games | 10 | 61 |
| Children | 3 | 60 |
| Comedy | 5 | 58 |
| Classics | 4 | 57 |
| Travel | 16 | 57 |
| Horror | 11 | 56 |
| Music | 12 | 51 |
+-------------+-------------+-----+
16 rows in set (0.01 sec)category表示例数据:
mysql> select * from category;
+-------------+-------------+---------------------+
| category_id | name | last_update |
+-------------+-------------+---------------------+
| 1 | Action | 2006-02-15 04:46:27 |
| 2 | Animation | 2006-02-15 04:46:27 |
| 3 | Children | 2006-02-15 04:46:27 |
| 4 | Classics | 2006-02-15 04:46:27 |
| 5 | Comedy | 2006-02-15 04:46:27 |
| 6 | Documentary | 2006-02-15 04:46:27 |
| 7 | Drama | 2006-02-15 04:46:27 |
| 8 | Family | 2006-02-15 04:46:27 |
| 9 | Foreign | 2006-02-15 04:46:27 |
| 10 | Games | 2006-02-15 04:46:27 |
| 11 | Horror | 2006-02-15 04:46:27 |
| 12 | Music | 2006-02-15 04:46:27 |
| 13 | New | 2006-02-15 04:46:27 |
| 14 | Sci-Fi | 2006-02-15 04:46:27 |
| 15 | Sports | 2006-02-15 04:46:27 |
| 16 | Travel | 2006-02-15 04:46:27 |
+-------------+-------------+---------------------+
16 rows in set (0.00 sec)film_category表示例数据:
mysql> select * from film_category limit 10;
+---------+-------------+---------------------+
| film_id | category_id | last_update |
+---------+-------------+---------------------+
| 1 | 6 | 2006-02-15 05:07:09 |
| 2 | 11 | 2006-02-15 05:07:09 |
| 3 | 6 | 2006-02-15 05:07:09 |
| 4 | 11 | 2006-02-15 05:07:09 |
| 5 | 8 | 2006-02-15 05:07:09 |
| 6 | 9 | 2006-02-15 05:07:09 |
| 7 | 5 | 2006-02-15 05:07:09 |
| 8 | 11 | 2006-02-15 05:07:09 |
| 9 | 11 | 2006-02-15 05:07:09 |
| 10 | 15 | 2006-02-15 05:07:09 |
+---------+-------------+---------------------+
10 rows in set (0.01 sec)发布于 2020-03-24 02:07:34
针对MySQL 8+的解决方案
WITH
cte1 AS ( SELECT c.name,
fc.category_id,
COUNT(fc.category_id) as cnt
FROM film_category fc
JOIN category c ON c.category_id = fc.category_id
GROUP BY fc.category_id ),
cte2 AS ( SELECT name,
category_id,
cnt
FROM cte1
WHERE cnt BETWEEN @from AND @to
ORDER BY cnt DESC )
SELECT name,
category_id,
cnt
FROM cte2
UNION ALL
( SELECT name,
category_id,
cnt
FROM cte1
WHERE ( SELECT COUNT(*)
FROM cte2 ) = 0
ORDER BY cnt DESC LIMIT 3 )
ORDER BY cnt DESC;mysql> SET @from := 60;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @to := 65;
Query OK, 0 rows affected (0.00 sec)
mysql> /* query skipped */
+----------+-------------+-----+
| name | category_id | cnt |
+----------+-------------+-----+
| Action | 1 | 64 |
| New | 13 | 63 |
| Drama | 7 | 62 |
| Games | 10 | 61 |
| Sci-Fi | 14 | 61 |
| Children | 3 | 60 |
+----------+-------------+-----+
6 rows in set (0.00 sec)
mysql> SET @from := 35;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @to := 45;
Query OK, 0 rows affected (0.00 sec)
mysql> /* query skipped */
+---------+-------------+-----+
| name | category_id | cnt |
+---------+-------------+-----+
| Sports | 15 | 74 |
| Foreign | 9 | 73 |
| Family | 8 | 69 |
+---------+-------------+-----+
3 rows in set (0.00 sec)当然,在5+版本中,您可以将CTE转换为子查询。
发布于 2020-03-23 21:05:43
我没有尝试这个查询,但我认为它可能是这样的,它将帮助您推进您的查询:
SELECT
c.name,
fc.category_id,
COUNT(fc.category_id) as ccc
FROM
film_category fc
INNER JOIN category as c on
c.category_id = fc.category_id
GROUP BY
fc.category_id
HAVING ccc > 55 AND ccc < 65https://stackoverflow.com/questions/60813409
复制相似问题