考勤表
sysid ktype stime
47 迟到 2006-1-1
47 迟到 2006-1-2
47 请假 2006-1-3
48 迟到 2006-1-4
48 病假 2006-1-5 姓名表
sysid name
47 张三
48 李四我想得到以下结果
sysid name 迟到 请假 病假
47 张三 2 1 0
48 李四 1 0 1
sysid ktype stime
47 迟到 2006-1-1
47 迟到 2006-1-2
47 请假 2006-1-3
48 迟到 2006-1-4
48 病假 2006-1-5 姓名表
sysid name
47 张三
48 李四我想得到以下结果
sysid name 迟到 请假 病假
47 张三 2 1 0
48 李四 1 0 1
sum(case when ktype='迟到' then 1 else 0) as 迟到,
sum(case when ktype='请假' then 1 else 0) as 请假,
sum(case when ktype='病假' then 1 else 0) as 病假
from 考勤表 a,姓名表 b
where a.sysid=b.sysid
group by a.sysid,b.name
insert into 考勤表 select 47,'迟到','2006-1-1'
insert into 考勤表 select 47,'迟到','2006-1-2'
insert into 考勤表 select 47,'请假','2006-1-3'
insert into 考勤表 select 48,'迟到','2006-1-4'
insert into 考勤表 select 48,'病假','2006-1-5' create table 姓名表(sysid int,[name] nvarchar(100))
insert into 姓名表 select 47,'张三'
insert into 姓名表 select 48,'李四'--我想得到以下结果
--sysid name 迟到 请假 病假
--47 张三 2 1 0
--48 李四 1 0 1
--select * from 考勤表 select * from 姓名表select a.sysid,a.[name],
(select count(1) from 考勤表 where sysid=a.sysid and ktype='迟到')'迟到',
(select count(1) from 考勤表 where sysid=a.sysid and ktype='请假')'请假',
(select count(1) from 考勤表 where sysid=a.sysid and ktype='病假')'病假'
from 姓名表 adrop table 考勤表,姓名表
sum(case when ktype='迟到' then 1 else 0 end) as 迟到, --加END
sum(case when ktype='请假' then 1 else 0 end) as 请假,
sum(case when ktype='病假' then 1 else 0 end) as 病假
from 考勤表 a,姓名表 b
where a.sysid=b.sysid
group by a.sysid,b.name
sum(case a.ktype='迟到' then 1 else 0)as 迟到,
sum(case a.ktype='请假' then 1 else 0)as 请假,
sum(case 1.ktype='病假' then 1 else 0)as 病假
from 考勤表 a,姓名表b
where a.sysid=b.sysid
group by b.sysid,b.name
insert into 考勤表 select 47,'迟到','2006-1-1'
insert into 考勤表 select 47,'迟到','2006-1-2'
insert into 考勤表 select 47,'请假','2006-1-3'
insert into 考勤表 select 48,'迟到','2006-1-4'
insert into 考勤表 select 48,'病假','2006-1-5' create table 姓名表(sysid int,[name] nvarchar(100))
insert into 姓名表 select 47,'张三'
insert into 姓名表 select 48,'李四'--我想得到以下结果
--sysid name 迟到 请假 病假
--47 张三 2 1 0
--48 李四 1 0 1
--select * from 考勤表 select * from 姓名表select b.sysid,b.[name],sum(case when a.ktype='迟到' then 1 else 0 end) as 迟到
,sum(case when a.ktype='请假' then 1 else 0 end) as 请假
,sum(case when a.ktype='病假' then 1 else 0 end) as 病假
from 考勤表 a,姓名表 b where a.sysid=b.sysid group by b.sysid ,b.[name] order by b.sysid
sysid ktype stime
47 迟到 2006-1-1
47 迟到 2006-1-2
47 请假 2006-1-3
48 迟到 2006-1-4
48 病假 2006-1-5
姓名表
sysid name
47 张三
48 李四
49 王二我想得到以下结果
sysid name 迟到 请假 病假
47 张三 2 1 0
48 李四 1 0 1
49 王二 0 0 0
谢谢,您的语句测试过了,
引用Yang_(扬帆破浪) select a.sysid,b.name,
sum(case when ktype='迟到' then 1 else 0 end) as 迟到, --加END
sum(case when ktype='请假' then 1 else 0 end) as 请假,
sum(case when ktype='病假' then 1 else 0 end) as 病假
from 考勤表 a,姓名表 b
where a.sysid=b.sysid
group by a.sysid,b.name
这个写法效率高
create table 考勤表(sysid int,ktype char(8), stime datetime )
insert into 考勤表
select 47,'迟到','2006-1-1'
union all
select 47,'迟到','2006-1-2'
union all
select 47,'请假','2006-1-3'
union all
select 48,'迟到','2006-1-4'
union all
select 48,'病假','2006-1-5' create table 姓名表(sysid int,[name] char(10))
insert into 姓名表
select 47,'张三'
union all
select 48,'李四'
union all
select 49,'王二'
--生成sql
declare @SQL varchar(8000)
set @SQL=''
select @SQL=@SQL +'(select count(*) from 考勤表
where sysid=a.sysid and ktype='''+ ktype +''')as '+ ktype+','
from (select distinct ktype from 考勤表)T
select @SQL='select a.sysid,a.name,' + left(@SQL,len(@SQL)-1)
+' from 姓名表 a'
--执行
exec(@SQL)
-- 结果:
-- sysid name 病假 迟到 请假
-- ----------- ---------- ----------- ----------- -----------
-- 47 张三 0 2 1
-- 48 李四 1 1 0
-- 49 王二 0 0 0drop table 考勤表,姓名表
-- 如果@SQL长度超过了8000
-- 参看老大的东东
-- http://blog.csdn.net/zjcxc/archive/2003/12/29/20075.aspx
sum(case when ktype='迟到' then 1 else 0 end) as 迟到, --加END
sum(case when ktype='请假' then 1 else 0 end) as 请假,
sum(case when ktype='病假' then 1 else 0 end) as 病假
from 考勤表 a
right join 姓名表 b --注意这里改用RIGHT JOIN
on a.sysid=b.sysid
group by b.sysid,b.name ---a.sysid改为b.sysid:)