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

Skyhoo 2010-10-27 10:39:59

001 ABC YY
002 BCD PP
003 CDD YY

001 ABC 2
002 BCD 1
003 CDD 2

...全文
35 点赞 收藏 5

5 条回复

Skyhoo 2010-10-27
[Quote=引用 3 楼 dawugui 的回复:]
SQL code
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' ,……
[/Quote]

duanzhi1984 2010-10-27

--利用子查询统计即可。楼主要多看看基础知识
``````select 字段1, 字段2,字段3=(select count(1) from a where 字段3 = t.字段3)
from tb ``````

--

dawugui 2010-10-27
``````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')
go

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

drop table a

/*

---------- ---------- -----------
001        ABC        2
002        BCD        1
003        CDD        2

（所影响的行数为 5 行）
*/``````

SQLCenter 2010-10-27
``````--> 测试数据：#
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 *, count(*)over(partition by 字段3)相同个数 from #

/*

-------- -------- -------- -----------
004      ADDD     KK       2
002      BCD      PP       1
003      CDD      YY       2
001      ABC      YY       2
*/
``````

dawugui 2010-10-27
select 字段1, 字段2,字段3=(select count(1) from a where 字段3 = t.字段3) from a t

2.1w+

MS-SQL Server 疑难问题

2010-10-27 10:39