alter procedure split @str varchar(128) as begin declare @p integer, @len integer declare @word varchar(128) declare @result table(word varchar(128)) set @len = len(@str) set @p = (select charindex(';', @str)) while @p > 0 begin set @word = substring(@str, 1, @p-1) set @word = rtrim(ltrim(@word)) if (@word <> '') begin insert into @result values (@word) end set @str = substring(@str, @p+1, @len) set @p = (select charindex(';', @str)) end set @word = rtrim(ltrim(@word)) if (@word <> '') begin insert into @result values (@word) end select * from @result end; go
declare @result table(pid varchar(128)) declare @pid varchar(128) declare @id int = (select min(id) from @src) while @id is not null begin set @pid = (select pid from @src where id=@id) insert into @result exec split @pid set @id = (select min(id) from @src where id>@id) end select pid, cnt=count(*) from @result group by pid
----------------------------------------------
-
更正一下: create procedure split @str varchar(128) as begin declare @p integer, @len integer declare @word varchar(128) declare @result table(word varchar(128)) set @len = len(@str) set @p = (select charindex(';', @str)) while @p > 0 begin set @word = substring(@str, 1, @p-1) set @word = rtrim(ltrim(@word)) if (@word <> '') begin insert into @result values (@word) end set @str = substring(@str, @p+1, @len) set @p = (select charindex(';', @str)) end set @str = rtrim(ltrim(@str)) if (@str <> '') begin insert into @result values (@str) end select * from @result end; go
declare @result table(pid varchar(128)) declare @pid varchar(128) declare @id int = (select min(id) from @src) while @id is not null begin set @pid = (select pid from @src where id=@id) insert into @result exec split @pid set @id = (select min(id) from @src where id>@id) end select pid, cnt=count(*) from @result group by pid
SELECT tempItem,count(*) tempItemCount FROM ( SELECT [value] = CONVERT(XML, '<v>' + REPLACE((select (select ''+pid from @src for xml path(''))),';', '</v><v>') + '</v>') ) aa OUTER APPLY( SELECT tempItem = N.v.value('.', 'nvarchar(100)') FROM aa.[value].nodes('/v') N(v) ) bb where tempItem<>'' group by tempItem order by len(tempItem),tempItem
----------------------------------------------
ABFrameWork(307455944)、ABMeeting(644245859提问)作者,QQ 16187001(有问题请加群提问或私信,谢谢)
;WITH cte AS (select stuff((select ';'+pid from @src for xml path('')), 1, 1, '') AS list), E1 AS ( SELECT 1 AS N UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1), E2 AS (SELECT 1 AS N FROM E1 a, E1 b), E4 AS (SELECT 1 AS N FROM E2 a, E2 b), E42 AS (SELECT 1 AS N FROM E4 a, E2 b), cteTally AS (SELECT 0 AS N UNION ALL SELECT TOP (DATALENGTH(ISNULL((SELECT list FROM cte),1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E42), cteStart AS (SELECT t.N+1 AS N1 FROM cteTally t, cte WHERE (SUBSTRING(cte.list,t.N,1) = ';' OR t.N = 0)), ids AS (SELECT id = SUBSTRING(cte.list, s.N1, ISNULL(NULLIF(CHARINDEX(';',cte.list,s.N1),0)-s.N1,8000)) FROM cteStart s, cte) SELECT id, COUNT(*) AS cnt FROM ids WHERE id<>'' GROUP BY id ORDER BY LEN(id), ids.id