如何计算mysql中逗号分隔字符串中子字符串的出现次数

kqqjbcuj 于 2021-08-09 发布在 Java

关注(0)|答案(1)|浏览(420)

假设我选择了这样的记录

+----+-------------------------------------------------------------------------------------
| id | groupedStatus                                                                      |
+----+-------------------------------------------------------------------------------------
| 1 | received,accepted,discarded,discarded,accepted,discarded,accepted,received,received |
| 2 | accepted,discarded,received,received,received,received,received                     |          
+----+-------------------------------------------------------------------------------------

除了上面的记录，我还想得到groupedstatus字符串中每个子字符串的出现情况，
例如，在第一行中，引用如下：
接收：3接受：3丢弃：3
原始表架构
表1

ID int

表2：

ID
Table1ID
Status enum('received','accepted','discarded')

我使用下面的查询选择上面的记录

select t1.id, group_concat(t2.status) as groupedStatus from Table1 t1 inner join Table2 t2 on t1.id=t2.table1ID group by t1.id

sql mysql group-by pivot String

来源：https://stackoverflow.com/questions/62277528/how-to-count-occurrence-of-a-substring-in-a-comma-separated-string-in-mysql

1条答案

按热度按时间

tez616oj1#

您可以通过条件聚合来实现这一点，如下所示：

select 
    t1.id, 
    sum(t2.status = 'received')  as received,
    sum(t2.status = 'accepted')  as accepted,
    sum(t2.status = 'discarded') as discarded,
    group_concat(t2.status) as groupedStatus 
from table1 t1 
inner join table2 t2 on t1.id = t2.table1ID 
group by t1.id

赞(0）回复(0）举报 2021-08-09

我来回答

如何计算mysql中逗号分隔字符串中子字符串的出现次数

1条答案

相关问题

热门标签

最新问答