MYSQl-Group_Concat中的元素计数 [英] MYSQl - Count of elements inside Group_Concat

问题描述

我是MYSQL的新手，在group_concat语句中需要一些帮助.我有下表

I'm quite new to MYSQL and would need a little help in the group_concat statement. I have the below table

Seller Merchant CustomerID
S1     M1       C1
S1     M1       C1
S1     M1       C2
S1     M1       C3
S1     M1       C4
S2     M2       C5
S2     M2       C6
S3     M3       C6

对于同一卖方和商人的组合，所有具有不同客户ID的商品以及其重复次数的计数.

For the combination of same seller and merchant, all the items which has different customerIDs along with the count of how many times it is repeated.

我能够使用group_concat得出唯一客户IDS的数量，但无法获得该数量.

I'm able to derive count of unique customer IDS using group_concat but not able to get the count.

SELECT * , LENGTH(CUSTIDS) - LENGTH(REPLACE(CUSTIDS,',',''))+1 AS COUNT_OF_CUSTIDS
FROM (SELECT SELLER, MERCHANT, GROUP_CONCAT(CUSTOMERID SEPARATOR '|') AS CUSTIDS
FROM TABLE
GROUP BY SELLER, MERCHANT
HAVING COUNT(DISTINCT CUSTOMERID ) >1 
)

这给了我下面的结果

Seller Merchant CustomerID    COUNT_OF_CUSTIDS
S1     M1       C1,C2,C3,C4   4
S2     M2       C5,C6         2

而我想要下面的

Seller Merchant CustomerID                 COUNT_OF_CUSTIDS
S1     M1       C1(2),C2(1),C3(1),C4(1)    4
S2     M2       C5(1),C6(1)                2

推荐答案

您需要先在seller/merchant/customerid级别进行汇总才能获得计数.然后，您可以继续进行聚合:

You need to first aggregate at the seller/merchant/customerid level to get the count. Then you can continue with your aggregation:

  SELECT SELLER, MERCHANT,
         COUNT(*) as COUNT_OF_CUSTIDS,
         GROUP_CONCAT(CUSTOMERID, ' (', cnt, ')' SEPARATOR '|') AS CUSTIDS
  FROM (SELECT SELLER, MERCHANT, COUNT(*) as cnt
        FROM TABLE
        GROUP BY SELLER, MERCHANT, CUSTOMERID
       ) t
  GROUP BY SELLER, MERCHANT
  HAVING COUNT(* ) > 1 

这篇关于MYSQl-Group_Concat中的元素计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！