今天帮别人弄一个根据字段中的逗号分隔符计算每个月批量购买章节总数

首先建立一个计算某分隔符在字符串中含有的个数之函数：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [SplitToTable]
(
    @str        NVARCHAR(MAX),    --字符串
    @seprate    NVARCHAR(2),    --分隔符
    @removeQuot BIT = 0         --为1时，拆分出的字符串如果第1位与最后1位是单引号时，去掉
)
RETURNS @ret TABLE 
        (string NVARCHAR(512))
AS 
BEGIN
    DECLARE @iPos  INT
    DECLARE @i     INT 
    SET @i = 0
    DECLARE @left NVARCHAR(MAX)
    -- Chinese_PRC_CI_AS_KS_WS表示区分全角半角
    SET @iPos = CHARINDEX(@seprate, @str COLLATE Chinese_PRC_CI_AS_KS_WS) --获取分隔符位置
    IF @iPos = 0
    BEGIN
        IF @removeQuot = 1 AND LEFT(@str, 1) = '''' AND RIGHT(@str, 1) = ''''
        BEGIN
            SET @str = SUBSTRING(@str, 2, LEN(@str) - 2)
        END 
        INSERT INTO @ret
          (
            string
          )
        VALUES
          (
            @str
          )
        RETURN
    END
    
    WHILE @iPos > 0
    BEGIN
        SELECT @left = LEFT(@str, @iPos -1),
               @str = RIGHT(@str, LEN(@str) -@iPos)
        
        IF @removeQuot = 1 AND LEFT(@left, 1) = '''' AND RIGHT(@left, 1) = ''''
        BEGIN 
            SET @left = SUBSTRING(@left, 2, LEN(@left) - 2)
        END 
        INSERT INTO @ret
          (
            string
          )
        VALUES
          (
            @left
          )
        SET @iPos = CHARINDEX(@seprate, @str COLLATE Chinese_PRC_CI_AS_KS_WS)
        IF @iPos = 0
        BEGIN
            IF @removeQuot = 1 AND LEFT(@str, 1) = '''' AND RIGHT(@str, 1) = ''''
            BEGIN 
                SET @str = SUBSTRING(@str, 2, LEN(@str) - 2)
            END 
            INSERT INTO @ret
              (
                string
              )
            VALUES
              (
                @str
              )
        END
    END
    RETURN 
END

 

接下来就是循环计算每个字段所含有的个数：

 

declare @BeginTime as datetime set @BeginTime='2013-11-01'
declare @EndTime as datetime set @EndTime='2013-12-01'
declare @TotalCount as int set @TotalCount=0

declare @ChapterIDTable Cursor
 
set @ChapterIDTable= Cursor for select autoid,chapteridinfo from PandaCoinOrder_BulkPayChapter_2016 with(nolock) where createtime>=@BeginTime and createtime<@EndTime
open @ChapterIDTable
declare @AutoID bigint
declare @ChapterIDInfo as varchar(8000)

FETCH NEXT FROM @ChapterIDTable into @AutoID,@ChapterIDInfo
while @@fetch_status=0
 begin
 SELECT @TotalCount=@TotalCount+COUNT(1)-1 FROM dbo.SplitToTable(@ChapterIDInfo, ',', null)  
 Fetch Next From @ChapterIDTable into @AutoID,@ChapterIDInfo
 end

close @ChapterIDTable
deallocate @ChapterIDTable
select @TotalCount

 

今天帮别人做的第一件事情，记录一下。