MySQL 计算在单元格中显示为列表的数据的出现次数

Question

大家好。假设我有一个这样组织的表:

|ColA         |
---------------
|AAA            |
|AAA#!#BBB      |
|BBB#!#CCC#!#DDD|
|AAA#!#DDD      |
|DDD            |

我想要实现的是计算每个字符串的出现次数，将符号“#!#”视为分隔符。最好的应该是有一个 MySQL Count() 结果样式:

|count|   |
|AAA  |3  |
|BBB  |2  |
|CCC  |1  |
|DDD  |3  |

是否可以在 SQL 语句中将 replace 与 count 结合使用？是否可以使用 replace 结果作为表格来应用 count？

Answer 1

像这样的东西应该可以解决问题:

架构

CREATE TABLE TableA
    (`id` int, `ColA` varchar(255));    
INSERT INTO TableA
    (`id`, `ColA`)
VALUES
    (1, 'AAA'),
    (2, 'AAA#!#BBB'),
    (3, 'BBB#!#CCC#!#DDD'),
    (4, 'AAA#!#DDD'),
    (5, 'DDD');


CREATE TABLE TableB
    (`id` int, `Name` varchar(255));    
INSERT INTO TableB
    (`id`, `Name`)
VALUES
    (1, 'AAA'),
    (2, 'BBB'),
    (3, 'CCC'),
    (4, 'DDD');

查询

SELECT col, SUM(count) as count 
FROM
  (SELECT
      t2.Name as col,
      ROUND (   
          (
              CHAR_LENGTH(t1.ColA)
              - CHAR_LENGTH( REPLACE (t1.ColA, t2.Name, "") ) 
          ) / CHAR_LENGTH(t2.Name)        
      ) as count    
  FROM TableA as t1
  INNER JOIN TableB as t2) as SubB 
GROUP BY col, count
HAVING count > 0;

SQL FIDDLE