求一条需要类似用split分割后统计的SQL语句

[dydxf]

产品编号 数量
AA-BB-1 1
AA-BC-222 2
AA-BC-22 3
AA-BC-22-2 2
需要把前面两个分隔符-连接的字符取出来做统计，计算结果要如下

AA-BB-1 1
AA-BC-222 2
AA-BC-22 5

昨天没有表示清楚，不好意思！
谢谢！

[Mr_Nice]

--> 测试数据：[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([产品编号] varchar(10),[数量] int)
insert [TB]
select 'AA-BB-1',1 union all
select 'AA-BC-222',2 union all
select 'AA-BC-22',3 union all
select 'AA-BC-22-2',2

select * from [TB]

SELECT  ISNULL(PARSENAME(replace([产品编号],'-','.'),4),'')
,ISNULL(PARSENAME(replace([产品编号],'-','.'),3),''),
ISNULL(PARSENAME(replace([产品编号],'-','.'),2),''),
ISNULL(PARSENAME(replace([产品编号],'-','.'),1),'')  FROM TB 

SELECT CASE WHEN PARSENAME(replace([产品编号],'-','.'),4) IS not NULL THEN 
	ISNULL(PARSENAME(replace([产品编号],'-','.'),4),'')+'-'+
	ISNULL(PARSENAME(replace([产品编号],'-','.'),3),'')+'-'+
	ISNULL(PARSENAME(replace([产品编号],'-','.'),2),'')
ELSE
ISNULL(PARSENAME(replace([产品编号],'-','.'),3),'')+'-'+
ISNULL(PARSENAME(replace([产品编号],'-','.'),2),'')+'-'+
ISNULL(PARSENAME(replace([产品编号],'-','.'),1),'')
END AS '产品编号'
,SUM([数量]) AS NUM
FROM TB
GROUP BY CASE WHEN PARSENAME(replace([产品编号],'-','.'),4) IS not NULL THEN 
	ISNULL(PARSENAME(replace([产品编号],'-','.'),4),'')+'-'+
	ISNULL(PARSENAME(replace([产品编号],'-','.'),3),'')+'-'+
	ISNULL(PARSENAME(replace([产品编号],'-','.'),2),'')
ELSE
ISNULL(PARSENAME(replace([产品编号],'-','.'),3),'')+'-'+
ISNULL(PARSENAME(replace([产品编号],'-','.'),2),'')+'-'+
ISNULL(PARSENAME(replace([产品编号],'-','.'),1),'')
END 

/*
产品编号	NUM
AA-BB-1	1
AA-BC-22	5
AA-BC-222	2*/

[Mr_Nice]

[Quote=引用 1 楼 orchidcat 的回复:]

SQL code
--> 测试数据：[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([产品编号] varchar(10),[数量] int)
insert [TB]
select 'AA-BB-1',1 union all
select 'AA-BC-222',2 union all
select ……
[/Quote]

写的有点儿乱
整理一下

SELECT  CASE WHEN PARSENAME(REPLACE([产品编号], '-', '.'), 4) IS NOT NULL
             THEN ISNULL(PARSENAME(REPLACE([产品编号], '-', '.'), 4), '') + '-'
                  + ISNULL(PARSENAME(REPLACE([产品编号], '-', '.'), 3), '') + '-'
                  + ISNULL(PARSENAME(REPLACE([产品编号], '-', '.'), 2), '')
             ELSE ISNULL(PARSENAME(REPLACE([产品编号], '-', '.'), 3), '') + '-'
                  + ISNULL(PARSENAME(REPLACE([产品编号], '-', '.'), 2), '') + '-'
                  + ISNULL(PARSENAME(REPLACE([产品编号], '-', '.'), 1), '')
        END AS '产品编号' ,
        SUM([数量]) AS NUM
FROM    TB
GROUP BY CASE WHEN PARSENAME(REPLACE([产品编号], '-', '.'), 4) IS NOT NULL
              THEN ISNULL(PARSENAME(REPLACE([产品编号], '-', '.'), 4), '') + '-'
                   + ISNULL(PARSENAME(REPLACE([产品编号], '-', '.'), 3), '') + '-'
                   + ISNULL(PARSENAME(REPLACE([产品编号], '-', '.'), 2), '')
              ELSE ISNULL(PARSENAME(REPLACE([产品编号], '-', '.'), 3), '') + '-'
                   + ISNULL(PARSENAME(REPLACE([产品编号], '-', '.'), 2), '') + '-'
                   + ISNULL(PARSENAME(REPLACE([产品编号], '-', '.'), 1), '')
         END 

/*
产品编号	NUM
AA-BB-1		1
AA-BC-22	5
AA-BC-222	2*/

[cjzm83]

group by left(产品编号,charindex('-',产品编号,charindex('-',产品编号)+1))

[zjw2004112]

select group by left(产品编号,charindex('-',产品编号,charindex('-',产品编号)+1)),sum(数量) from table  group by left(产品编号,charindex('-',产品编号,charindex('-',产品编号)+1))

[zjw2004112]

上面写错了

select left(产品编号,charindex('-',产品编号,charindex('-',产品编号)+1)),sum(数量) from table  group by left(产品编号,charindex('-',产品编号,charindex('-',产品编号)+1))

[ask_chang]

产品编号 数量
AA-BB-1 1
AA-BC-222 2
AA-BC-22 3
AA-BC-22-2 2
需要把前面两个分隔符-连接的字符取出来做统计，计算结果要如下

AA-BB-1 1
AA-BC-222 2
AA-BC-22 5

解释一下，怎么计算得1,2,5

[cjzm83]

上面的错了

if object_id('[TB]') is not null drop table [TB]
create table [TB]([产品编号] varchar(10),[数量] int)
insert [TB]
select 'AA-BB-1',1 union all
select 'AA-BC-222',2 union all
select 'AA-BC-22',3 union all
select 'AA-BC-22-2',2

declare @s varchar(100)
set @s = '-00-00-00-00'
select left(产品编号 + @s,charindex('-',产品编号+@s,charindex('-',产品编号,charindex('-',产品编号)+1)+1)-1) as 产品编号,
       sum([数量]) as total
from tb
group by 
left(产品编号 + @s,charindex('-',产品编号+@s,charindex('-',产品编号,charindex('-',产品编号)+1)+1)-1)