select replace(substring(名称,5,3),'(',''),sum(贪污金额) from tb group by replace(substring(名称,5,3),'(','')
CREATE TABLE TB ( 名称 VARCHAR(20), 贪污金额 INT )INSERT INTO dbo.TB SELECT '001-郭美美',100 UNION ALL SELECT '001-郭美美',200 UNION ALL SELECT '002-王军',200 UNION ALL SELECT '002-王军',150 UNION ALL SELECT '001-郭美美(红十字)',140 UNION ALL SELECT '001-郭美美(开房)',230 UNION ALL SELECT '002-王军(红十字)',200 SELECT 名称,SUM(贪污金额) FROM ( SELECT (CASE WHEN CHARINDEX('(',名称)>0 THEN LEFT(名称,CHARINDEX('(',名称)-1) ELSE 名称 END )AS 名称,贪污金额 FROM TB ) a GROUP BY a.名称名称 -------------------- ----------- 001-郭美美 670 002-王军 550(2 row(s) affected)
select replace(substring(名称,5,3),'(',''),sum(贪污金额) from a group by replace(substring(名称,5,3),'(','')
select replace(substring(名称,1,7),'(',''),sum(贪污金额) from a group by replace(substring(名称,1,7),'(','')
from tb group by replace(substring(名称,5,3),'(','')
CREATE TABLE TB
(
名称 VARCHAR(20),
贪污金额 INT
)INSERT INTO dbo.TB
SELECT '001-郭美美',100
UNION ALL
SELECT '001-郭美美',200
UNION ALL
SELECT '002-王军',200
UNION ALL
SELECT '002-王军',150
UNION ALL
SELECT '001-郭美美(红十字)',140
UNION ALL
SELECT '001-郭美美(开房)',230
UNION ALL
SELECT '002-王军(红十字)',200
SELECT 名称,SUM(贪污金额)
FROM
(
SELECT (CASE WHEN CHARINDEX('(',名称)>0 THEN LEFT(名称,CHARINDEX('(',名称)-1) ELSE 名称 END )AS 名称,贪污金额
FROM TB
) a
GROUP BY a.名称名称
-------------------- -----------
001-郭美美 670
002-王军 550(2 row(s) affected)
from a group by replace(substring(名称,5,3),'(','')
from a group by replace(substring(名称,1,7),'(','')