mysql 字符的数量_mysql – 计算字符串中唯一字符的数量

没有直接或简单的方法.您可能需要编写存储函数来完成工作,并查看数据中可能包含的所有字符.以下是仅仅数字的示例,可以为存储函数中的所有字符进行扩展

mysql> select * from test ;

+------------+

| val |

+------------+

| 11111111 |

| 111222222 |

| 1113333222 |

+------------+

select

val,

sum(case when locate('1',val) > 0 then 1 else 0 end )

+ sum( case when locate('2',val) > 0 then 1 else 0 end)

+ sum(case when locate('3',val) > 0 then 1 else 0 end)

+sum(case when locate('4',val) > 0 then 1 else 0 end ) as occurence

from test group by val

+------------+-----------+

| val | occurence |

+------------+-----------+

| 11111111 | 1 |

| 111222222 | 2 |

| 1113333222 | 3 |

+------------+-----------+

或者,如果您有足够的时间,请创建一个包含您可以想到的所有字符的查找表.并在2行中进行查询

mysql> select * from test ;

+------------+

| val |

+------------+

| 11111111 |

| 111222222 |

| 1113333222 |

+------------+

3 rows in set (0.00 sec)

mysql> select * from look_up ;

+------+------+

| id | val |

+------+------+

| 1 | 1 |

| 2 | 2 |

| 3 | 3 |

| 4 | 4 |

+------+------+

4 rows in set (0.00 sec)

select

t1.val,

sum(case when locate(t2.val,t1.val) > 0 then 1 else 0 end ) as occ

from test t1,(select * from look_up)t2

group by t1.val ;

+------------+------+

| val | occ |

+------------+------+

| 11111111 | 1 |

| 111222222 | 2 |

| 1113333222 | 3 |

+------------+------+