分组查询 统计问题，高手赐教！

select 字段1, 字段2,字段3=(select count(1) from a where 字段3 = t.字段3) 

from tb 

create table A(字段1 varchar(10),字段2 varchar(10),字段3 varchar(10))

insert into a values('001', 'ABC'   ,'YY')

insert into a values('002', 'BCD'   ,'PP')

insert into a values('003', 'CDD'   ,'YY')

insert into a values('004', 'ADDD'  ,'KK')

insert into a values('005', 'ADDaD' ,'KK')

go



select 字段1, 字段2,字段3=(select count(1) from a where 字段3 = t.字段3) from a t



drop table a



/*

字段1        字段2        字段3         

---------- ---------- ----------- 

001        ABC        2

002        BCD        1

003        CDD        2

004        ADDD       2

005        ADDaD      2



（所影响的行数为 5 行）

*/

--> 测试数据：#

if object_id('tempdb.dbo.#') is not null drop table #

create table #(字段1 varchar(8), 字段2 varchar(8), 字段3 varchar(8))

insert into #

select '001', 'ABC', 'YY' union all

select '002', 'BCD', 'PP' union all

select '003', 'CDD', 'YY' union all

select '004', 'ADDD', 'KK' union all

select '005', 'ADDaD', 'KK'



select *, count(*)over(partition by 字段3)相同个数 from #



/*

字段1    字段2    字段3    相同个数

-------- -------- -------- -----------

004      ADDD     KK       2

005      ADDaD    KK       2

002      BCD      PP       1

003      CDD      YY       2

001      ABC      YY       2

*/