根据条件对数据求和的sql查询

Question

我的表数据和如下所示

---------------------
Building   | Area m2
---------------------
|Dante 12  |   10
|Dante 10  |    5
|Dante 9   |    2
|Crandley  |   20
|Bence     |   30

我想要对建筑面积求和，但像'%dante%‘这样的建筑物我想合并成如下所示的和"Dante“：

-------------------
Building | Area m2
-------------------
Dante    |  17
Crandley |  20
Bence    |  30

Answer 1

Group by with case将对给定的样本数据执行此操作，此类型的like也可以使用索引

Select 
case when building like 'dante%' then 'Dante' else building end,
sum([area m2])
from
table
group by 
case when building like 'dante%' then 'Dante' else building end

如果有其他列的数字，可以先去掉这些数字，然后再做下面的事情

;with cte
as
(select REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE (building, '0', ''),
'1', ''),
'2', ''),
'3', ''),
'4', ''),
'5', ''),
'6', ''),
'7', ''),
'8', ''),
'9', '') as bulding,aream2
 from #temp
)
select building,sum(aream2)
from
cte 
group by 
building

参考：

Remove numbers found in string column

Answer 2

仅当要删除数值时，才起作用

数值将被替换(嵌套的替换函数最深可达32层)，并按列分组。

SELECT x.Building, SUM(x.AreaM2) AS [Area m2]
FROM (
    SELECT 
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE (Building, '0', '')
        ,'1', '')
        ,'2', '')
        ,'3', '')
        ,'4', '')
        ,'5', '')
        ,'6', '')
        ,'7', '')
        ,'8', '')
        ,'9', '') [Building], AreaM2 FROM Buildings) AS x
GROUP BY x.Building