如果,这个要查询的字符串,应该是一定规律的不然,你的结果应该如下
&aaa& 5次
&aaa&&bbb& 2次
&aaa&&ddd& 2次
&aaa&&ddd&&eee& 2次
& n次
&a n次
&aa n次
&aaa n次
...
&aaa& 5次
&aaa&&bbb& 2次
&aaa&&ddd& 2次
&aaa&&ddd&&eee& 2次
& n次
&a n次
&aa n次
&aaa n次
...
调试欢乐多
SUM( CASE WHEN 字段名 LIKE '%&aaa&&bbb&%' THEN 1 ELSE 0 END ) AS ' &aaa&&bbb&',
SUM( CASE WHEN 字段名 LIKE '%&aaa&&ddd&%' THEN 1 ELSE 0 END ) AS '&aaa&&ddd&',
SUM( CASE WHEN 字段名 LIKE '%&aaa&&ddd&&eee&%' THEN 1 ELSE 0 END ) AS '&aaa&&ddd&&eee&'
FROM 表名
然后遍历列的组合,将每一种组合单独统计出数据,汇总到一张表中,然后再group BY sum统计出来
好吧,我能想到的只能这么写
用 CHARINDEX + SUBSTRING + LEN 将字段进行拆分,
然后遍历列的组合,将每一种组合单独统计出数据,汇总到一张表中,然后再group BY 统计出来
好吧,我能想到的只能这么写
SELECT 1,'&aaa&&bbb&' UNION ALL
SELECT 2,'&aaa&&bbb&&123&' UNION ALL
SELECT 3,'&aaa&&ccc&&456&' UNION ALL
SELECT 4,'&aaa&&ddd&&eee&&222&' UNION ALL
SELECT 5,'&aaa&&ddd&&eee&&789&'
)
,t1 (text,i) AS (
SELECT text,
CHARINDEX('&&',text+'&')+1
FROM table1
WHERE CHARINDEX('&&',text+'&')>0 UNION ALL
SELECT text,
CHARINDEX('&&',text+'&',i+1)+1
FROM t1
WHERE CHARINDEX('&&',text+'&',i+1)>0
)
,t2 (prefix) AS (
SELECT DISTINCT LEFT(text,i)
FROM t1
)
SELECT t2.prefix,
COUNT(*) c
FROM t2
JOIN table1 t
ON CHARINDEX(t2.prefix,t.text)=1
GROUP BY t2.prefix
HAVING COUNT(*) > 1
prefix c
-------------------- -----------
&aaa&& 5
&aaa&&bbb& 2
&aaa&&ddd&& 2
&aaa&&ddd&&eee&& 2
SELECT 1,'&aaa&&bbb&'
UNION ALL SELECT 2,'&aaa&&bbb&&123&'
UNION ALL SELECT 3,'&aaa&&ccc&&456&'
UNION ALL SELECT 4,'&aaa&&ddd&&eee&&222&'
UNION ALL SELECT 5,'&aaa&&ddd&&eee&&789&'
)
,TBCTE AS(
SELECT ID,V,ROW_NUMBER()OVER(PARTITION BY T1.ID ORDER BY GETDATE())RN FROM
(SELECT ID,CAST('<V>_'+REPLACE(SUBSTRING(text,2,LEN(text)-2),'&&','_</V><V>_')+'_</V>'AS XML)VS FROM TB)T1
CROSS APPLY(SELECT N.V.value('.','varchar(100)')V FROM T1.VS.nodes('/V') N(V))T2
)
,CTE1 AS(
SELECT ID,V,RN FROM TBCTE T
WHERE EXISTS(SELECT 1 FROM TBCTE WHERE T.ID<>ID AND T.V=V)
)
,CTE AS(
SELECT ID,CAST(V AS VARCHAR(8000))V,RN C FROM CTE1
WHERE RN=1
UNION ALL
SELECT T1.ID,T2.V+T1.V,T2.C+1 FROM CTE1 T1
JOIN CTE T2 ON T1.ID=T2.ID AND T1.RN=T2.C+1
)
SELECT REPLACE(V,'_','&'),COUNT(V) FROM CTE
GROUP BY V
HAVING COUNT(V)>1