MySQL：统计单词在单元格中出现的次数，并将数字放在bra中单词的旁边

发布于12月15日

我需要桌子有:

Account Prefix需要有‘Oil’(#)来计算该单元格中有多少次‘Oil’

SELECT
  jurisdiction,
  Replace(REPLACE(REPLACE(GROUP_CONCAT(DISTINCT LEFT(fileno, 1)ORDER BY fileno DESC SEPARATOR ', '),'O','Oil')'M','Mobile'),'R','Real')AS accountprefix
From
  table.tester
WHERE
  statuscode = 'A'
  AND builddateid = '2023'
GROUP BY jurisdiction;

Account Prefix需要有‘Oil’(#)来计算该单元格中有多少次‘Oil’

推荐答案

在我修改查询的地方try 执行以下操作，通过将‘O’替换为‘Oil(Count)’来计算和显示‘Oil’在accountprefix中出现的次数，其中计数表示每个jurisdiction在fileno列中出现‘O’的次数.

SELECT
  jurisdiction,
  REPLACE(
    REPLACE(
      REPLACE(
        GROUP_CONCAT(DISTINCT LEFT(fileno, 1) ORDER BY fileno DESC SEPARATOR ', '),
        'O', CONCAT('Oil (', COUNT(CASE WHEN LEFT(fileno, 1) = 'O' THEN 1 END), ')')
      ),
      'M', 'Mobile'
    ),
    'R', 'Real'
  ) AS accountprefix
FROM
  table.tester
WHERE
  statuscode = 'A'
  AND builddateid = '2023'
GROUP BY jurisdiction;