比如：

id     姓名      状态  

1      刘德华    1

2      刘德华    2

3      周华健    0

4      吴彦祖    1

在access中，用一条sql查询语句，生成结果为:

姓名    总数    状态0    状态1   状态2

刘德华   2       0          1       1

周华健   1       1          0       0

吴彦祖   1       0          1       0

答案一：

if not object_id('tb') is null    drop table tbGoCreate table tb([id] int,[姓名] nvarchar(3),[状态] int)Insert tbselect 1,N'刘德华',1 union allselect 2,N'刘德华',2 union allselect 3,N'周华健',0 union allselect 4,N'吴彦祖',1Goselect [姓名],       count(*)总数,       sum(case when [状态]=0 then 1 else 0 end )[状态0],       sum(case when [状态]=1 then 1 else 0 end )[状态1],       sum(case when [状态]=2 then 1 else 0 end )[状态2]from tbgroup by [姓名]

答案二：

select name,count(*),sum(iif(audit=0,1,0)), sum(iif(audit=1,1,0)),sum(iif(audit=2,1,0))from tbgroup by name