我有每一行具有逗号分隔值下表：
ID
-----------------------------------------------------------------------------
10031,10042
10064,10023,10060,10065,10003,10011,10009,10012,10027,10004,10037,10039
10009
20011,10027,10032,10063,10023,10033,20060,10012,10020,10031,10011,20036,10041
我需要一个计数的每个ID （一GROUPBY）。
我只是想避免光标执行和难倒如何做到这一点没有游标。
任何帮助，将不胜感激 ！
您将要使用拆分功能：
create FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))
returns @temptable TABLE (items varchar(MAX))
as
begin
declare @idx int
declare @slice varchar(8000)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end;
然后你就可以查询在以下方式中的数据：
select items, count(items)
from table1 t1
cross apply dbo.split(t1.id, ',')
group by items
请参阅SQL拨弄演示
好了，该解决方案我总是用，而且很可能有可能是一个更好的办法，是使用将分裂的一切功能。 没有使用游标，只要while循环。
if OBJECT_ID('splitValueByDelimiter') is not null
begin
drop function splitValueByDelimiter
end
go
create function splitValueByDelimiter (
@inputValue varchar(max)
, @delimiter varchar(1)
)
returns @results table (value varchar(max))
as
begin
declare @delimeterIndex int
, @tempValue varchar(max)
set @delimeterIndex = 1
while @delimeterIndex > 0 and len(isnull(@inputValue, '')) > 0
begin
set @delimeterIndex = charindex(@delimiter, @inputValue)
if @delimeterIndex > 0
set @tempValue = left(@inputValue, @delimeterIndex - 1)
else
set @tempValue = @inputValue
if(len(@tempValue)>0)
begin
insert
into @results
select @tempValue
end
set @inputValue = right(@inputValue, len(@inputValue) - @delimeterIndex)
end
return
end
之后，你可以这样调用的输出：
if object_id('test') is not null
begin
drop table test
end
go
create table test (
Id varchar(max)
)
insert
into test
select '10031,10042'
union all select '10064,10023,10060,10065,10003,10011,10009,10012,10027,10004,10037,10039'
union all select '10009'
union all select '20011,10027,10032,10063,10023,10033,20060,10012,10020,10031,10011,20036,10041'
select value
from test
cross apply splitValueByDelimiter(Id, ',')
希望它能帮助，虽然我仍然可以通过循环的一切
重申上述关于不把多个值到一个单一的列中的注释（使用一个单独的子表，每行一个值！）之后，
然而，一个可能的方法：使用UDF为分隔字符串转换为表格。 一旦所有的值都被转换为表格，将所有的表到一个表，做一组通过该表。
Create Function dbo.ParseTextString (@S Text, @delim VarChar(5))
Returns @tOut Table
(ValNum Integer Identity Primary Key,
sVal VarChar(8000))
As
Begin
Declare @dlLen TinyInt -- Length of delimiter
Declare @wind VarChar(8000) -- Will Contain Window into text string
Declare @winLen Integer -- Length of Window
Declare @isLastWin TinyInt -- Boolean to indicate processing Last Window
Declare @wPos Integer -- Start Position of Window within Text String
Declare @roVal VarChar(8000)-- String Data to insert into output Table
Declare @BtchSiz Integer -- Maximum Size of Window
Set @BtchSiz = 7900 -- (Reset to smaller values to test routine)
Declare @dlPos Integer -- Position within Window of next Delimiter
Declare @Strt Integer -- Start Position of each data value within Window
-- -------------------------------------------------------------------------
-- ---------------------------
If @delim is Null Set @delim = '|'
If DataLength(@S) = 0 Or
Substring(@S, 1, @BtchSiz) = @delim Return
-- --------------------------------------------
Select @dlLen = DataLength(@delim),
@Strt = 1, @wPos = 1,
@wind = Substring(@S, 1, @BtchSiz)
Select @winLen = DataLength(@wind),
@isLastWin = Case When DataLength(@wind) = @BtchSiz
Then 0 Else 1 End,
@dlPos = CharIndex(@delim, @wind, @Strt)
-- --------------------------------------------
While @Strt <= @winLen
Begin
If @dlPos = 0 Begin -- No More delimiters in window
If @isLastWin = 1 Set @dlPos = @winLen + 1
Else Begin
Set @wPos = @wPos + @Strt - 1
Set @wind = Substring(@S, @wPos, @BtchSiz)
-- ----------------------------------------
Select @winLen = DataLength(@wind), @Strt = 1,
@isLastWin = Case When DataLength(@wind) = @BtchSiz
Then 0 Else 1 End,
@dlPos = CharIndex(@delim, @wind, 1)
If @dlPos = 0 Set @dlPos = @winLen + 1
End
End
-- -------------------------------
Insert @tOut (sVal)
Select LTrim(Substring(@wind,
@Strt, @dlPos - @Strt))
-- -------------------------------
-- Move @Strt to char after last delimiter
Set @Strt = @dlPos + @dlLen
Set @dlPos = CharIndex(@delim, @wind, @Strt)
End
Return
End
然后写，（使用表模式），
Declare @AllVals VarChar(8000)
Select @AllVals = Coalesce(@allVals + ',', '') + ID
From Table Where ID Is Not null
-- -----------------------------------------
Select sVal, Count(*)
From dbo.ParseTextString(@AllVals, ',')
Group By sval
