t_id t_count t_weight
a0101 10 0.1
a0102 10 0.2
a0101-1.2 20 0.2
a0101-1.3 20 0.2
a0102-2.0 30 0.6
结果：(如果t_id有“-”就取该字符的左边内容，“-”位置不定，如果没有该字符，就取本内容）
t_id t_count t_weight
a0101 50 0.5 'a0101、a0101-1.2、a0101-1.3 的t_count,t_weight的汇总
a0102 40 0.8 'a0102、a0102-2.0 的t_count,t_weight的汇总
------解决思路----------------------
/* 测试数据
WITH table1(t_id,t_count,t_weight) AS (
SELECT 'a0101',10,0.1 UNION ALL
SELECT 'a0102',10,0.2 UNION ALL
SELECT 'a0101-1.2',20,0.2 UNION ALL
SELECT 'a0101-1.3',20,0.2 UNION ALL
SELECT 'a0102-2.0',30,0.6
)*/
SELECT t_id, SUM(t_count) t_count, SUM(t_weight) t_weight
FROM (
SELECT LEFT(t_id,
CHARINDEX('-',t_id+'-')-1) AS t_id,
t_count,
t_weight
FROM table1
) t
GROUP BY t_id
t_id t_count t_weight
--------- ----------- ---------------------------------------
a0101 50 0.5
a0102 40 0.8
------解决思路----------------------
with cte(t_id,t_count,t_weight)
as
(
select 'a0101' ,10,0.1 union all
select 'a0102' ,10,0.2 union all
select 'a0101-1.2',20,0.2 union all
select 'a0101-1.3',20,0.2 union all
select 'a0102-2.0',30,0.6
)
select (case when CHARINDEX('-',t_id)>0 then LEFT(t_id,CHARINDEX('-',t_id)-1) else t_id end) as t_id,SUM(t_count) as t_count,SUM(t_weight) as t_weight from cte
group by (case when CHARINDEX('-',t_id)>0 then LEFT(t_id,CHARINDEX('-',t_id)-1) else t_id end)
--结果
/*
t_id t_count t_weight
a0101 50 0.5
a0102 40 0.8
*/