teratail
回答率
85.47%
質問するログイン新規登録
トップAccessに関する質問左外結合の結果、結合すべき行がない右表のカラムのsum関数が1を返す理由と回避方法

編集履歴

回答編集履歴

5

NULLエスケープ漏れ分

2016/09/28 14:28

投稿

Panzer_vor
Panzer_vor

スコア1636

test CHANGED
@@ -214,25 +214,31 @@
214
214
 
215
215
  (上記クエリを選択クエリ「Query1」にしたと想定)
216
216
 
217
+
218
+
219
+ ※サブクエリ側のカラムは常にNULLが混入する可能性があるので、
220
+
221
+ 今回のケースでは全てエスケープしといた方が無難ですね。
222
+
217
223
  ```SQL
218
224
 
219
225
  SELECT
220
226
 
221
227
  LocalGov.LgName
222
228
 
223
- , Nz(JF1, 0) AS JT1
229
+ , Nz(JF1, 0) AS JT1
224
-
230
+
225
- , JP1
231
+ , Nz(JP1, 0) AS JP1
226
-
232
+
227
- , JH1
233
+ , Nz(JH1, 0) AS JH1
228
-
234
+
229
- , DATA_CNT - AF AS ZCount
235
+ , Nz(DATA_CNT, 0) - Nz(AF, 0) AS ZCount
230
-
236
+
231
- , AF AS ADCount
237
+ , Nz(AF, 0) AS ADCount
232
-
238
+
233
- , S1
239
+ , Nz(S1, 0) AS S1
234
-
240
+
235
- , SP1
241
+ , Nz(SP1, 0) AS SP1
236
242
 
237
243
  FROM
238
244
 

4

カラム名修正

2016/09/28 14:27

投稿

Panzer_vor
Panzer_vor

スコア1636

test CHANGED
@@ -272,6 +272,6 @@
272
272
 
273
273
  JT1などの他のカラムで軒並み0を返しているのは、
274
274
 
275
- ZFが1を返すのと同じ理屈で**iif内の条件が成立していない(falseになる)**からではないでしょうかね？
275
+ ZCountが1を返すのと同じ理屈で**iif内の条件が成立していない(falseになる)**からではないでしょうかね？
276
-
277
-
276
+
277
+

3

蛇足

2016/09/27 17:24

投稿

Panzer_vor
Panzer_vor

スコア1636

test CHANGED
@@ -265,3 +265,13 @@
265
265
  Accessで動作検証できる環境にないので、
266
266
 
267
267
  このやり方でも駄目でしたらすみません・・・
268
+
269
+
270
+
271
+ ちなみに蛇足ですが、
272
+
273
+ JT1などの他のカラムで軒並み0を返しているのは、
274
+
275
+ ZFが1を返すのと同じ理屈で**iif内の条件が成立していない(falseになる)**からではないでしょうかね？
276
+
277
+

2

コード追記

2016/09/27 17:23

投稿

Panzer_vor
Panzer_vor

スコア1636

test CHANGED
@@ -22,10 +22,246 @@
22
22
 
23
23
  下記のように**SUMした結果をエスケープ**するとうまくいくのではないでしょうか？
24
24
 
25
-
25
+ **(※LEFT JOINにてレコードなしでも全てNULLの行が発生するため、`SUM(Nz([カラム名], 0))`と動作差異は出なさそうです、すみません)**
26
26
 
27
27
  ```SQL
28
28
 
29
29
  Nz(SUM([カラム名]),0) --SUMした結果に対してNz関数を適用
30
30
 
31
31
  ```
32
+
33
+
34
+
35
+ #追記
36
+
37
+ コメントに記載しましたが、
38
+
39
+ LEFT JOIN内のサブクエリで集約を行う方法だとうまく数が取れそうな気がします。
40
+
41
+ 先ず確認のため、以下が現状のSQLとなっております。
42
+
43
+ (インデントは勝手につけました)
44
+
45
+ ```SQL
46
+
47
+ SELECT
48
+
49
+ LocalGov.LgName
50
+
51
+ , sum(JF1) AS JT1
52
+
53
+ , sum(JF2) AS JT2
54
+
55
+ , sum(JF3) AS JT3
56
+
57
+ , max(J1) AS JP1
58
+
59
+ , max(J2) AS JP2
60
+
61
+ , max(J3) AS JP3
62
+
63
+ , sum(J1) AS JH1
64
+
65
+ , sum(J2) AS JH2
66
+
67
+ , sum(J3) AS JH3
68
+
69
+ , sum(ZF) AS ZCount
70
+
71
+ , sum(AF) AS ADCount
72
+
73
+ , sum(Sk_zai) AS S1
74
+
75
+ , max(Sk_zai) AS SP1
76
+
77
+ , sum(Sk_ad) AS S2
78
+
79
+ , max(Sk_ad) AS SP2
80
+
81
+ , sum(Tk_zai) AS SK1
82
+
83
+ , max(Tk_zai) AS SKP1
84
+
85
+ , sum(Tk_ad) AS SK2
86
+
87
+ , max(Tk_ad) AS SKP2
88
+
89
+ , sum(Hikasan) AS HK
90
+
91
+ , max(Hikasan) AS HKP
92
+
93
+ , sum(Kikasan) AS KK
94
+
95
+ , max(Kikasan) AS KKP
96
+
97
+ FROM
98
+
99
+ LocalGov
100
+
101
+ LEFT JOIN (
102
+
103
+ SELECT
104
+
105
+ LgName
106
+
107
+ , iif(Jtype = 1, 1, 0) AS JF1
108
+
109
+ , iif(Jtype = 2, 1, 0) AS JF2
110
+
111
+ , iif(Jtype = 3, 1, 0) AS JF3
112
+
113
+ , iif(Jtype = 1, Jimu, 0) AS J1
114
+
115
+ , iif(Jtype = 2, Jimu, 0) AS J2
116
+
117
+ , iif(Jtype = 3, Jimu, 0) AS J3
118
+
119
+ , iif(isnull(Date_ad), 1, 0) AS ZF
120
+
121
+ , iif(isnull(Date_ad), 0, 1) AS AF
122
+
123
+ , Sk_zai
124
+
125
+ , Sk_ad
126
+
127
+ , Tk_zai
128
+
129
+ , Tk_ad
130
+
131
+ , Hikasan
132
+
133
+ , Kikasan
134
+
135
+ FROM
136
+
137
+ WT_SQ_G1
138
+
139
+ ) AS T1
140
+
141
+ ON LocalGov.LgName = T1.LgName
142
+
143
+ WHERE
144
+
145
+ LgMember = TRUE
146
+
147
+ GROUP BY
148
+
149
+ LocalGov.LgName
150
+
151
+ , LgMemberSeq
152
+
153
+ ORDER BY
154
+
155
+ LgMemberSeq;
156
+
157
+ ```
158
+
159
+
160
+
161
+ 上記を改めて見たところ、
162
+
163
+ 集約しているカラムは全て**WT_SQ_G1テーブル**から取得していそうですので、
164
+
165
+ LEFT JOINでくっつける前にWT_SQ_G1のみで集約をかけると良いと思います。
166
+
167
+
168
+
169
+ 以下はそのサンプルとなります。
170
+
171
+ 先ずは手始めにLEFT JOINの内部から・・・
172
+
173
+ **(※一部省略していますのでご注意を・・・)**
174
+
175
+ ```SQL
176
+
177
+ -- LEFT JOIN内のクエリ
178
+
179
+ -- サブクエリ内でごちゃごちゃし過ぎると、
180
+
181
+ -- Accessのレポート機能でエラーになることがあるらしいので選択クエリで用意する等工夫する必要があるかも・・・
182
+
183
+ SELECT
184
+
185
+ LgName
186
+
187
+ , COUNT(*) AS DATA_CNT
188
+
189
+ , SUM(iif(Jtype = 1, 1, 0)) AS JF1
190
+
191
+ , MAX(iif(Jtype = 1, Jimu, 0)) AS JP1
192
+
193
+ , SUM(iif(Jtype = 1, Jimu, 0)) AS JH1
194
+
195
+ , COUNT(Date_ad) AS AF
196
+
197
+ , SUM(Sk_zai) AS S1
198
+
199
+ , MAX(Sk_zai) AS SP1
200
+
201
+ FROM
202
+
203
+ WT_SQ_G1
204
+
205
+ GROUP BY
206
+
207
+ LgName
208
+
209
+ ```
210
+
211
+
212
+
213
+ 次はメインクエリの方のサンプルを記載します。
214
+
215
+ (上記クエリを選択クエリ「Query1」にしたと想定)
216
+
217
+ ```SQL
218
+
219
+ SELECT
220
+
221
+ LocalGov.LgName
222
+
223
+ , Nz(JF1, 0) AS JT1
224
+
225
+ , JP1
226
+
227
+ , JH1
228
+
229
+ , DATA_CNT - AF AS ZCount
230
+
231
+ , AF AS ADCount
232
+
233
+ , S1
234
+
235
+ , SP1
236
+
237
+ FROM
238
+
239
+ LocalGov
240
+
241
+ LEFT JOIN Query1
242
+
243
+ ON LocalGov.LgName = Query1.LgName
244
+
245
+ WHERE
246
+
247
+ LgMember = TRUE
248
+
249
+ -- 以下のGROUP BYはサブクエリで事前集約するよう変更したため不要
250
+
251
+ -- GROUP BY
252
+
253
+ -- LocalGov.LgName
254
+
255
+ -- , LgMemberSeq
256
+
257
+ ORDER BY
258
+
259
+ LgMemberSeq;
260
+
261
+ ```
262
+
263
+
264
+
265
+ Accessで動作検証できる環境にないので、
266
+
267
+ このやり方でも駄目でしたらすみません・・・

1

誤字修正

2016/09/27 17:17

投稿

Panzer_vor
Panzer_vor

スコア1636

test CHANGED
@@ -16,9 +16,11 @@
16
16
 
17
17
 
18
18
 
19
- またそもそも対象行がが存在しない場合はいくらSUM内で0エスケープかけたも意味がないので、
19
+ またそもそも対象行が存在しない場合は、
20
20
 
21
+ いくらSUM内で0エスケープかけても意味がないため、
22
+
21
- 下記のよう**SUMした結果をエスケープ**したらうまくいくのではないてましょうか？
23
+ 下記のように**SUMした結果をエスケープ**するとうまくいくのではないでしょうか？
22
24
 
23
25
 
24
26