计算列中不同字符的所有出现次数 [英] Count all occurances of different characters in a column

问题描述

假设我有两列:

Table: MyTable

myString 
----------
 abaa
 abb
 a
 bc     

我正在寻找一个查询，该查询将返回 myString 列中每个字符的计数.在这种情况下，它将返回:

I'm looking for a query that would return the count of each char within the myString column. In this case, it would return :

letter     count
-----------------
a            5
b            3
c            1

我发现计算字符的唯一方法是通过复杂的删除方法(计算没有字符的长度，删除字符，然后重新计算)，所以我想知道是否有办法完成这个更复杂的任务.

The only way I found to count chars was by a convoluted deletion method (count the length without the char, delete the char, and then recount), so I'm wondering if there would be a way to accomplish this more complicated task.

推荐答案

这会很慢(就像 SQL 中的任何解决方案一样)，但您可以使用 numbers table 拆分字母并计算它们:

This will be slow (as will likely any solution in SQL), but you can use a numbers table to split apart the letters and count them:

SELECT
  SUBSTRING(myString, number, 1) AS Letter,
  COUNT(*) AS LetterCount
FROM
  numbers n
  JOIN myTable mt
    ON n.number <= CHAR_LENGTH(mt.myString)
GROUP BY SUBSTRING(myString, number, 1)

工作 SQL Fiddle

这篇关于计算列中不同字符的所有出现次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！