sql-server-2008 - 如何从 SQL Server 的两个不同表列中获取一个不同的 ListofDate 列

Question

有两个表 Leaves, Attendanceatd 有数据。

我需要从这两个表中显示不同的 ListofDates 和 employeeid 并在 Leave 表中显示 approvalstatus='Approved' 和 startdate/enddate 两个表的日期列上的期间持续时间。

表架构: SQL FIDDLE

create table Leaves(idemp_lv int,leavedate Datetime, approvalstatus varchar(100))
    insert into Leaves values (1,'2013-04-22 00:00:00.000','Approved');
    insert into Leaves values (2,'2013-04-21 00:00:00.000','Approved'); 
    insert into Leaves values(3,'2013-04-26 00:00:00.000','Approved');
    insert into Leaves values(1,'2013-04-21 00:00:00.000','Pending'); 
    insert into Leaves values(3,'2013-04-02 00:00:00.000','Pending');
    insert into Leaves values(1,'2013-04-19 00:00:00.000','Approved');

create table Attendanceatd(idemp_at int,absentdate Datetime)
insert into Attendanceatd values(1,'2013-04-19 00:00:00.000');
insert into Attendanceatd values(3,'2013-04-02 00:00:00.000');
insert into Attendanceatd values(1,'2013-04-15 00:00:00.000');

期望的输出:

Empid ListofDate
1     2013-04-22 00:00:00.000
1     2013-04-19 00:00:00.000
1     2013-04-15 00:00:00.000
2     2013-04-21 00:00:00.000
3     2013-04-26 00:00:00.000
3     2013-04-02 00:00:00.000

目前我正在尝试这个:

select ListOfDates
from 
(
 select leavedate as ListOfDates from leaves where approvalstatus='Approved' and leavedate >='20130302' and  leavedate <'20130501'
 union
 select absentdate  as ListOfDates from Attendanceatd where  absentdate >='20130302' and  absentdate <'20130501'
)t1
group by ListOfDates 

无法获取 employeeId 和 ListOfDates

Answer 1

我认为您不需要分组依据。这给了你想要的

 select idemp_lv, leavedate as ListOfDates
 from leaves
 where approvalstatus='Approved' and leavedate >='20130302' and  leavedate <'20130501'
 union
 select idemp_at, absentdate  as ListOfDates
 from Attendanceatd
 where  absentdate >='20130302' and  absentdate <'20130501'

UNION applies DISTINCT自动(如果使用 UNION ALL，请注意区别)

Updated SQLFiddle