select T.bssID,T.BtsID,temp.num from tb T,(select BssID,BtsID,sum(CellID) as num from tb group by BssID,BtsID )temp where T.bssID=temp.bssID and T.btsID=temp.BtsID
if object_id('tb')is not null drop table tb go create table tb(BssID int, BtsID int, CellID int, CellNum int) insert tb select 1, 1 ,0 ,null insert tb select 1, 1 ,1 ,null insert tb select 1, 1 ,2 ,null insert tb select 1, 2 ,0 ,null insert tb select 1, 2 ,1,null update tb set cellnum=b.cnt from ( select bssid,btsid ,count(*)cnt from tb group by bssid,btsid)b where tb.bssid=b.bssid and tb.btsid=b.btsid select * from tb /*BssID BtsID CellID CellNum ----------- ----------- ----------- ----------- 1 1 0 3 1 1 1 3 1 1 2 3 1 2 0 2 1 2 1 2*/
from tb T,(select BssID,BtsID,sum(CellID) as num
from tb
group by BssID,BtsID )temp
where T.bssID=temp.bssID and T.btsID=temp.BtsID
go
create table tb(BssID int, BtsID int, CellID int, CellNum int)
insert tb select 1, 1 ,0 ,null
insert tb select 1, 1 ,1 ,null
insert tb select 1, 1 ,2 ,null
insert tb select 1, 2 ,0 ,null
insert tb select 1, 2 ,1,null
update tb set cellnum=b.cnt from (
select bssid,btsid ,count(*)cnt from tb group by bssid,btsid)b where tb.bssid=b.bssid and tb.btsid=b.btsid
select * from tb
/*BssID BtsID CellID CellNum
----------- ----------- ----------- -----------
1 1 0 3
1 1 1 3
1 1 2 3
1 2 0 2
1 2 1 2*/