teratail
回答率
85.35%
teratail header banner
teratail header banner
質問するログイン新規登録
トップに関する質問Oracle SQLで、期間が重なるレコード同士をマージしてSELECTしたい。

編集履歴

質問編集履歴

3

体裁調整のため

2020/05/26 02:36

投稿

abcmart
abcmart

スコア7

test CHANGED
File without changes
test CHANGED
@@ -36,25 +36,25 @@
36
36
 
37
37
  INSERT ALL
38
38
 
39
- INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D1',TO_DATE('2020/05/01 09:00','YYYY-MM-DD HH24:MI:SS'), TO_DATE('2020/05/01 12:00','YYYY-MM-DD HH24:MI:SS'))
39
+ INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D1',TO_DATE('2020/05/01 09:00','YYYY/MM/DD HH24:MI'), TO_DATE('2020/05/01 12:00','YYYY/MM/DD HH24:MI'))
40
40
 
41
- INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D1',TO_DATE('2020/05/01 11:00','YYYY-MM-DD HH24:MI:SS'), TO_DATE('2020/05/02 10:00','YYYY-MM-DD HH24:MI:SS'))
41
+ INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D1',TO_DATE('2020/05/01 11:00','YYYY/MM/DD HH24:MI'), TO_DATE('2020/05/02 10:00','YYYY/MM/DD HH24:MI'))
42
42
 
43
- INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D1',TO_DATE('2020/04/30 07:00','YYYY-MM-DD HH24:MI:SS'), TO_DATE('2020/05/03 11:00','YYYY-MM-DD HH24:MI:SS'))
43
+ INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D1',TO_DATE('2020/04/30 07:00','YYYY/MM/DD HH24:MI'), TO_DATE('2020/05/03 11:00','YYYY/MM/DD HH24:MI'))
44
44
 
45
- INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D1',TO_DATE('2020/05/03 10:00','YYYY-MM-DD HH24:MI:SS'), TO_DATE('2020/05/04 11:00','YYYY-MM-DD HH24:MI:SS'))
45
+ INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D1',TO_DATE('2020/05/03 10:00','YYYY/MM/DD HH24:MI'), TO_DATE('2020/05/04 11:00','YYYY/MM/DD HH24:MI'))
46
46
 
47
- INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D1',TO_DATE('2020/05/04 18:00','YYYY-MM-DD HH24:MI:SS'), TO_DATE('2020/05/05 11:00','YYYY-MM-DD HH24:MI:SS'))
47
+ INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D1',TO_DATE('2020/05/04 18:00','YYYY/MM/DD HH24:MI'), TO_DATE('2020/05/05 11:00','YYYY/MM/DD HH24:MI'))
48
48
 
49
- INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D2',TO_DATE('2020/05/01 09:00','YYYY-MM-DD HH24:MI:SS'), TO_DATE('2020/05/01 12:00','YYYY-MM-DD HH24:MI:SS'))
49
+ INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D2',TO_DATE('2020/05/01 09:00','YYYY/MM/DD HH24:MI'), TO_DATE('2020/05/01 12:00','YYYY/MM/DD HH24:MI'))
50
50
 
51
- INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D2',TO_DATE('2020/05/01 12:00','YYYY-MM-DD HH24:MI:SS'), TO_DATE('2020/05/02 10:00','YYYY-MM-DD HH24:MI:SS'))
51
+ INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D2',TO_DATE('2020/05/01 12:00','YYYY/MM/DD HH24:MI'), TO_DATE('2020/05/02 10:00','YYYY/MM/DD HH24:MI'))
52
52
 
53
- INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D2',TO_DATE('2020/05/03 12:00','YYYY-MM-DD HH24:MI:SS'), TO_DATE('2020/05/04 18:00','YYYY-MM-DD HH24:MI:SS'))
53
+ INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D2',TO_DATE('2020/05/03 12:00','YYYY/MM/DD HH24:MI'), TO_DATE('2020/05/04 18:00','YYYY/MM/DD HH24:MI'))
54
54
 
55
- INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D2',TO_DATE('2020/05/05 10:00','YYYY-MM-DD HH24:MI:SS'), TO_DATE('2020/05/05 15:00','YYYY-MM-DD HH24:MI:SS'))
55
+ INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D2',TO_DATE('2020/05/05 10:00','YYYY/MM/DD HH24:MI'), TO_DATE('2020/05/05 15:00','YYYY/MM/DD HH24:MI'))
56
56
 
57
- INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D2',TO_DATE('2020/05/05 14:00','YYYY-MM-DD HH24:MI:SS'), TO_DATE('2020/05/05 18:00','YYYY-MM-DD HH24:MI:SS'))
57
+ INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D2',TO_DATE('2020/05/05 14:00','YYYY/MM/DD HH24:MI'), TO_DATE('2020/05/05 18:00','YYYY/MM/DD HH24:MI'))
58
58
 
59
59
  select * from dual;
60
60
 

2

コード追記のため。

2020/05/26 02:36

投稿

abcmart
abcmart

スコア7

test CHANGED
File without changes
test CHANGED
@@ -13,6 +13,56 @@
13
13
  列1,列2のセットが同じ値のものとします。
14
14
 
15
15
  以下の例ですと、(C1,D1)と(C1,D2)のペア単位でマージとなります。
16
+
17
+
18
+
19
+ ```CREATEとINSERTはこちらです。
20
+
21
+ CREATE TABLE KIKAN_MERGE_TB
22
+
23
+ (
24
+
25
+ C1 VARCHAR2(10),
26
+
27
+ C2 VARCHAR2(10),
28
+
29
+ C3 DATE,
30
+
31
+ C4 DATE
32
+
33
+ )
34
+
35
+
36
+
37
+ INSERT ALL
38
+
39
+ INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D1',TO_DATE('2020/05/01 09:00','YYYY-MM-DD HH24:MI:SS'), TO_DATE('2020/05/01 12:00','YYYY-MM-DD HH24:MI:SS'))
40
+
41
+ INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D1',TO_DATE('2020/05/01 11:00','YYYY-MM-DD HH24:MI:SS'), TO_DATE('2020/05/02 10:00','YYYY-MM-DD HH24:MI:SS'))
42
+
43
+ INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D1',TO_DATE('2020/04/30 07:00','YYYY-MM-DD HH24:MI:SS'), TO_DATE('2020/05/03 11:00','YYYY-MM-DD HH24:MI:SS'))
44
+
45
+ INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D1',TO_DATE('2020/05/03 10:00','YYYY-MM-DD HH24:MI:SS'), TO_DATE('2020/05/04 11:00','YYYY-MM-DD HH24:MI:SS'))
46
+
47
+ INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D1',TO_DATE('2020/05/04 18:00','YYYY-MM-DD HH24:MI:SS'), TO_DATE('2020/05/05 11:00','YYYY-MM-DD HH24:MI:SS'))
48
+
49
+ INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D2',TO_DATE('2020/05/01 09:00','YYYY-MM-DD HH24:MI:SS'), TO_DATE('2020/05/01 12:00','YYYY-MM-DD HH24:MI:SS'))
50
+
51
+ INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D2',TO_DATE('2020/05/01 12:00','YYYY-MM-DD HH24:MI:SS'), TO_DATE('2020/05/02 10:00','YYYY-MM-DD HH24:MI:SS'))
52
+
53
+ INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D2',TO_DATE('2020/05/03 12:00','YYYY-MM-DD HH24:MI:SS'), TO_DATE('2020/05/04 18:00','YYYY-MM-DD HH24:MI:SS'))
54
+
55
+ INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D2',TO_DATE('2020/05/05 10:00','YYYY-MM-DD HH24:MI:SS'), TO_DATE('2020/05/05 15:00','YYYY-MM-DD HH24:MI:SS'))
56
+
57
+ INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D2',TO_DATE('2020/05/05 14:00','YYYY-MM-DD HH24:MI:SS'), TO_DATE('2020/05/05 18:00','YYYY-MM-DD HH24:MI:SS'))
58
+
59
+ select * from dual;
60
+
61
+ ```
62
+
63
+
64
+
65
+
16
66
 
17
67
 
18
68
 

1

体裁調整

2020/05/22 01:06

投稿

abcmart
abcmart

スコア7

test CHANGED
File without changes
test CHANGED
@@ -16,7 +16,7 @@
16
16
 
17
17
 
18
18
 
19
- 列1 列2 列3 列4
19
+ 列1 列2 　列3 　　　　列4
20
20
 
21
21
  C1 D1 2020/05/01 09:00 2020/05/01 12:00
22
22
 
@@ -44,7 +44,7 @@
44
44
 
45
45
 
46
46
 
47
- 列1 列2 列3 列4
47
+ 列1 列2 　 列3 　　　 列4
48
48
 
49
49
  C1 D1 2020/04/30 09:00 2020/05/04 11:00
50
50