select 姓名, (case when 类型='迟到' then 1 else 0 end)迟到次数, (case when 类型='迟到' then 扣钱 else 0 end)迟到扣钱, (case when 类型='病假' then 1 else 0 end)病假次数, (case when 类型='病假' then 扣钱 else 0 end)病假扣钱 from tb group by 姓名
select 姓名,迟到次数=sum(case when 类型='迟到' then 1 else 0 end), 迟到扣钱=sum(case when 类型='迟到' then 扣钱 else 0 end), 病假次数=sum(case when 类型='病假' then 1 else 0 end), 病假扣钱=sum(case when 类型='病假' then 扣钱 else 0 end) from tb group by 姓名
create table tb(姓名 nvarchar(10),时间 datetime,扣钱 int,类型 nvarchar(10)) insert into tb select 'x','2011-6-1',10,'迟到' insert into tb select 'x','2011-6-2',10,'迟到' insert into tb select 'x','2011-6-3',10,'病假' select 姓名, sum(case when 类型='迟到' then 1 else 0 end)迟到次数, sum(case when 类型='迟到' then 扣钱 else 0 end)迟到扣钱, sum(case when 类型='病假' then 1 else 0 end)病假次数, sum(case when 类型='病假' then 扣钱 else 0 end)病假扣钱 from tb group by 姓名 go drop table tb /* 姓名 迟到次数 迟到扣钱 病假次数 病假扣钱 ---------- ----------- ----------- ----------- ----------- x 2 20 1 10(1 行受影响) */
(case when 类型='迟到' then 1 else 0 end)迟到次数,
(case when 类型='迟到' then 扣钱 else 0 end)迟到扣钱,
(case when 类型='病假' then 1 else 0 end)病假次数,
(case when 类型='病假' then 扣钱 else 0 end)病假扣钱
from tb
group by 姓名
迟到扣钱=sum(case when 类型='迟到' then 扣钱 else 0 end),
病假次数=sum(case when 类型='病假' then 1 else 0 end),
病假扣钱=sum(case when 类型='病假' then 扣钱 else 0 end)
from tb group by 姓名
insert into tb select 'x','2011-6-1',10,'迟到'
insert into tb select 'x','2011-6-2',10,'迟到'
insert into tb select 'x','2011-6-3',10,'病假'
select 姓名,
sum(case when 类型='迟到' then 1 else 0 end)迟到次数,
sum(case when 类型='迟到' then 扣钱 else 0 end)迟到扣钱,
sum(case when 类型='病假' then 1 else 0 end)病假次数,
sum(case when 类型='病假' then 扣钱 else 0 end)病假扣钱
from tb
group by 姓名
go
drop table tb
/*
姓名 迟到次数 迟到扣钱 病假次数 病假扣钱
---------- ----------- ----------- ----------- -----------
x 2 20 1 10(1 行受影响)
*/