按列值按LIKE分组 [英] Group by LIKE of a column value

问题描述

给出这样的假设查询:

SELECT COUNT(*)
FROM subscriptions
GROUP by plan_type

还有一个与以下表格相似的表格:

And a table similar to the one below:

+----+-----------------------+-------------+--+
| id |       plan_type       | customer_id |  |
+----+-----------------------+-------------+--+
|  1 | gold_2017             |         523 |  |
|  2 | gold_2016_recurring   |        2300 |  |
|  3 | silver_2016           |         234 |  |
|  4 | silver_2017_recurring |        2593 |  |
|  5 | platinum_recurring    |        4123 |  |
+----+-----------------------+-------------+--+

所需结果:

+-------+----------+
| count |   type   |
+-------+----------+
|     2 | gold     |
|     2 | silver   |
|     1 | platinum |
+-------+----------+

是否可以使用GROUP BY和LIKE语句(如LIKE"silver"，LIKE"gold"，LIKE"Platinum"等)对这些条目进行分组?

Is there any way to group these entries using a GROUP BY and a LIKE statement (LIKE "silver", LIKE "gold", LIKE "platinum", etc)?

推荐答案

您可以使用case:

SELECT (CASE WHEN plan_type LIKE 'silver%' THEN 'silver'
             WHEN plan_type LIKE 'gold%' THEN 'gold'
             WHEN plan_type LIKE 'platinum%' THEN 'platinum'
        END) as plan_grp, COUNT(*)
FROM subscriptions
GROUP by (CASE WHEN plan_type LIKE 'silver%' THEN 'silver'
               WHEN plan_type LIKE 'gold%' THEN 'gold'
               WHEN plan_type LIKE 'platinum%' THEN 'platinum'
          END);

某些数据库允许您在GROUP BY中使用列别名.

Some databases allow you to use a column alias in the GROUP BY.

这篇关于按列值按LIKE分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！