AAA(员工表)
ID GH XM
1 01 小明
2 02 技安BBB(出勤表)
ID GH RQ(日期)
1 01 2010-1-10
2 01 2010-1-11
3 01 2010-1-12
4 02 2010-1-10
5 02 2010-1-11如果我查询 10-12号的出勤表,请问如何查询出如下结果集呢?ID GH XM RQ(日期) JT(状态)
1 01 小明 2010-1-10 正常
2 01 小明 2010-1-11 正常
3 01 小明 2010-1-12 正常
4 02 技安 2010-1-10 正常
5 02 技安 2010-1-11 正常
6 02 技安 2010-1-12 缺勤(BBB表没记录)
ID GH XM
1 01 小明
2 02 技安BBB(出勤表)
ID GH RQ(日期)
1 01 2010-1-10
2 01 2010-1-11
3 01 2010-1-12
4 02 2010-1-10
5 02 2010-1-11如果我查询 10-12号的出勤表,请问如何查询出如下结果集呢?ID GH XM RQ(日期) JT(状态)
1 01 小明 2010-1-10 正常
2 01 小明 2010-1-11 正常
3 01 小明 2010-1-12 正常
4 02 技安 2010-1-10 正常
5 02 技安 2010-1-11 正常
6 02 技安 2010-1-12 缺勤(BBB表没记录)
if object_id('AA') is not null drop table AA
create table AA (ID int,GH varchar(2),XM varchar(4))
insert into AA
select 1,'01','小明' union all
select 2,'02','技安'
--> 测试数据: BBB
if object_id('BBB') is not null drop table BBB
create table BBB (ID int,GH varchar(2),RQ datetime)
insert into BBB
select 1,'01','2010-1-10' union all
select 2,'01','2010-1-11' union all
select 3,'01','2010-1-12' union all
select 4,'02','2010-1-10' union all
select 5,'02','2010-1-11'
declare @star datetime,@end datetime
set @star='2010-01-10'
set @end='2010-01-12'
select b.id,b.gh,b.xm,a.rq,jt=case isnull(c.rq,'') when '' then '缺勤' else '正常' end from
(select rq=dateadd(dd,number,@star) from master..spt_values where type='p' and number between 0 and datediff(dd,@star,@end))a
full join aa b on 1=1
left join bbb c on b.gh=c.gh and a.rq=c.rq
order by gh,a.rq--结果:
id gh xm rq jt
----------- ---- ---- ----------------------- ----
1 01 小明 2010-01-10 00:00:00.000 正常
1 01 小明 2010-01-11 00:00:00.000 正常
1 01 小明 2010-01-12 00:00:00.000 正常
2 02 技安 2010-01-10 00:00:00.000 正常
2 02 技安 2010-01-11 00:00:00.000 正常
2 02 技安 2010-01-12 00:00:00.000 缺勤
--改下ID的显示:
--> 测试数据: AA
if object_id('AA') is not null drop table AA
create table AA (ID int,GH varchar(2),XM varchar(4))
insert into AA
select 1,'01','小明' union all
select 2,'02','技安'
--> 测试数据: BBB
if object_id('BBB') is not null drop table BBB
create table BBB (ID int,GH varchar(2),RQ datetime)
insert into BBB
select 1,'01','2010-1-10' union all
select 2,'01','2010-1-11' union all
select 3,'01','2010-1-12' union all
select 4,'02','2010-1-10' union all
select 5,'02','2010-1-11'
declare @star datetime,@end datetime
set @star='2010-01-10'
set @end='2010-01-12'
select id=row_number() over(order by b.gh,a.rq),b.gh,b.xm,a.rq,jt=case isnull(c.rq,'') when '' then '缺勤' else '正常' end from
(select rq=dateadd(dd,number,@star) from master..spt_values where type='p' and number between 0 and datediff(dd,@star,@end))a
full join aa b on 1=1
left join bbb c on b.gh=c.gh and a.rq=c.rq
order by gh,a.rq--结果:id gh xm rq jt
-------------------- ---- ---- ----------------------- ----
1 01 小明 2010-01-10 00:00:00.000 正常
2 01 小明 2010-01-11 00:00:00.000 正常
3 01 小明 2010-01-12 00:00:00.000 正常
4 02 技安 2010-01-10 00:00:00.000 正常
5 02 技安 2010-01-11 00:00:00.000 正常
6 02 技安 2010-01-12 00:00:00.000 缺勤
if object_id('[AAA]') is not null drop table [AAA]
create table [AAA]([ID] int,[GH] varchar(2),[XM] varchar(4))
insert [AAA]
select 1,'01','小明' union all
select 2,'02','技安'
--> 测试数据:[BBB]
if object_id('[BBB]') is not null drop table [BBB]
create table [BBB]([ID] int,[GH] varchar(2),[RQ] datetime)
insert [BBB]
select 1,'01','2010-1-10' union all
select 2,'01','2010-1-11' union all
select 3,'01','2010-1-12' union all
select 4,'02','2010-1-10' union all
select 5,'02','2010-1-11'select t.[GH],t.[XM],t.[RQ],
case isnull(r.[GH],'') when '' then '缺勤' else '正常' end as jt
from
(
select g.[GH],g.[XM],dateadd(day,f.number,'2010-01-10') as [RQ]
from master..spt_values f,[AAA] g
where f.type='P' and dateadd(day,f.number,'2010-01-10')<='2010-1-12'
) t left join [BBB] r on
t.[RQ]=r.[RQ] and t.[GH]=r.[GH]
---------------------------
01 小明 2010-01-10 00:00:00.000 正常
01 小明 2010-01-11 00:00:00.000 正常
01 小明 2010-01-12 00:00:00.000 正常
02 技安 2010-01-10 00:00:00.000 正常
02 技安 2010-01-11 00:00:00.000 正常
02 技安 2010-01-12 00:00:00.000 缺勤
INSERT @a SELECT 1 ,'01','小明'
union all select 2 ,'02','技安'
declare @b table(ID INT, GH char(2),RQ varchar(20))
insert @b select 1 ,'01' ,'2010-1-10'
union all select 2 ,'01' ,'2010-1-11'
union all select 3 ,'01' ,'2010-1-12'
union all select 4 ,'02' ,'2010-1-10'
union all select 5 ,'02' ,'2010-1-11' SELECT cc.gh,cc.xm,cc.rq,jt=CASE WHEN bb.id IS NOT NULL THEN '正常' ELSE '缺勤' end FROM @b bb RIGHT join
(
SELECT * FROM @a a CROSS JOIN (SELECT distinct rq FROM @b WHERE rq<='2010-1-12')c
)cc
ON cc.gh=bb.gh and cc.rq=bb.rq
ORDER BY cc.id,cc.rq--result
/*
gh xm rq jt
---- -------------------- -------------------- ----
01 小明 2010-1-10 正常
01 小明 2010-1-11 正常
01 小明 2010-1-12 正常
02 技安 2010-1-10 正常
02 技安 2010-1-11 正常
02 技安 2010-1-12 缺勤(所影响的行数为 6 行)*/