作者Antzzz (减肥中请勿喂食)
看板Database
标题Re: [SQL ]由Table中捞取资料後Join到另外一个Table
时间Sat Mar 22 15:25:48 2008
※ 引述《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)