我有两个表,我们称它们为表A &B: -
表A如下所示:
A.Num1 | A.Num2 | A.Date
12345 | 38170 | 28/05/2013
12345 | 38170 | 29/05/2013
12345 | 38170 | 31/05/2013
12345 | 38170 | 01/06/2013
12345 | 38170 | 03/06/2013
12345 | 38170 | 04/06/2013
12345 | 38170 | 04/06/2013
12345 | 38170 | 07/06/2013
表2 (B)如下所示:
B.Num1 | B.Num2 | B.Status | B.Date
12345 | 38170 | New | 28/05/2013
12345 | 38170 | Closed | 31/05/2013
12345 | 38170 | Reopened | 04/06/2013
我需要一个这样的表作为输出-它基本上应该包含表A中的所有行+表B中的状态
Num1 | Num2 | Status | Date
12345 | 38170 | New | 28/05/2013
12345 | 38170 | New | 29/05/2013
12345 | 38170 | Closed | 31/05/2013
12345 | 38170 | Closed | 01/06/2013
12345 | 38170 | Closed | 03/06/2013
12345 | 38170 | Reopened | 04/06/2013
12345 | 38170 | Reopened | 04/06/2013
12345 | 38170 | Reopened | 07/06/2013
而且,最好通过简单的SQL语句,而不是过程。
提前感谢。如有任何澄清,请评论
Indru
更新当表B中的两行改变了状态,但在相同的日期,我在连接中得到不一致的值。
举个例子:
我有两张表,比如A &b:它们是相当大的表格,它们记录了一定的信息。表A每隔几天扫描一次数据,但缺少状态列如果发生了更改,那么表B将获得一个带有status列的新条目。我需要匹配这两个,并为表a中对应于b的每个条目派生一个状态。
表BId | Num1 | Num2 | CreatedOn | Status
100 | 12345 | 38170 | 28/05/2013 | New
150 | 12345 | 38170 | 28/05/2013 | Closed
200 | 12345 | 38170 | 31/05/2013 | Reopened
250 | 12345 | 38170 | 04/06/2013 | Closed
表Id | Num1 | Num2 | CreatedOn
55 | 12345 | 38170 | 28/05/2013
99 | 12345 | 38170 | 30/05/2013
145 | 12345 | 38170 | 31/05/2013
192 | 12345 | 38170 | 31/05/2013
223 | 12345 | 38170 | 04/06/2013
期望输出值
Id | Num1 | Num2 | CreatedOn | Status
55 | 11552 | 38170 | 28/05/2013 | New
99 | 11552 | 38170 | 30/05/2013 | Closed
145 | 11552 | 38170 | 31/05/2013 | Reopened
192 | 11552 | 38170 | 31/05/2013 | Reopened
223 | 11552 | 38170 | 04/06/2013 | Closed
相反,我得到:(使用杰弗里·坎普的回复)
Id | Num1 | Num2 | CreatedOn | Status
55 | 11552 | 38170 | 28/05/2013 | Closed
99 | 11552 | 38170 | 30/05/2013 | Closed
145 | 11552 | 38170 | 31/05/2013 | Reopened
192 | 11552 | 38170 | 31/05/2013 | Reopened
223 | 11552 | 38170 | 04/06/2013 | Closed
和这个:(使用回复2 - Mark Bannister)
Id | Num1 | Num2 | CreatedOn | Status
55 | 11552 | 38170 | 28/05/2013 | New
99 | 11552 | 38170 | 30/05/2013 | New
145 | 11552 | 38170 | 31/05/2013 | Reopened
192 | 11552 | 38170 | 31/05/2013 | Reopened
223 | 11552 | 38170 | 04/06/2013 | Closed
显然,记录Id-55既可以是New也可以是Closed,因为在表b中同一天(28/05/13)有两个条目。但逻辑是,它从New -> Closed ->重开有什么办法可以做到吗?
对于初学者来说很简单:
SELECT A.Num1
,A.Num2
,NVL(
(SELECT DISTINCT
FIRST_VALUE(B.Status)
OVER (ORDER BY B.Date DESC)
FROM B
WHERE B.Num1 = A.Num1
AND B.Num2 = A.Num2
AND B.Date <= A.Date
),'New') AS Status
,A.Date
FROM A;
尝试:
select a.Num1, a.Num2, a."Date", b.Status
from TableA a
join (select TableB.*,
lead("Date",1) over (partition by Num1, Num2
order by "Date") NextDate
from TableB) b
on a.Num1 = b.Num1 and
a.Num2 = b.Num2 and
a."Date" >= b."Date" and
a."Date" < coalesce(b.NextDate,a."Date"+1)
SQLFiddle。