2个回答
展开全部
原表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数据库,如果你用的别的数据库,应该也有功能类似的相关函数。
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询