求一SQL語句的寫法

Question

有一表如下結構tab1：
id(自動增長型),empno,empdate(異動日期)
另有一tab2：
有其中一些字段：id(自動增長型),empno,empname,indate(入職日期),outdate(離職日期)想得出如下結果：
對empno分組且上條的empdate作為起始時間，下一條的記錄作為終止時間，且入職日期作為第一條(對單一empno而言)記錄的起始時間，離職日期作為最後一條記錄的終止時間
類似：
empno,empname,sdate,edate請各位高手幫手！

Answer 1

tab1中的記錄：
1 321 2007-04-17 00:00:00.000
2 192 2007-04-17 00:00:00.000
3 193 2007-04-17 00:00:00.000
17 240 2007-04-18 00:00:00.000
18 192 2007-04-18 00:00:00.000
19 193 2007-04-18 00:00:00.000
21 195 2007-04-18 00:00:00.000
5 195 2007-04-17 00:00:00.000
753 192 2007-04-23 00:00:00.000
755 193 2007-04-23 00:00:00.000
759 195 2007-04-23 00:00:00.000tab2中的記錄：
1 192 2004-06-11 00:00:00.000 NULL
10 193 2004-07-12 00:00:00.000 NULL
15 195 2004-09-02 00:00:00.000 NULL
66 240 2006-05-26 00:00:00.000 NULL
156 321 2006-06-05 00:00:00.000 2006-12-29 00:00:00.000

Answer 2

tab2的記錄忘了加姓名了
1 192 刘香生 2004-06-11 00:00:00.000 NULL
10 193 黄桂秀 2004-07-12 00:00:00.000 NULL
15 195 钟小玲 2004-09-02 00:00:00.000 NULL
66 240 李惠清 2006-05-26 00:00:00.000 NULL
156 321 龙小琼 2006-06-05 00:00:00.000 2006-12-29 00:00:00.000

Answer 3

select a.*,b.empname into #b from 
(select  empno,empdate from tab1 union all
select  empno,indate from tab2 union all 
select  empno,outdate from tab2 )a 
inner join
(select  distinct empno,empname from tab2) b on a.empno=b.empnoselect *,(select min(empdate) from #b where empno=a.empno and empdate>a.empdate ) from #b a我想这样应可以~

Answer 4

在最后加个order by empno empdate

Answer 5

結果：
empno empname sdate edate
192 刘香生 2004-06-11 2007-04-17
192 刘香生 2007-04-17 2007-04-18
192 刘香生 2007-04-18 2007-04-23
192 刘香生 2007-04-23
193 黄桂秀 2004-07-12 2007-04-17
193 黄桂秀 2007-04-17 2007-04-18
193 黄桂秀 2007-04-18 2007-04-23
193 黄桂秀 2007-04-23
195 钟小玲 2004-09-02 2007-04-17
195 钟小玲 2007-04-17 2007-04-18
195 钟小玲 2007-04-18 2007-04-23
195 钟小玲 2007-04-23
240 李惠清 2006-05-26 2007-04-18
240 李惠清 2007-04-18
321 龙小琼 2006-06-05 2007-04-17
321 龙小琼 2007-04-17 2007-12-29由於測試記錄的問題，請把
156 321 龙小琼 2006-06-05 00:00:00.000 2006-12-29 00:00:00.000
改成：156 321 龙小琼 2006-06-05 00:00:00.000 2007-12-29 00:00:00.000
要不會出現邏輯上的錯誤
謝謝了

Answer 6

先謝謝ljsql(第 1 行: '脑子' 附近有语法错误。) 我試試

Answer 7

create table tab1 (id int ,empno int ,empdate datetime)
insert into  tab1
select 1 ,321 ,'2007-04-17 00:00:00.000' union all
select 2 ,192 ,'2007-04-17 00:00:00.000' union all
select 3 ,193 ,'2007-04-17 00:00:00.000'union all
select 17 ,240 ,'2007-04-18 00:00:00.000'union all
select 18 ,192 ,'2007-04-18 00:00:00.000'union all
select 19 ,193 ,'2007-04-18 00:00:00.000'union all
select 21 ,195 ,'2007-04-18 00:00:00.000'union all
select 5 ,195 ,'2007-04-17 00:00:00.000'union all
select 753 ,192 ,'2007-04-23 00:00:00.000'union all
select 755 ,193 ,'2007-04-23 00:00:00.000'union all
select 759 ,195 ,'2007-04-23 00:00:00.000'select * from tab2
create table tab2 (id int ,empno int ,empname varchar(30),indate datetime,outdate datetime)
insert into  tab2
select 1 ,192 ,'刘香生', '2004-06-11 00:00:00.000', NULL union all
select 10 ,193 ,'黄桂秀', '2004-07-12 00:00:00.000', NULL union all
select 15 ,195 ,'钟小玲', '2004-09-02 00:00:00.000', NULL union all
select 66 ,240 ,'李惠清', '2006-05-26 00:00:00.000', NULL union all
select 156 ,321 ,'龙小琼', '2006-06-05 00:00:00.000','2006-12-29 00:00:00.000'
select a.*,b.empname into #b from 
(select  empno,empdate from tab1 union all
select  empno,indate from tab2 union all 
select  empno,outdate from tab2 )a 
inner join
(select  distinct empno,empname from tab2) b on a.empno=b.empnoselect *,(select min(empdate) from #b where empno=a.empno and empdate>a.empdate ) from #b a  where empdate  is not null order by empno, empdate
----------------------------------------------------
empno empdate    empname  a                                                      
----------- ------------------------------------------------------  
192         2004-06-11 00:00:00.000                                刘香生                            2007-04-17 00:00:00.000
192         2007-04-17 00:00:00.000                                刘香生                            2007-04-18 00:00:00.000
192         2007-04-18 00:00:00.000                                刘香生                            2007-04-23 00:00:00.000
192         2007-04-23 00:00:00.000                                刘香生                            NULL
193         2004-07-12 00:00:00.000                                黄桂秀                            2007-04-17 00:00:00.000
193         2007-04-17 00:00:00.000                                黄桂秀                            2007-04-18 00:00:00.000
193         2007-04-18 00:00:00.000                                黄桂秀                            2007-04-23 00:00:00.000
193         2007-04-23 00:00:00.000                                黄桂秀                            NULL
195         2004-09-02 00:00:00.000                                钟小玲                            2007-04-17 00:00:00.000
195         2007-04-17 00:00:00.000                                钟小玲                            2007-04-18 00:00:00.000
195         2007-04-18 00:00:00.000                                钟小玲                            2007-04-23 00:00:00.000
195         2007-04-23 00:00:00.000                                钟小玲                            NULL
240         2006-05-26 00:00:00.000                                李惠清                            2007-04-18 00:00:00.000
240         2007-04-18 00:00:00.000                                李惠清                            NULL
321         2006-06-05 00:00:00.000                                龙小琼                            2006-12-29 00:00:00.000
321         2006-12-29 00:00:00.000                                龙小琼                            2007-04-17 00:00:00.000
321         2007-04-17 00:00:00.000                                龙小琼                            NULL（所影响的行数为 17 行）

Answer 8

Select
A.empno,
B.empname,
A.empdate As sdate,
IsNull(Min(C.empdate), B.outdate) As edate
From
tab1 A
Inner Join
tab2 B
On A.empno = B.empno
Left Join
tab1 C
On A.empno = C.empno And A.empdate < C.empdate
Group By A.empno, B.empname, A.empdate, B.outdate
Union
Select
A.empno,
A.empname,
A.indate As sdate,
Min(B.empdate) As edate
From
tab2 A
Inner Join
tab1 B
On A.empno = B.empno
Group By
A.empno,
A.empname,
A.indate

Answer 9

Create Table tab1
(id Int,
 empno Int,
 empdate DateTime)Create Table tab2
(id Int,
 empno Int,
 empname Nvarchar(10),
 indate DateTime,
 outdate DateTime)
Insert tab1 Select 1, 321, '2007-04-17 00:00:00.000'
Union All Select 2, 192, '2007-04-17 00:00:00.000'
Union All Select 3, 193, '2007-04-17 00:00:00.000'
Union All Select 17, 240, '2007-04-18 00:00:00.000'
Union All Select 18, 192, '2007-04-18 00:00:00.000'
Union All Select 19, 193, '2007-04-18 00:00:00.000'
Union All Select 21, 195, '2007-04-18 00:00:00.000'
Union All Select 5, 195, '2007-04-17 00:00:00.000'
Union All Select 753, 192, '2007-04-23 00:00:00.000'
Union All Select 755, 193, '2007-04-23 00:00:00.000'
Union All Select 759, 195, '2007-04-23 00:00:00.000'Insert tab2 Select 1, 192, N'刘香生 ','2004-06-11 00:00:00.000', NULL
Union All Select 10, 193, N'黄桂秀 ','2004-07-12 00:00:00.000', NULL
Union All Select 15, 195, N'钟小玲 ','2004-09-02 00:00:00.000', NULL
Union All Select 66, 240, N'李惠清', '2006-05-26 00:00:00.000', NULL
Union All Select 156, 321, N'龙小琼', '2006-06-05 00:00:00.000', '2007-12-29 00:00:00.000'
GO
Select
A.empno,
B.empname,
A.empdate As sdate,
IsNull(Min(C.empdate), B.outdate) As edate
From
tab1 A
Inner Join
tab2 B
On A.empno = B.empno
Left Join
tab1 C
On A.empno = C.empno And A.empdate < C.empdate
Group By A.empno, B.empname, A.empdate, B.outdate
Union
Select
A.empno,
A.empname,
A.indate As sdate,
Min(B.empdate) As edate
From
tab2 A
Inner Join
tab1 B
On A.empno = B.empno
Group By
A.empno,
A.empname,
A.indate
GO
Drop Table tab1, tab2
--Result
/*
empno empname sdate edate
192 刘香生  2004-06-11 00:00:00.000 2007-04-17 00:00:00.000
192 刘香生  2007-04-17 00:00:00.000 2007-04-18 00:00:00.000
192 刘香生  2007-04-18 00:00:00.000 2007-04-23 00:00:00.000
192 刘香生  2007-04-23 00:00:00.000 NULL
193 黄桂秀  2004-07-12 00:00:00.000 2007-04-17 00:00:00.000
193 黄桂秀  2007-04-17 00:00:00.000 2007-04-18 00:00:00.000
193 黄桂秀  2007-04-18 00:00:00.000 2007-04-23 00:00:00.000
193 黄桂秀  2007-04-23 00:00:00.000 NULL
195 钟小玲  2004-09-02 00:00:00.000 2007-04-17 00:00:00.000
195 钟小玲  2007-04-17 00:00:00.000 2007-04-18 00:00:00.000
195 钟小玲  2007-04-18 00:00:00.000 2007-04-23 00:00:00.000
195 钟小玲  2007-04-23 00:00:00.000 NULL
240 李惠清 2006-05-26 00:00:00.000 2007-04-18 00:00:00.000
240 李惠清 2007-04-18 00:00:00.000 NULL
321 龙小琼 2006-06-05 00:00:00.000 2007-04-17 00:00:00.000
321 龙小琼 2007-04-17 00:00:00.000 2007-12-29 00:00:00.000
*/

Answer 10

精簡下Select
A.empno,
B.empname,
A.empdate As sdate,
IsNull(Min(C.empdate), B.outdate) As edate
From
(Select empno, empdate From tab1
Union All 
Select empno, indate As empdate From tab2) A
Inner Join
tab2 B
On A.empno = B.empno
Left Join
tab1 C
On A.empno = C.empno And A.empdate < C.empdate
Group By A.empno, B.empname, A.empdate, B.outdate

Answer 11

Create Table tab1
(id Int,
 empno Int,
 empdate DateTime)Create Table tab2
(id Int,
 empno Int,
 empname Nvarchar(10),
 indate DateTime,
 outdate DateTime)
Insert tab1 Select 1, 321, '2007-04-17 00:00:00.000'
Union All Select 2, 192, '2007-04-17 00:00:00.000'
Union All Select 3, 193, '2007-04-17 00:00:00.000'
Union All Select 17, 240, '2007-04-18 00:00:00.000'
Union All Select 18, 192, '2007-04-18 00:00:00.000'
Union All Select 19, 193, '2007-04-18 00:00:00.000'
Union All Select 21, 195, '2007-04-18 00:00:00.000'
Union All Select 5, 195, '2007-04-17 00:00:00.000'
Union All Select 753, 192, '2007-04-23 00:00:00.000'
Union All Select 755, 193, '2007-04-23 00:00:00.000'
Union All Select 759, 195, '2007-04-23 00:00:00.000'Insert tab2 Select 1, 192, N'刘香生 ','2004-06-11 00:00:00.000', NULL
Union All Select 10, 193, N'黄桂秀 ','2004-07-12 00:00:00.000', NULL
Union All Select 15, 195, N'钟小玲 ','2004-09-02 00:00:00.000', NULL
Union All Select 66, 240, N'李惠清', '2006-05-26 00:00:00.000', NULL
Union All Select 156, 321, N'龙小琼', '2006-06-05 00:00:00.000', '2007-12-29 00:00:00.000'
GO
Select
A.empno,
B.empname,
A.empdate As sdate,
IsNull(Min(C.empdate), B.outdate) As edate
From
(Select empno, empdate From tab1
Union All 
Select empno, indate As empdate From tab2) A
Inner Join
tab2 B
On A.empno = B.empno
Left Join
tab1 C
On A.empno = C.empno And A.empdate < C.empdate
Group By A.empno, B.empname, A.empdate, B.outdate
GO
Drop Table tab1, tab2
--Result
/*
empno empname sdate edate
192 刘香生  2004-06-11 00:00:00.000 2007-04-17 00:00:00.000
192 刘香生  2007-04-17 00:00:00.000 2007-04-18 00:00:00.000
192 刘香生  2007-04-18 00:00:00.000 2007-04-23 00:00:00.000
192 刘香生  2007-04-23 00:00:00.000 NULL
193 黄桂秀  2004-07-12 00:00:00.000 2007-04-17 00:00:00.000
193 黄桂秀  2007-04-17 00:00:00.000 2007-04-18 00:00:00.000
193 黄桂秀  2007-04-18 00:00:00.000 2007-04-23 00:00:00.000
193 黄桂秀  2007-04-23 00:00:00.000 NULL
195 钟小玲  2004-09-02 00:00:00.000 2007-04-17 00:00:00.000
195 钟小玲  2007-04-17 00:00:00.000 2007-04-18 00:00:00.000
195 钟小玲  2007-04-18 00:00:00.000 2007-04-23 00:00:00.000
195 钟小玲  2007-04-23 00:00:00.000 NULL
240 李惠清 2006-05-26 00:00:00.000 2007-04-18 00:00:00.000
240 李惠清 2007-04-18 00:00:00.000 NULL
321 龙小琼 2006-06-05 00:00:00.000 2007-04-17 00:00:00.000
321 龙小琼 2007-04-17 00:00:00.000 2007-12-29 00:00:00.000
*/

Answer 12

To:ljsql(第 1 行: '脑子' 附近有语法错误。) 
有點問題，
首先你得把156 321 龙小琼 2006-06-05 00:00:00.000 2006-12-29 00:00:00.000
改成：156 321 龙小琼 2006-06-05 00:00:00.000 2007-12-29 00:00:00.000
以便測試321 2006-06-05 00:00:00.000 龙小琼 2007-04-17 00:00:00.000
321 2007-04-17 00:00:00.000 龙小琼 2007-12-29 00:00:00.000
321 2007-12-29 00:00:00.000 龙小琼 NULL
對龙小琼來言如果最后這條記錄沒有的話那就正確了，2007-12-29是其離職的日期

Answer 13

還是paoluo(一天到晚游泳的鱼) 的更准確些，並且沒有利用臨時表
再研究一下如果確定沒有問題了就立馬結貼
謝謝幾位了！