mysql-正在尝试将'group\u concat'与'count()合并`

mctunoxg  于 2021-06-24  发布在  Mysql
关注(0)|答案(1)|浏览(428)
SELECT A.id  ,A.Prospect_Id, GROUP_CONCAT(DISTINCT A.MYVIEW SEPARATOR ' , ') AS myview
FROM
   (SELECT id, Prospect_Id, CONCAT(Current_Status, '(', count(Current_Status), ')') AS MYVIEW     
    FROM prospectdata1
    GROUP BY Prospect_Id) A
GROUP BY  A.prospect_id
HAVING myview REGEXP 'PHONE_FOLLOW_UP.*DUPLICATE'

输出:

预期产量:

ct2axkht

ct2axkht1#

您需要同时按潜在客户id和当前状态分组:

SELECT MIN(MinId) AS id
     , Prospect_Id
     , GROUP_CONCAT(CONCAT(Current_Status, '(', SUM(Cnt), ')') SEPARATOR ' , ') AS myview
FROM (
  SELECT Prospect_Id
       , Current_Status
       , COUNT(*) Cnt
       , MIN(id) MinId
   FROM prospectdata1
  WHERE Current_Status REGEXP 'PHONE_FOLLOW_UP.*DUPLICATE'
  GROUP BY Prospect_Id
      , Current_Status
) A
GROUP BY prospect_id

相关问题