※ 引述《gargamel (Nerding)》之銘言：
: 推文等好久
: 我直接改
: 你table schema一定要改...我改的只是參考
: table1 加上一組key來當pk
: table2 加上t1_key當做fk
: 直接table1.key = table2.t1_key 就出來了
: Table1 Table2
: ------------------------------------ -----------------------
: ID key 部門 升遷日 t1_key ID 加班日
: ------------------------------------ -----------------------
: AAA 1 DO1 2008-02-10 1 AAA 2008-02-12
: AAA 2 D02 2008-02-20 1 AAA 2008-02-15
: AAA 3 D03 2008-03-10 2 AAA 2008-02-22
: BBB 4 D02 2008-02-12 2 AAA 2008-02-25
: BBB 5 D03 2008-03-08 3 AAA 2008-03-12
: 3 AAA 2008-03-18
: 4 BBB 2008-02-15
: TABle3 (希望的Results) 5 BBB 2008-03-10
: -----------------------------------
: pk ID 加班日 部門
: -----------------------------------
: 1 AAA 2008-02-12 D01
: 1 AAA 2008-02-15 D01
: 2 AAA 2008-02-22 D02
: 2 AAA 2008-02-25 D02
: 3 AAA 2008-03-12 D03
: 3 AAA 2008-03-18 D03
: 4 BBB 2008-02-15 D02
: 5 BBB 2008-03-10 D03
: ------------------------------------
我的作法是把Table1的離開部門日期湊出來
(這是最麻煩的地方，因為資料庫沒有順序觀念)
再去Join Table2：
----------------------------------------------------------
DECLARE @table1 TABLE (ID nvarchar(10)
, Dept nvarchar(10)
, BeginDate datetime)
INSERT INTO @table1 (ID, Dept, BeginDAte) VALUES ('AAA', 'D01', '2008-02-10')
INSERT INTO @table1 (ID, Dept, BeginDAte) VALUES ('AAA', 'D02', '2008-02-20')
INSERT INTO @table1 (ID, Dept, BeginDAte) VALUES ('AAA', 'D03', '2008-03-10')
INSERT INTO @table1 (ID, Dept, BeginDAte) VALUES ('BBB', 'D02', '2008-02-12')
INSERT INTO @table1 (ID, Dept, BeginDAte) VALUES ('BBB', 'D03', '2008-03-08')
DECLARE @table2 TABLE (ID nvarchar(10)
, PlusDate datetime)
INSERT INTO @table2 (ID, PlusDate) VALUES ('AAA', '2008-02-12')
INSERT INTO @table2 (ID, PlusDate) VALUES ('AAA', '2008-02-15')
INSERT INTO @table2 (ID, PlusDate) VALUES ('AAA', '2008-02-22')
INSERT INTO @table2 (ID, PlusDate) VALUES ('AAA', '2008-02-25')
INSERT INTO @table2 (ID, PlusDate) VALUES ('AAA', '2008-03-12')
INSERT INTO @table2 (ID, PlusDate) VALUES ('AAA', '2008-03-18')
INSERT INTO @table2 (ID, PlusDate) VALUES ('BBB', '2008-02-15')
INSERT INTO @table2 (ID, PlusDate) VALUES ('BBB', '2008-03-10')
select A.ID, A.PlusDate, B.Dept
from @table2 A
left join
(
select T1.ID, T1.Dept, T1.BeginDate, T2.BeginDate as EndDate
from @table1 T1
left join @table1 T2
on T2.ID = T2.ID
and T2.BeginDate = (select min(T3.BeginDAte)
from @table1 T3
where T3.ID = T1.ID
and T3.BeginDate > T1.BeginDate)
) B on B.ID = A.ID and A.PlusDate >= B.BeginDate
and (B.EndDate is null or A.PlusDate < B.EndDate)
-----------------------------------------------------
如前面所講的，如果我可以決定Table Schema的話
我才不要開將Table開成這樣=_=麻煩死了
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 211.20.100.62
※ 編輯: Antzzz 來自: 211.20.100.62 (03/22 15:26)