Q&A
もっとシンプルに絞った方が良いのでは？また具体的なデータと期待する結果を例示されると効率的だとおもいます
###前提・実現したいこと
ACCESS2010で左外結合の結果、右表に結合すべき行がない場合、右表のカラムを引数とするsum関数の返り値として必ずNULLまたは0を得たいと思っています。
###発生している問題・エラーメッセージ
左外結合の結果のデータシートビューで、結合する行がないサブクエリの列"ZF"のsumの結果の"ZCount"に1が返ってくる。他の列の挙動としては、JF1～JH3とADCountには0が返され、S1～KKPには何も表示されません（おそらくNullが返ってきている）。
###該当のソースコード
SELECT LocalGov.LgName, sum(JF1) AS JT1, sum(JF2) AS JT2, sum(JF3) AS JT3, max(J1) AS JP1, max(J2) AS JP2, max(J3) AS JP3, sum(J1) AS JH1, sum(J2) AS JH2, sum(J3) AS JH3, sum(ZF) AS ZCount, sum(AF) AS ADCount, sum(Sk_zai) AS S1, max(Sk_zai) AS SP1, sum(Sk_ad) AS S2, max(Sk_ad) AS SP2, sum(Tk_zai) AS SK1, max(Tk_zai) AS SKP1, sum(Tk_ad) AS SK2, max(Tk_ad) AS SKP2, sum(Hikasan) AS HK, max(Hikasan) AS HKP, sum(Kikasan) AS KK, max(Kikasan) AS KKP
FROM LocalGov LEFT JOIN (SELECT LgName, iif(Jtype=1,1,0) AS JF1, iif(Jtype=2,1,0) AS JF2, iif(Jtype=3,1,0) AS JF3, iif(Jtype=1,Jimu,0) AS J1, iif(Jtype=2,Jimu,0) AS J2, iif(Jtype=3,Jimu,0) AS J3, iif(isnull(Date_ad),1,0) AS ZF, iif(isnull(Date_ad),0,1) AS AF, Sk_zai, Sk_ad, Tk_zai, Tk_ad, Hikasan, Kikasan FROM WT_SQ_G1) AS T1 ON LocalGov.LgName = T1.LgName
WHERE LgMember=true
GROUP BY LocalGov.LgName, LgMemberSeq
ORDER BY LgMemberSeq;
###試したこと
sum(ZF) as ZCountを、sum(Nz([ZF],0) as ZCountとしても結果は同じでした。選択列にcount(LgName) as Fooを追加して、iif(Foo>0,sum(ZF),0) as ZCountとすると結果に0を得られますが、すべての列について同じようにするよりもシンプルにしたいと思っています。
###補足情報(言語/FW/ツール等のバージョンなど)
LocalGov、WT_SQ_G1ともにACCESSのローカルテーブルです。Date_adがNullの件数とNullでない件数を求めるために、それぞれ"ZF","AF"列を設けてどちらかに1を立てて選択列で合計しています。左表の"LgName"に等しい値を持つサブクエリの結果が存在しないときにこの問題が起こっています。結合できた場合は正しい結果が得られています。サブクエリ部分をクエリとして結合させても結果は変わりませんでした。
テーブル"LocalGov"と"WT_SQ_G1"について省略して記述します。
LocalGov
ID LgName LgMember LgMemberSeq
101 A True 2
102 B True 1
WT_SQ_G1
LgName Jtype Jimu Date_ad 以下省略
A 1 88,000 Null
A 2 111,300 Null
A 3 55,000 2016/8/21
結果
LgName JT1 JT2 JT3 JP1 JP2 JP3 JH1 JH2 JH3 ZCount ADCount
A 1 1 1 88,000 111,300 55,000 88,000 111,300 55,000 2 1
B 0 0 0 0 0 0 0 0 0 1 0
このLgName=Bの、ZCountが1になるのが不可解なんです。
（よく考えるとそれ以外の列が0を返すのが妙といえば妙ですが。）
回答1件
0
ベストアンサー
SQLのインデントがあれなので、
ぱっと見ただけの回答ですが、
ZFが1を返すのは、
SQL
1 iif(isnull(Date_ad),1,0) -- Date_addがnullなら1を返しているのでは？
のせいだと思われます。
またそもそも対象行が存在しない場合は、
いくらSUM内で0エスケープかけても意味がないため、
下記のようにSUMした結果をエスケープするとうまくいくのではないでしょうか？
(※LEFT JOINにてレコードなしでも全てNULLの行が発生するため、SUM(Nz([カラム名], 0))と動作差異は出なさそうです、すみません)
SQL
1 Nz(SUM([カラム名]),0) --SUMした結果に対してNz関数を適用
#追記
コメントに記載しましたが、
LEFT JOIN内のサブクエリで集約を行う方法だとうまく数が取れそうな気がします。
先ず確認のため、以下が現状のSQLとなっております。
(インデントは勝手につけました)
SQL
1SELECT 2 LocalGov.LgName 3 , sum(JF1) AS JT1 4 , sum(JF2) AS JT2 5 , sum(JF3) AS JT3 6 , max(J1) AS JP1 7 , max(J2) AS JP2 8 , max(J3) AS JP3 9 , sum(J1) AS JH1 10 , sum(J2) AS JH2 11 , sum(J3) AS JH3 12 , sum(ZF) AS ZCount 13 , sum(AF) AS ADCount 14 , sum(Sk_zai) AS S1 15 , max(Sk_zai) AS SP1 16 , sum(Sk_ad) AS S2 17 , max(Sk_ad) AS SP2 18 , sum(Tk_zai) AS SK1 19 , max(Tk_zai) AS SKP1 20 , sum(Tk_ad) AS SK2 21 , max(Tk_ad) AS SKP2 22 , sum(Hikasan) AS HK 23 , max(Hikasan) AS HKP 24 , sum(Kikasan) AS KK 25 , max(Kikasan) AS KKP 26FROM 27 LocalGov 28 LEFT JOIN ( 29 SELECT 30 LgName 31 , iif(Jtype = 1, 1, 0) AS JF1 32 , iif(Jtype = 2, 1, 0) AS JF2 33 , iif(Jtype = 3, 1, 0) AS JF3 34 , iif(Jtype = 1, Jimu, 0) AS J1 35 , iif(Jtype = 2, Jimu, 0) AS J2 36 , iif(Jtype = 3, Jimu, 0) AS J3 37 , iif(isnull(Date_ad), 1, 0) AS ZF 38 , iif(isnull(Date_ad), 0, 1) AS AF 39 , Sk_zai 40 , Sk_ad 41 , Tk_zai 42 , Tk_ad 43 , Hikasan 44 , Kikasan 45 FROM 46 WT_SQ_G1 47 ) AS T1 48 ON LocalGov.LgName = T1.LgName 49WHERE 50 LgMember = TRUE 51GROUP BY 52 LocalGov.LgName 53 , LgMemberSeq 54ORDER BY 55 LgMemberSeq;
上記を改めて見たところ、
集約しているカラムは全てWT_SQ_G1テーブルから取得していそうですので、
LEFT JOINでくっつける前にWT_SQ_G1のみで集約をかけると良いと思います。
以下はそのサンプルとなります。
先ずは手始めにLEFT JOINの内部から・・・
(※一部省略していますのでご注意を・・・)
SQL
1-- LEFT JOIN内のクエリ 2-- サブクエリ内でごちゃごちゃし過ぎると、 3-- Accessのレポート機能でエラーになることがあるらしいので選択クエリで用意する等工夫する必要があるかも・・・ 4SELECT 5 LgName 6 , COUNT(*) AS DATA_CNT 7 , SUM(iif(Jtype = 1, 1, 0)) AS JF1 8 , MAX(iif(Jtype = 1, Jimu, 0)) AS JP1 9 , SUM(iif(Jtype = 1, Jimu, 0)) AS JH1 10 , COUNT(Date_ad) AS AF 11 , SUM(Sk_zai) AS S1 12 , MAX(Sk_zai) AS SP1 13FROM 14 WT_SQ_G1 15GROUP BY 16 LgName
次はメインクエリの方のサンプルを記載します。
(上記クエリを選択クエリ「Query1」にしたと想定)
※サブクエリ側のカラムは常にNULLが混入する可能性があるので、
今回のケースでは全てエスケープしといた方が無難ですね。
SQL
1SELECT 2 LocalGov.LgName 3 , Nz(JF1, 0) AS JT1 4 , Nz(JP1, 0) AS JP1 5 , Nz(JH1, 0) AS JH1 6 , Nz(DATA_CNT, 0) - Nz(AF, 0) AS ZCount 7 , Nz(AF, 0) AS ADCount 8 , Nz(S1, 0) AS S1 9 , Nz(SP1, 0) AS SP1 10FROM 11 LocalGov 12 LEFT JOIN Query1 13 ON LocalGov.LgName = Query1.LgName 14WHERE 15 LgMember = TRUE 16-- 以下のGROUP BYはサブクエリで事前集約するよう変更したため不要 17-- GROUP BY 18-- LocalGov.LgName 19-- , LgMemberSeq 20ORDER BY 21 LgMemberSeq;
Accessで動作検証できる環境にないので、
このやり方でも駄目でしたらすみません・・・
ちなみに蛇足ですが、
JT1などの他のカラムで軒並み0を返しているのは、
ZCountが1を返すのと同じ理屈で**iif内の条件が成立していない(falseになる)**からではないでしょうかね？
投稿2016/09/27 03:23
編集2016/09/28 14:28
総合スコア1636
SUM(Nz([カラム名],1))でも良いのでは？
SUM(Nz([ZF],0)でも、Nz(sum([ZF]),0)でも結果は1でした。結合できた行がないわけですから、結合する行があれば必ず値が入っているLgNameをCount()して、0であれば右表の全ての列を実務上0として扱う他ないのかもしれません。戻り値の1は、いわゆる「ゴミ」と見た方がいいように思えます。
> A.Ichiさん
ご指摘ありがとうございます。
確かにLEFT JOINでメインテーブルに結合した時点でレコード数が1件以上となることはほぼ確定するので、
今回のケースではA.Ichiさんの方法で変わりなさそうですね。
後ほど一部記述を訂正しておきます。
ただSUMの結果がNULLの場合に必ず0を返す要件だと、
集計するデータ自体がない場合にNULLを返す点もカバーする必要もあるかなと思うので、SUM関数の結果をエスケープする方が無難かなと思ってます。
まぁ要件次第ですけどね^^;
> Mazak_Yamdaさん
コメント・検証のほどありがとうございます。
当方Accessの環境がないため、
クエリを試せていないのですが、
iifのNullの場合は1を返す処理を外しても1が返ってくるのでしょうか？
そもそもNullが欲しいのにiifを使っているところに疑問があるので^^;
(sum関数もGROUP BYに含まれてないカラムを取りたいから付けてるように見えますし・・・)
> Panzer_vorさん、ご回答ありがとうございます。
> そもそもNullが欲しいのにiifを使っているところに疑問
実はDate_ad列がNullの行数と、Nullでない行数を求めたいために、iif(isnull(Date_ad),1,0) as ZF, iif(isnull(Date_ad),0,1) as AFとしてカラムを作ってLgNameでGroupし、Sum(ZF),Sum(AF)としているものです。
ですから、ZF、AFともに1又は0であるはずで、左表のLgNameに対応するサブクエリの行数>0の場合にはSum()はうまく働きます。
ただし、左表の特定の行に結合する行がサブクエリにない場合は、集合関数は意味を持たないと考えなければならないのかと考えます。
それにしてもDate_adがNullの行数と、Nullでない行数を求めるスマートな方法はあるものでしょうか。
A.Ichiさん、コメントありがとうございます。
0にせよ、Nullにせよ、対象が存在しない場合には戻り値としては無から有を生むという意味で適切ではないのかもしれません。そういう意味で、結果は「ない」ということなのかなと考えています。
このクエリは帳票のためのデータを作るものなので、集計すべき行がない場合には実務上0が欲しいところなのです。
> Mazak_Yamdaさん
なるほどそのような意図があったんですね。
(ソースをよく読んでなくてすみません^^;)
いまの形のSQLであると、
COUNT取って判別するぐらいしか手がないかもしれないですね。
(当方もいいやり方が思いつきません。)
なのでうまいやり方をするにはクエリにテコ入れする必要があると思います。
LEFT JOINでくっつける側のデータは、
結局最終的にはLocalGovのデータ単位に集約される気配がするので、
LEFT JOIN内のサブクエリ内で事前に結合キーに指定する「LgName」単位で集約してはどうでしょう？
その中でCOUNT(*)取得したのが集約単位ごとの全レコード数、
COUNT(カラム名)で取得したの指定カラム内のNULLを除いたデータ数が取得できるのでもう少しうまいことできる気がします。
(このやり方をベースにした回答はまた後ほど・・・)
>Panzer_vorさん
Sum(iif(Jtype=1,1,0)) AS JF1、これは発想できませんでした！！
それに、右表を"LgName"でグループ化して集計してからLocalGovに左外結合させれば、SumやMaxに無効な演算をさせることもありません。
それに、Nz(JF1,0) AS JT1。JF1～JF3がNullかもしれないとの洞察、さすがです。
（悲しいかなNzの戻りは文字列になってしまうので、数値への型変換が要ります。）
サブクエリ部分を抜き出してクエリ化し、SQLを書き直しました。
結果、思い通りの出力を得られました。
これでまた前に進むことができます。ありがとうございました。
>Panzer_vorさん
>JT1などの他のカラムで軒並み0を返しているのは、 ZCountが1を返すのと同じ理屈でiif内の条件が成立していない
条件を評価すべき対象がないのですから、Sum関数が集計対象がない場合は0を返す仕様であればいいのですが、"ZCount"には演算対象がないにもかかわらず1が返ってきていました。このようなことはさせてはいけないということをよく覚えておきます。
それにしても、SQLにインデントをかけると見やすいですね。結果の構造もよくわかります。Accessだと、SQLを整形しても一旦保存すると全部つながってしまいますので、SQLってこういうものだと思っていました。
> Mazak_Yamdaさん
先ずは解決できて何よりです^^
よく見るとメインクエリの方でNzのエスケープ処理は1カラムにしかやってなかったですね、すみません^^;
外部結合での取得なので、
メインクエリではLgName以外のカラムはいずれもNULLエスケープしとかないと危ないですね。
メインクエリの不備については後ほど修正しときます。
> Mazak_Yamdaさん
>>> SQLにインデントをかけると見やすいですね
そうですね、インデントをつけてあげるだけでも大分デバッグ効率は上がります。
Accessがクエリの整形結果を自動で崩してしまう件については当方も把握してますが、
調査時などで試行錯誤する際は一旦整形を行った方が良いかと思います。
例えばA5:SQLというSQL開発ツールとかを利用すると、
SQLのフォーマットを一瞬で整えてくれるので便利です。
(設定により細かい調整がある程度効きますし。)
後蛇足ですが、A5:SQLは整形以外でもかなり便利な子なので、
機会があれば触ってみると良いかもしれませんね。
>Panzer_vorさん
A5:SQLはRDBをいじり始めたころに一度試していますが、ERDエディタとして使おうと思っていたものの、私には使えませんでした。
またトライしようと思います。
ありがとうございました。
あなたの回答
tips
プレビュー
