下面通过一个ABAP程序，具体说明一下INNER JOIN,LEFT OUTER JOIN,RIGHT OUTER JOIN差异。
有两个数据库表，表中记录如下：
Inner join结果：
Left outer join结果如下：
right outer join结果如下：
完整代码如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 |
REPORT ZTEST_JOIN. CLASS demo DEFINITION. PUBLIC SECTION. CLASS-METHODS: class_constructor, main. PRIVATE SECTION. TYPES: wa1 TYPE demo_join1, wa2 TYPE demo_join2. TYPES BEGIN OF wa. INCLUDE TYPE wa1 AS wa1 RENAMING WITH SUFFIX 1. INCLUDE TYPE wa2 AS wa2 RENAMING WITH SUFFIX 2. TYPES END OF wa. CLASS-DATA out TYPE REF TO if_demo_output. ENDCLASS. CLASS demo IMPLEMENTATION. METHOD main. DATA itab TYPE TABLE OF wa. out->begin_section( `Inner Joins` )->begin_section( `demo1 INNER JOIN demo2` ). SELECT t1~a AS a1, t1~b AS b1, t1~c AS c1, t1~d AS d1, t2~d AS d2, t2~e AS e2, t2~f AS f2, t2~g AS g2, t2~h AS h2 FROM demo_join1 AS t1 INNER JOIN demo_join2 AS t2 ON t2~d = t1~d ORDER BY t1~d INTO CORRESPONDING FIELDS OF TABLE @itab. out->write( itab )->next_section( `demo2 INNER JOIN demo1` ). SELECT t1~a AS a1, t1~b AS b1, t1~c AS c1, t1~d AS d1, t2~d AS d2, t2~e AS e2, t2~f AS f2, t2~g AS g2, t2~h AS h2 FROM demo_join2 AS t2 INNER JOIN demo_join1 AS t1 ON t1~d = t2~d ORDER BY t1~d INTO CORRESPONDING FIELDS OF TABLE @itab. out->write( itab )->end_section( )->next_section( `Outer Joins` )->begin_section( `demo1 LEFT OUTER JOIN demo2` ). SELECT t1~a AS a1, t1~b AS b1, t1~c AS c1, t1~d AS d1, t2~d AS d2, t2~e AS e2, t2~f AS f2, t2~g AS g2, t2~h AS h2 FROM demo_join1 AS t1 LEFT OUTER JOIN demo_join2 AS t2 ON t2~d = t1~d ORDER BY t1~d INTO CORRESPONDING FIELDS OF TABLE @itab. out->write( itab )->next_section( `demo2 LEFT OUTER JOIN demo1` ). SELECT t1~a AS a1, t1~b AS b1, t1~c AS c1, t1~d AS d1, t2~d AS d2, t2~e AS e2, t2~f AS f2, t2~g AS g2, t2~h AS h2 FROM demo_join2 AS t2 LEFT OUTER JOIN demo_join1 AS t1 ON t1~d = t2~d ORDER BY t1~d INTO CORRESPONDING FIELDS OF TABLE @itab. out->write( itab )->end_section( )->begin_section( `demo1 RIGHT OUTER JOIN demo2` ). SELECT t1~a AS a1, t1~b AS b1, t1~c AS c1, t1~d AS d1, t2~d AS d2, t2~e AS e2, t2~f AS f2, t2~g AS g2, t2~h AS h2 FROM demo_join1 AS t1 RIGHT OUTER JOIN demo_join2 AS t2 ON t2~d = t1~d ORDER BY t1~d INTO CORRESPONDING FIELDS OF TABLE @itab. out->write( itab )->next_section( `demo2 RIGHT OUTER JOIN demo1` ). SELECT t1~a AS a1, t1~b AS b1, t1~c AS c1, t1~d AS d1, t2~d AS d2, t2~e AS e2, t2~f AS f2, t2~g AS g2, t2~h AS h2 FROM demo_join2 AS t2 RIGHT OUTER JOIN demo_join1 AS t1 ON t1~d = t2~d ORDER BY t1~d INTO CORRESPONDING FIELDS OF TABLE @itab. out->display( itab ). ENDMETHOD. METHOD class_constructor. DATA: itab1 TYPE TABLE OF wa1 WITH EMPTY KEY, itab2 TYPE TABLE OF wa2 WITH EMPTY KEY. out = cl_demo_output=>new( )->begin_section( `Database Tables` ). itab1 = VALUE #( ( a = 'a1' b = 'b1' c = 'c1' d = 'uu' ) ( a = 'a2' b = 'b2' c = 'c2' d = 'uu' ) ( a = 'a3' b = 'b3' c = 'c3' d = 'vv' ) ( a = 'a4' b = 'b4' c = 'c4' d = 'ww' ) ). itab2 = VALUE #( ( d = 'uu' e = 'e1' f = 'f1' g = 'g1' h = 'h1' ) ( d = 'ww' e = 'e2' f = 'f2' g = 'g2' h = 'h2' ) ( d = 'xx' e = 'e3' f = 'f3' g = 'g3' h = 'h3' ) ). DELETE FROM demo_join1. INSERT demo_join1 FROM TABLE @itab1. DELETE FROM demo_join2. INSERT demo_join2 FROM TABLE @itab2. out->begin_section( `demo1` )->write( itab1 )->next_section( `demo2` )->write( itab2 )->end_section( )->end_section( ). ENDMETHOD. ENDCLASS. START-OF-SELECTION. demo=>main( ). |
以上。
发表评论