select left(name,3), SUM([count]) from tb group by left(name,3) ------ ----------- 百仕达 50 皇御苑 326 可园 153 丽湖花 130 名津广 133 御景华 142 中海怡 177只能这样才最接近lz的要求了
意思是说propertyName重复的Total列名数据相加吗 如果是,试试 select propertyname,sum(total) from 表名 group by propertyname having count(propertyname)>1 试试,我如果自个要试要建库建表,麻烦,如果不行,我在我这试试
select SUBSTRING(propertyName ,1,len(propertyName )-2),sum(total) from ( select a.propertyName ,a.total from tb as a, (select LEFT(propertyName ,3) as aa from tb group by LEFT(propertyName ,3) having count(left(propertyName ,3))>1) as b where left(a.propertyName ,3) =b.aa) t group by SUBSTRING(propertyName ,1,len(propertyName )-2) union select a.propertyName ,a.total from tb as a, (select LEFT(propertyName ,3) as aa from tb group by LEFT(propertyName ,3) having count(left(propertyName ,3))=1) as b where left(a.propertyName ,3) =b.aa ---------------------------------------------------------------------------------------------------- ----------- 百仕达花园 50 皇御苑 326 可园 153 丽湖花园 130 名津广场 133 御景华城花园 142 中海怡翠山庄 177(7 行受影响)
皇御苑 199 B区
中海怡翠山庄 177
可园 153
御景华城花园 142
名津广场 133
丽湖花园 130
皇御苑 127 A区
百仕达花园 30 一期
百仕达花园 20 二期
皇御苑B区 199
中海怡翠山庄 177
可园 153
御景华城花园 142
名津广场 133
丽湖花园 130
皇御苑A区 127
百仕达花园一期 30
百仕达花园二期 20 我想得到以下数据
propertyName Total
皇御苑 326
中海怡翠山庄 177
可园 153
御景华城花园 142
名津广场 133
丽湖花园 130
百仕达花园 50
SUM([count]) from tb group by left(name,3)
------ -----------
百仕达 50
皇御苑 326
可园 153
丽湖花 130
名津广 133
御景华 142
中海怡 177只能这样才最接近lz的要求了
如果是,试试
select propertyname,sum(total) from 表名 group by propertyname having count(propertyname)>1
试试,我如果自个要试要建库建表,麻烦,如果不行,我在我这试试
他的意思 A小区一期,A小区二期都要算做A小区来计算初始的数据是第三方给我的接口数据,已经不可能再设置数据库结构拉
select SUBSTRING(propertyName ,1,len(propertyName )-2),sum(total) from (
select a.propertyName ,a.total from tb as a,
(select LEFT(propertyName ,3) as aa from tb group by LEFT(propertyName ,3) having count(left(propertyName ,3))>1) as b
where left(a.propertyName ,3) =b.aa) t
group by SUBSTRING(propertyName ,1,len(propertyName )-2)
union
select a.propertyName ,a.total from tb as a,
(select LEFT(propertyName ,3) as aa from tb group by LEFT(propertyName ,3) having count(left(propertyName ,3))=1) as b
where left(a.propertyName ,3) =b.aa
---------------------------------------------------------------------------------------------------- -----------
百仕达花园 50
皇御苑 326
可园 153
丽湖花园 130
名津广场 133
御景华城花园 142
中海怡翠山庄 177(7 行受影响)