Разделенный текст с точкой с запятой в SQL с накопленным счетчиком

у меня есть таблица ниже

Program          FileCount
B1               1
A1;B2            2
A2;B3            1
A3;C1;B4         1
A3;C2;D1;B5;B6   3
C3;D2;B7         1
B8;B9            2
B8;B9            2

Меня интересует только программа B, и я хотел бы пройти всю программу B

Program    FileCount
B1         1
B2         2  
B3         1  
B4         1
B5         3
B6         3
B7         1
B8         4
B9         4

Пожалуйста, обрати внимание :

  1. Я использую Microsoft SQL Server 2012
  2. имя для программы B не является однородным, состоящим только из 2 цифр, но различается (например, B1233456, B123)

Я использовал приведенный ниже синтаксис, однако он не дает того, что мне нужно.

SELECT

distinct CASE WHEN PATINDEX('%B%', Program)>0 THEN SUBSTRING(Program, PATINDEX('%B%', Program), 50) ELSE '' END as Program, 

sum(cast (Filecount as integer)) as FileCount

FROM dbo.Database1
where Program like '%B%'
group by Program

Большое спасибо, ребята, за вашу помощь.

С уважением,

Адхи


sql sql-server delimited
person Adhitya Sanusi    schedule 25.08.2017    source источник
comment
Возможный дубликат функции с разделителями в SQL для разделения данных между полу -двоеточие   -  person Michał Turczyn    schedule 25.08.2017
comment
это не дубликат, так как это более сложная проблема, связанная с накопленным значением количества файлов.   -  person Adhitya Sanusi    schedule 25.08.2017

Ответы (2)


arrow_upward
1
arrow_downward

Сначала создайте пользовательскую функцию разделения

        CREATE FUNCTION [dbo].[Split]
        (
            @String NVARCHAR(4000),
            @Delimiter NCHAR(1)
        )
        RETURNS TABLE 
        AS
        RETURN 
        (
            WITH Split(stpos,endpos) 
            AS(
                SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
                UNION ALL
                SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
                    FROM Split
                    WHERE endpos > 0
            )
            SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
                'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
            FROM Split
        )

Чем вы можете использовать этот запрос, чтобы сделать выбор:

    declare @t table (Program Varchar(max), Filecount integer)

    insert into @t(Program ,   FileCount)
    select 'B1 ',  1
    union all select 'A1;B2', 2
    union all select 'A2;B3', 1
    union all select 'A3;C1;B4', 1
    union all select 'A3;C2;D1;B5;B6', 3
    union all select 'C3;D2;B7', 1
    union all select 'B8;B9', 2
    union all select 'B81;B9', 2


    SELECT DISTINCT s.Data, t.Filecount
    FROM @t t
    CROSS APPLY dbo.Split(t.Program, ';') s
    WHERE Data like 'B%'
person Egbert    schedule 25.08.2017
comment
ПОТРЯСАЮЩИЕ !! Спасибо Эгберт. Я сохраню эту функцию. Действительно ценю это. - person Adhitya Sanusi; 30.08.2017

arrow_upward
1
arrow_downward

Ну вот. Sql server 2016 имеет новую функцию разделения, которую я использую там, где я использую ssf_split. моя функция ssf_split возвращает таблицу с одним столбцом с именем Textline.

Я надеюсь, что это достаточно ясно, что уже поздно :)

declare @t table (Program Varchar(max), Filecount integer)

insert into @t(Program ,   FileCount)
      select 'B1            ',   1
union all select 'A1;B2         ',   2
union all select 'A2;B3         ',   1
union all select 'A3;C1;B4      ',   1
union all select 'A3;C2;D1;B5;B6',   3
union all select 'C3;D2;B7      ',   1
union all select 'B8;B9         ',   2
union all select 'B8;B9         ',   2


select X.textline as Program, sum(t.filecount) as FileCount from @t t
Cross apply dbo.ssf_split(t.program,';') x where SUBSTRING(x.TextLine, 1,1) = 'b'
Group by x.TextLine
order by x.textline

будет производить этот вывод:

Program                FileCount
---------------------- -----------
B1                     1
B2                     2
B3                     1
B4                     1
B5                     3
B6                     3
B7                     1
B8                     4
B9                     4

Таким образом, функция 2016 — STRING_SPLIT (строка, разделитель), а имя столбца – значение.

поэтому замените dbo.ssf_split на STRING_SPLIT и текстовую строку на значение, и все будет хорошо.

person Jay Wheeler    schedule 25.08.2017
comment
Да, я прочитал статью о String_Split. Если бы я только мог использовать его в SQL 2012 :(. - person Adhitya Sanusi; 30.08.2017