Question

sql里查询一个字段里的记录的多个类的汇总（几个字段按不同分类的汇总）

表结构如下:
machine sernum area PF
462 16205R3E AT P
462 16203H0N AT F
316 1620A7WP AT S
316 16206CCC AT S
想的到的结果是:
machine S P F
462 1 1
316 2

Answer 1

--技术要点：行转列
--以下提供SQL SERVER语句

--创建测试环境
create table tab
(
machine int,
sernum varchar(10),
area varchar(2),
PF varchar(1)
)
--制造数据
insert into tab select 462,'16205R3E','AT','P'
insert into tab select 462,'16203H0N','AT','F'
insert into tab select 316,'1620A7WP','AT','S'
insert into tab select 316,'16206CCC','AT','S'

--1. 静态行转列(所谓静态，是指的PF列只有P,F,S这三个值，或者值是固定的某几个值)
select machine,
max(case pf when 'P' then num else 0 end) as P,
max(case pf when 'F' then num else 0 end) as F,
max(case pf when 'S' then num else 0 end) as S
from
(select machine,pf,count(1) as num from tab group by machine,pf
)tb
group by machine

/* 结果集
machine P F S
----------- ----------- ----------- -----------
316 0 0 2
462 1 1 0

(2 row(s) affected)
*/

--2. 动态行转列(相对于静态的概念)
declare @sql varchar(8000)
set @sql = 'select machine as ' + 'machine'
select @sql = @sql + ' , max(case pf when ''' + pf + ''' then num else 0 end) [' + pf + ']'
from (select distinct pf from tab) as a
set @sql = @sql + ' from (select machine,pf,count(1) as num from tab group by machine,pf
)tb group by machine'
exec(@sql)

/* 结果集
machine F P S
----------- ----------- ----------- -----------
316 0 0 2
462 1 1 0

(2 row(s) affected)
*/

--删除环境
drop table tab

Answer 2

select matchine
,count(case when pf = 'S' then 1 else 0 end) as 'S'
,count(case when pf = 'P then 1 else 0 end) as 'P'
,count(case when pf = 'F' then 1 else 0 end) as 'F'
from table
GROUP BY matchine

Answer 3

select machine,
sum(decode(PF,'S',1,0)) S,
sum(decode(PF,'P',1,0)) P,
sum(decode(PF,'F',1,0)) F
from tab
group by machine

追问

非常感谢各位，如果是access数据库要怎么写呢？