如何使用一系列日期而不使用过程来JOIN表 [英] How to JOIN tables using a range of dates without having procedures
问题描述
我有两个表，让我们称它们为表A& B： -
表A如下所示：
Num1 | A.Num2 |日期
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.日期
12345 | 38170 |新| 28/05/2013
12345 | 38170 |关闭| 31/05/2013
12345 | 38170 |重新打开| 04/06/2013
我需要一个这样的表格作为输出 - 表A +表B中的状态
Num1 | Num2 |状态|日期
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
此外，最好通过简单的SQL语句，而不是过程。 >
提前感谢。请对任何说明进行评论
Indru
UPDATE
如果表B中的两行状态已更改，但在同一日期，我在加入中获得的值不一致。
举个例子：
我有两个表，比如A&他们是相当大的表，他们记录一定的信息。表A通过每几天扫描一次数据但缺少状态列
如果有更改，则表B将获取一个带有状态列的新条目。
我需要匹配这两个并为表A中对应于B的每个条目派生一个状态。
表B
Id | Num1 | Num2 | CreatedOn |状态
100 | 12345 | 38170 | 28/05/2013 |新
150 | 12345 | 38170 | 28/05/2013 |已结束
200 | 12345 | 38170 | 31/05/2013 |重新打开
250 | 12345 | 38170 | 04/06/2013 |已关闭
表A
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 |状态
55 | 11552 | 38170 | 28/05/2013 |新
99 | 11552 | 38170 | 30/05/2013 |已结束
145 | 11552 | 38170 | 31/05/2013 |重新打开
192 | 11552 | 38170 | 31/05/2013 |重新打开
223 | 11552 | 38170 | 04/06/2013 |已关闭
我得到的是:(使用Jeffrey Kamp的回复）
Id | Num1 | Num2 | CreatedOn |状态
55 | 11552 | 38170 | 28/05/2013 |已结束
99 | 11552 | 38170 | 30/05/2013 |已结束
145 | 11552 | 38170 | 31/05/2013 |重新打开
192 | 11552 | 38170 | 31/05/2013 |重新打开
223 | 11552 | 38170 | 04/06/2013 |已关闭
而且：（使用回复2 - Mark Bannister）
Id | Num1 | Num2 | CreatedOn |状态
55 | 11552 | 38170 28/05/2013 |新
99 | 11552 | 38170 | 30/05/2013 |新
145 | 11552 | 38170 | 31/05/2013 |重新打开
192 | 11552 | 38170 | 31/05/2013 |重新打开
223 | 11552 | 38170 | 04/06/2013 |已关闭
显然，记录Id-55可以是新建或关闭，因为在同一个日期（28/05/13）在表B中。但逻辑是，它从新 - >关闭 - >重新打开
所以有什么办法吗？
初学者简单的：
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;
这可能是一个更好的方法，但可能会更快。
I have two tables, lets call them table A & B :-
Table A looks like this:
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
Table 2 (B) looks like this:
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
I need an table like this as output - It should basically contain all rows from table A + status from table 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
Also, preferably through a simple SQL statement, rather than procedures.
Thanks in advance. Kindly comment for any clarifications
Indru
UPDATE
When two rows in Table B have the status changed but on the same date, I am getting inconsistent values in the join.
Take an example:
I have two tables, say A & B. They are pretty large tables and they document a certain information. Table A documents by scanning the data every few days but lacks a status column If there is a change, then table B gets a new entry with status column. I need to match these two and derive a status for each entry in table A corresponding to B.
Table B
Id | 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
Table A
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
Desired Output
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
Instead, I am getting: (using Jeffrey Kamp's reply)
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
And this: (using reply 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
Clearly the record Id-55 can be either New or Closed since there are two entries on the same day (28/05/13) in table B. But logic is that it goes from New -> Closed -> Reopened So is there any way of doing this?
Something simplistic for starters:
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;
There's probably a better method that may work faster though.
这篇关于如何使用一系列日期而不使用过程来JOIN表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
