首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >mysql中的having条件和if else条件

问mysql中的having条件和if else条件
EN

Stack Overflow用户
提问于 2020-03-23 20:17:19
回答 2查看 82关注 0票数 1

我想找出有55到65部电影的所有电影类别。返回这些类别的名称和每个类别的电影数量,按电影数量排序。如果没有介于55和65之间的类别,则返回最高可用计数。

代码语言:javascript
复制
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

代码语言:javascript
复制
+-------------+-------------+-----+
| 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表示例数据:

代码语言:javascript
复制
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表示例数据:

代码语言:javascript
复制
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)
EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2020-03-24 02:07:34

针对MySQL 8+的解决方案

代码语言:javascript
复制
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;
代码语言:javascript
复制
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转换为子查询。

票数 1
EN

Stack Overflow用户

发布于 2020-03-23 21:05:43

我没有尝试这个查询,但我认为它可能是这样的,它将帮助您推进您的查询:

代码语言:javascript
复制
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 < 65
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/60813409

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档