最近一直在做一个项目，很久没来这里写东西了。项目刚完成第一期，第二期就接踵而至了。在一期中，我发现自己对SQL文还有不少知识的空白点，比如对于UNION和左连接的使用，下面就这方面做些笔记，与大家共享。
■项目中使用的DB是Oracle。
■例一，注意UNION的使用。
★详细式样中的SQL文（部分）：
取得項目
省略
選択条件
請求残高．今回締日 ＝ 画面．「締日」
結合条件
得意先．得意先コード ＝ 請求残高．得意先コード
請求残高．得意先コード ＝ A．得意先コード
得意先．得意先コード ＝ 得意先アドオン．得意先コード
テーブル
得意先
得意先アドオン
請求残高
（取得項目
省略
選択条件
売上実績．売上計上日 ＞ add_months(締日,-1)
売上実績．売上計上日 ＜＝ 締日
結合条件
売上実績アドオン．売上実績管理番号 ＝ 売上実績．売上実績管理番号
その他結合条件
テーブル
売上実績
売上実績アドオン
コンボボックスデータ
UNION
取得項目
省略
選択条件
入金明細．削除フラグ ＝ ０
入金データ．削除フラグ ＝ ０
結合条件
入金データ．入金番号 ＝ 入金明細．入金番号
その他結合条件
テーブル
入金明細
入金データ
） A ★对应的SQL文（部分）：
1 "SELECT "
2 +" M_CUST.CUST_CD, "
3 +" M_CUST_A.CUST_A_SELFPSN, "
4 +" M_EXCH_RATE_A.EXCH_RATE, "
5 +" T_BILL_CUMULATE_W_DDS.CLAIM_LAST, "
6 +" A.UN_TYPE, "
7 +" A.SALES_DATE, "
8 +" A.SLIP_CD, "
9 +" A.DDS_TORI_TYP_ALL_U, "
10 +" A.DDS_TORI_TYP_ALL_N, "
11 +" A.CUST_ODR_NO, "
12 +" A.BOUND_CD, "
13 +" A.ITEM_NAME_U, "
14 +" A.ITEM_NAME_N, "
15 +" A.UPDATED_BY, "
16 +" A.SALES_QTY, "
17 +" A.UNIT_COST_TYP, "
18 +" A.SALES_UNIT_PRICE_EXCH_RATES, "
19 +" A.SALES_AMOUNT_EXCH_RATES, "
20 +" A.SALES_UNIT_PRICE, "
21 +" A.SALES_AMOUNT, "
22 +" A.DEPO_AMOUNT, "
23 +" A.FOREIGN_AMOUNT, "
24 +" A.SALES_TYP "
25 +"FROM "
26 +" T_BILL_CUMULATE_W_DDS, "
27 +" M_CUST, "
28 +" M_CUST_A, "
29 +" M_CUR, "
30 +" M_EXCH_RATE_A, "
31 +" ( "
32 +" SELECT "
33 +" '1' AS UN_TYPE, "
34 +" C.CUST_CD AS CUST_CD, "
35 +" C.SALES_DATE AS SALES_DATE, "
36 +" C.SLIP_CD AS SLIP_CD, "
37 +" C.SALES_SEQ_NO, "
38 +" '', "
39 +" D.VEND_TYP AS DDS_TORI_TYP_ALL_U, "
40 +" '' AS DDS_TORI_TYP_ALL_N, "
41 +" C.CUST_ODR_NO AS CUST_ODR_NO, "
42 +" D.BOUND_CD AS BOUND_CD, "
43 +" C.ITEM_NAME AS ITEM_NAME_U, "
44 +" TO_NUMBER('') AS ITEM_NAME_N, "
45 +" C.UPDATED_BY AS UPDATED_BY, "
46 +" C.SALES_QTY AS SALES_QTY, "
47 +" D.UNIT_COST_TYP AS UNIT_COST_TYP, "
48 +" C.SALES_UNIT_PRICE AS SALES_UNIT_PRICE_EXCH_RATES, "
49 +" C.SALES_AMOUNT_EXCH_RATES AS SALES_AMOUNT_EXCH_RATES, "
50 +" C.SALES_UNIT_PRICE AS SALES_UNIT_PRICE, "
51 +" C.SALES_AMOUNT AS SALES_AMOUNT, "
52 +" TO_NUMBER('') AS DEPO_AMOUNT, "
53 +" TO_NUMBER('') AS FOREIGN_AMOUNT, "
54 +" D.SALES_TYP AS SALES_TYP "
55 +" FROM "
56 +" T_SALES C, "
57 +" T_SALES_A D "
58 +" WHERE "
59 +" C.SALES_DATE > ADD_MONTHS(?, - 1) "
60 +" AND C.SALES_DATE <= ? "
61 +" AND D.VEND_TYP IN ( "
62 +" SELECT "
63 +" SYS_TYPE_VALUE.VALUE "
64 +" FROM "
65 +" SYS_TYPE_VALUE "
66 +" WHERE "
67 +" SYS_TYPE_VALUE.NAME = 'DDS_TORI_TYP_ALL' "
68 +" ) "
69 +" AND D.UNIT_COST_TYP IN ( "
70 +" SELECT "
71 +" SYS_TYPE_VALUE.VALUE "
72 +" FROM "
73 +" SYS_TYPE_VALUE "
74 +" WHERE "
75 +" SYS_TYPE_VALUE.NAME = 'UNIT_COST_TYP' "
76 +" ) "
77 +" AND C.SALES_SEQ_NO = D.SALES_SEQ_NO "
78 +" UNION "
79 +" SELECT "
80 +" '2', "
81 +" G.DEPO_SALES_CD, "
82 +" G.DEPO_DATE, "
83 +" G.DEPO_CTL_NO, "
84 +" TO_NUMBER(''), "
85 +" H.DEPO_DTL_LINE, "
86 +" TO_NUMBER(''), "
87 +" '入金', "
88 +" '', "
89 +" '', "
90 +" '', "
91 +" H.DEPO_CLASS, "
92 +" H.UPDATED_BY, "
93 +" TO_NUMBER(''), "
94 +" TO_NUMBER(''), "
95 +" TO_NUMBER(''), "
96 +" TO_NUMBER(''), "
97 +" TO_NUMBER(''), "
98 +" TO_NUMBER(''), "
99 +" H.DEPO_AMOUNT, "
100 +" H.FOREIGN_AMOUNT, "
101 +" '' "
102 +" FROM "
103 +" T_M_DEPOSIT_DDS G, "
104 +" T_M_DEPOSIT_M_DDS H "
105 +" WHERE "
106 +" G.DEL_FLG = '0' "
107 +" AND H.DEL_FLG = '0' "
108 +" AND G.DEPO_DATE > ADD_MONTHS(?, - 1) "
109 +" AND G.DEPO_DATE <= ? "
110 +" AND H.DEPO_CLASS IN ( "
111 +" SELECT "
112 +" SYS_TYPE_VALUE.VALUE "
113 +" FROM "
114 +" SYS_TYPE_VALUE "
115 +" WHERE "
116 +" SYS_TYPE_VALUE.NAME = 'DDS_DEPO_CLASS' "
117 +" ) "
118 +" AND G.DEPO_CTL_NO = H.DEPO_CTL_NO "
119 +" ) A "
120 +"WHERE "
121 +" AND T_BILL_CUMULATE_W_DDS.BILL_CLOSE_DATE = ? "
122 +" AND M_EXCH_RATE_A.CUR_CD = M_CUR.CUR_CD "
123 +" AND TO_CHAR(M_EXCH_RATE_A.EXCH_START_DATE, 'YYYYMM') = TO_CHAR(A.SALES_DATE, 'YYYYMM') "
124 +" AND TO_CHAR(M_EXCH_RATE_A.EXCH_START_DATE, 'DD') < TO_CHAR(A.SALES_DATE, 'DD') "
125 +" AND M_CUST.CUST_CD = M_CUST_A.CUST_CD "
126 +" AND M_CUST.CUST_CD = T_BILL_CUMULATE_W_DDS.CUST_CD "
127 +" AND T_BILL_CUMULATE_W_DDS.CUST_CD = A.CUST_CD "
128 +"ORDER BY "
129 +" M_CUST.CUST_CD, "
130 +" A.SALES_DATE, "
131 +" A.SLIP_CD"
■例二，注意左连接的使用。
★详细式样中的SQL文（部分）：
③画面入力値をもとに、[支払データ]、[支払データ明細]、[通貨]結合し、検索する。
・[支払データ］の検索条件
[支払データ］.作成者 ＝ 画面.処理担当
[支払データ］.削除フラグ ＝ 0
画面から出力済再出力が選択された場合
[支払データ］.抽出フラグ ＝ １
画面から出力済再出力が選択しない場合
[支払データ］.抽出フラグ ＝ ０
・[支払データ］、[支払データ明細］の結合条件
[支払データ］.支払番号 ＝ [支払データ明細］.支払番号
・[支払データ］、[通貨］の結合条件
[支払データ］.通貨コード ＝ [通貨］.通貨コード
・支払区分取得
抽出項目： [コンボボックスデータ].表示説明
テーブル：コンボボックスデータ （LEFTJOIN）
取得条件：
[支払データ明細］.支払区分 ＝ [コンボボックスデータ].値
[コンボボックスデータ].名称 ＝ 'DDS_PAY_CLASS'
・経費科目取得
抽出項目： [コンボボックスデータ].表示説明
テーブル：コンボボックスデータ （LEFTJOIN）
取得条件：
[支払データ明細］.経費科目 ＝ [コンボボックスデータ].値
[コンボボックスデータ].名称 ＝ 'DDS_PAY_MANAGE_ITEM'
★对应的SQL文（部分）：
1 SELECT
2 A.PAY_SALES_CD,
3 A.PAY_SALES_NAME,
4 A.PAY_DATE,
5 A.PAY_CTL_NO,
6 A.PAY_DEPO_DTL_LINE,
7 A.PAY_FSUM_AMOUNT,
8 A.PAY_SUM_AMOUNT,
9 A.PAY_BILL_DATE,
10 A.PAY_BILL_NO,
11 A.CUR_SYMBOL,
12 A.PAY_DEPO_SUMMARY,
13 B.DISPLAY_NAME,
14 C.DISPLAY_NAME,
15 D.DISPLAY_NAME,
16 E.DISPLAY_NAME
17 FROM(
18 SELECT
19 T_M_PAYMENT_DDS.PAY_SALES_CD AS PAY_SALES_CD,
20 T_M_PAYMENT_DDS.PAY_SALES_NAME AS PAY_SALES_NAME,
21 T_M_PAYMENT_DDS.PAY_DATE AS PAY_DATE,
22 T_M_PAYMENT_DDS.PAY_CTL_NO AS PAY_CTL_NO,
23 T_M_PAYMENT_M_DDS.PAY_DEPO_DTL_LINE AS PAY_DEPO_DTL_LINE,
24 T_M_PAYMENT_M_DDS.PAY_FSUM_AMOUNT AS PAY_FSUM_AMOUNT,
25 T_M_PAYMENT_M_DDS.PAY_SUM_AMOUNT AS PAY_SUM_AMOUNT,
26 T_M_PAYMENT_M_DDS.PAY_BILL_DATE AS PAY_BILL_DATE,
27 T_M_PAYMENT_M_DDS.PAY_BILL_NO AS PAY_BILL_NO,
28 T_M_PAYMENT_M_DDS.PAY_MANAGE_ITEM AS PAY_MANAGE_ITEM,
29 T_M_PAYMENT_M_DDS.PAY_DEPO_SUMMARY AS PAY_DEPO_SUMMARY,
30 T_M_PAYMENT_M_DDS.PAY_CLASS AS PAY_CLASS,
31 T_M_PAYMENT_M_DDS.PAY_BANK_NAME AS PAY_BANK_NAME,
32 T_M_PAYMENT_M_DDS.PAY_ORG_NAME AS PAY_ORG_NAME,
33 M_CUR.CUR_SYMBOL AS CUR_SYMBOL
34 FROM
35 T_M_PAYMENT_DDS,
36 T_M_PAYMENT_M_DDS,
37 M_CUR
38 WHERE
39 T_M_PAYMENT_DDS.PAY_CTL_NO = T_M_PAYMENT_M_DDS.PAY_CTL_NO
40 AND T_M_PAYMENT_DDS.PAY_CUR_CD = M_CUR.CUR_CD
41 AND T_M_PAYMENT_DDS.CREATED_BY = ?
42 AND T_M_PAYMENT_DDS.CREATED_DATE >= ?
43 AND TO_CHAR(T_M_PAYMENT_DDS.CREATED_DATE, 'YYYY/MM/DD') <= ?
44 AND T_M_PAYMENT_DDS.DEL_FLG = 0
45 AND T_M_PAYMENT_DDS.PAY_FLG = 0
46 AND T_M_PAYMENT_DDS.SELECT_FLG = ?
47 ORDER BY
48 T_M_PAYMENT_DDS.PAY_CTL_NO,
49 T_M_PAYMENT_M_DDS.PAY_DEPO_DTL_LINE
50 ) A LEFT JOIN(
51 SELECT
52 SYS_TYPE_VALUE.DISPLAY_NAME AS DISPLAY_NAME,
53 SYS_TYPE_VALUE.VALUE AS VALUE
54 FROM
55 SYS_TYPE_VALUE
56 WHERE
57 SYS_TYPE_VALUE.NAME = 'DDS_PAY_CLASS'
58 ) B ON A.PAY_CLASS = B.VALUE LEFT JOIN(
59 SELECT
60 SYS_TYPE_VALUE.DISPLAY_NAME AS DISPLAY_NAME,
61 SYS_TYPE_VALUE.VALUE AS VALUE
62 FROM
63 SYS_TYPE_VALUE
64 WHERE
65 SYS_TYPE_VALUE.NAME = 'DDS_PAY_MANAGE_ITEM'
66 ) C ON A.PAY_MANAGE_ITEM = C.VALUE LEFT JOIN(
67 SELECT
68 SYS_TYPE_VALUE.DISPLAY_NAME AS DISPLAY_NAME,
69 SYS_TYPE_VALUE.VALUE AS VALUE
70 FROM
71 SYS_TYPE_VALUE
72 WHERE
73 SYS_TYPE_VALUE.NAME = 'DDS_PAY_BANK_NAME'
74 ) D ON A.PAY_BANK_NAME = D.VALUE LEFT JOIN(
75 SELECT
76 SYS_TYPE_VALUE.DISPLAY_NAME AS DISPLAY_NAME,
77 SYS_TYPE_VALUE.VALUE AS VALUE
78 FROM
79 SYS_TYPE_VALUE
80 WHERE
81 SYS_TYPE_VALUE.NAME = 'DDS_PAY_ORG_NAME'
82 ) E ON A.PAY_ORG_NAME = E.VALUE
posted on 2006-12-18 11:44
小言身寸 阅读(872)
评论(0) 编辑 收藏 所属分类:
数据库/SQL语言