原表B:
1)实现一对一:
SQL:
SELECT DISTINCT CUSTOMER,REGEXP_SUBSTR(SHANGPIN, '[^,]+', 1, LEVEL) AS SHANGPIN
FROM CUSTOMERS
CONNECT BY LEVEL <= LENGTH(SHANGPIN) - LENGTH(REGEXP_REPLACE(SHANGPIN, ',', ''))+1
2)最终效果实现:
SQL:
SELECT A.SHANGPIN 产品,COUNT(A.CUSTOMER) 购买客户数
FROM (SELECT DISTINCT CUSTOMER,REGEXP_SUBSTR(SHANGPIN, '[^,]+', 1, LEVEL) SHANGPIN
FROM CUSTOMERS
CONNECT BY LEVEL <= LENGTH(SHANGPIN) - LENGTH(REGEXP_REPLACE(SHANGPIN, ',', ''))+1) A
GROUP BY SHANGPIN
这里我用的是Oracle数据库,如果你用的别的数据库,应该也有功能类似的相关函数。