SQL Server 2008コミュの教えてください（初心者です）

まずは以下の SQL を実行して見るべし


select 仕入明細テーブル.物件CD, 物件テーブル.物件名, 客先テーブル.客先名, 仕入明細テーブル.金額
from 仕入明細テーブル
  inner join 仕入伝票テーブル on 仕入明細テーブル.仕入伝票No = 仕入伝票テーブル.仕入伝票No
  inner join 物件テーブル on 仕入明細テーブル.物件No = 物件テーブル.物件No
  inner join 客先テーブル on 物件テーブル.客先No = 客先テーブル.客先No

>group付近で不適切、と言われてしまいます。

まず、誤解の原因はSQLのサブクエリー構文というのは、一時的に中間結果の表を作り、SQLの実行が終わったら中間結果の表は自動的に破棄するということです。

つまり、中間結果の表でなかったら。たとえばTMPという表で集計するなら

select
TMP.物件CD, sum(TMP.金額) as 合計額
from TMP
group by TMP.物件CD

と書くわけです。(表がひとつしかないのでTMP.はあってもなくてもよい訳です)

でもやりたいのはTMP表ではなくサブクエリー結果にgroup byしたい。で、それなら

select
TMP.物件CD, sum(TMP.金額) as 合計額
from (select …)　AS TMP
group by TMP.物件CD

なのです。

ちなみにMSDNのリファレンス(http://msdn.microsoft.com/ja-jp/library/ms177634.aspx)
FROM句の説明で

"[AS] table_alias
table_source の別名です。別名は、便宜上、または自己結合やサブクエリでテーブルまたはビューを区別するために使用できます…"と、書かれています。

リファレンスで"[AS] "と書かれているのは省略可能という意味なので

select
TMP.物件CD, sum(TMP.金額) as 合計額
from (select …) TMP
group by TMP.物件CD

と、書いてもよいということです。

>from句内のselect文だけを実行してみると、納品日の期間指定が無視されます。
これはサブクエリーの集合関数とは別のバグです。

>(期日指定した上で)これは「９または１１」でないと意味がない項目です。

冷静に考えればバグはすぐ見つかると思います。

(仕入伝票テーブル.納品日 between '2011/12/01' and '2011/12/31') and
仕入明細テーブル.仕入費目CD=9 or 仕入明細テーブル.仕入費目CD=11

ではなくて

(仕入伝票テーブル.納品日 between '2011/12/01' and '2011/12/31') and
(仕入明細テーブル.仕入費目CD=9 or 仕入明細テーブル.仕入費目CD=11)

ですよね?

つまりAND演算子とOR演算子の結合優先順位に誤解があったということです。

こういうバグを防ぐのならIN演算子(http://msdn.microsoft.com/ja-jp/library/ms177682.aspx)を使いましょう。

(仕入伝票テーブル.納品日 between '2011/12/01' and '2011/12/31') and
(仕入明細テーブル.仕入費目CD IN (9,11))

以上を勘案すると

select
　TMP.物件CD,
　sum(TMP.金額) as 合計額
　from　(
　　select
　　　MEISAI.物件CD,
　　　MEISAI.金額,
　　　from
　　　　　仕入明細テーブル as MEISAI inner join 仕入伝票テーブル as DENPYOU
　　　　　　　　　on MEISAI.[仕入伝票No]=DENPYOU.[仕入伝票No]
　　　where (
　　　　　DENPYOU.納品日 between '2011/12/01' and '2011/12/31')
and (MEISAI.仕入費目CD in (9,11)
)
) as TMP
group by TMP.物件CD

"仕入伝票テーブル.納品日"列はサブクエリのWHERE句でしか使われないのでなく、外側の集計SELECT文では使わないしょ ? 。なのでサブクエリのSELECTになくていいはずです。

SQLの動作は次のとおりです。

1. 仕入明細テーブルと仕入伝票テーブルを[仕入伝票No]で内部結合する。
2. 中間結果表は物件CD列と金額列だけが含まれる
3. where 句 (DENPYOU.納品日 between '2011/12/01' and '2011/12/31') and (MEISAI.仕入費目CD in (9,11)で表が選択される。
4.生成した中間(Temporary)表に TMPという別名をつける。TMP表は内部結合したうち"物件CD"列と"金額"列だけが含まれる。
5.　TMP表をFROM句にして物件CD列でgroup by し、集合関数sumでTMP表の金額列を処理する。
6. 集合関数sumの結果列は"合計額"という別名がつけられる。

(そろそろ文字数制限なので、ブレイク)

さて、
>"仕入明細テーブル．物件CD"、"物件テーブル．物件名"、"客先テーブル．客先名"　にて　グループ化

という要求仕様なので、実はSQLが仕様を満たしません。内部結合はたっつぁん氏のとおりなので

select
　TMP.物件CD,
　TMP.物件名
TMP.客先名
　sum(TMP.金額) as 合計額
from (
select MEISAI.物件CD, BUKKEN.物件名, KYAKUSAKI.客先名, MEISAI.金額
from 仕入明細テーブル as MEISAI
inner join 仕入伝票テーブル as DENPYOU
on MEISAI.仕入伝票No = DENPYOU.仕入伝票No
inner join 物件テーブル as BUKKEN
on MEISAI.物件No = BUKKEN.物件No
inner join 客先テーブル as KYAKUSAKI
on BUKKEN.客先No = KYAKUSAKI.客先No
where (
　　　　　DENPYOU.納品日 between '2011/12/01' and '2011/12/31')
and (MEISAI.仕入費目CD in (9,11)
)

) as TMP
group by TMP.物件CD, TMP.物件名, TMP.物件名

ここまでできると60点。

実は

select MEISAI.物件CD, BUKKEN.物件名, KYAKUSAKI.客先名, sum(MEISAI.金額) as 合計額
from 仕入明細テーブル as MEISAI
inner join 仕入伝票テーブル as DENPYOU
on MEISAI.仕入伝票No = DENPYOU.仕入伝票No
inner join 物件テーブル as BUKKEN
on MEISAI.物件No = BUKKEN.物件No
inner join 客先テーブル as KYAKUSAKI
on BUKKEN.客先No = KYAKUSAKI.客先No
where (
　　　　　DENPYOU.納品日 between '2011/12/01' and '2011/12/31')
and (MEISAI.仕入費目CD in (9,11)
)
　　group by MEISAI.物件CD, BUKKEN.物件名, KYAKUSAKI.客先名

でもいいはずというのはたっつぁん氏のご明察。

ただ、パフォーマンスを考えると、ただ捨てるためだけの内部結合はもったいないので

select MEISAI.物件CD, BUKKEN.物件名, KYAKUSAKI.客先名, sum(MEISAI.金額) as 合計額
from 仕入明細テーブル as MEISAI
inner join 仕入伝票テーブル as DENPYOU
on MEISAI.仕入伝票No = DENPYOU.仕入伝票No
and (DENPYOU.納品日 between '2011/12/01' and '2011/12/31')
and (MEISAI.仕入費目CD in (9,11))
inner join 物件テーブル as BUKKEN
on MEISAI.物件No = BUKKEN.物件No
inner join 客先テーブル as KYAKUSAKI
on BUKKEN.客先No = KYAKUSAKI.客先No
　　group by MEISAI.物件CD, BUKKEN.物件名, KYAKUSAKI.客先名

という風に、where句を内部結合の条件句に繰り込むことも可能。

たっつぁんさま、あら金さま

ありがとうございます！
今はおうちなので、会社でアドバイスいただいたとおり実行して報告いたします

4＞あら金さん
>>パフォーマンスを考えると、ただ捨てるためだけの内部結合はもったいない

昔のバージョンならいざ知らず最近はオプティマイザが進化しているので、結合条件で絞り込む場合と、結合後に全体から絞り込む場合とでどちらが効率的かは見た目で判定できません

索引の付け方とか、レコード件数とか、マシンのメモリ量とか様々な要因に影響されるので、パフォーマンスが問題になるような場合は、実際のコードをパフォーマンス プロファイラーで検証して、どちらの記述が優れているかを都度判断するのが望ましいでしょう

実際 概ねは結合条件で絞り込む方がパフォーマンス的に有利のようです
まぁ、コメント 1 で提示した SQL はトピ主の理解を深めるための参考のつもりだし (だから まったく絞り込んでいない)

>実際 概ねは結合条件で絞り込む方がパフォーマンス的に有利のようです

経験から言うと、Oracleのオプティマイザは素直だが、SQL Serverのオプティマイザは信用していない。

SQL Serverの基本設計思想は自動自律調律なのだが…理解に苦しむ実行プランを立ててくる。それも表統計を取り直してもインデックスを張りなおしたり、複合インデックスにしても大体その工夫したインデックスを使ってくれなかったりする。

SQL Serverでパフォーマンスで問題化したら環境をスケールアップするくらいしか手がない。ディスクを分散して、メモリーを増設して、CPUを増やす。ライセンス料は鯉の滝登り。

特にADO.NETのメモリー喰いは最低。

Oracleの場合、山ほどチューニングパラメータがある。

あら金さん
たっつぁんさん


さっそく、ひとつひとつノートに書き出して頭を整理したうえで、実際にマネジメントスタジオで書いてみました。
FROM句で作成したテーブルを「ＴＭＰ」としていただいて、進行や出所がはっきり理解できました。

あと、すべてのテーブルをT_仕入明細に対してJOINさせなければならない、と考えていたため、他のストアドでも非常に面倒なFROM句を書いていたのですが、最初のたっつぁんさんのアドバイスで目からウロコです！
１つめと２つめをつなげて、２つめから３つめにつなげるのはＯＫなのですね。
考えてみれば、Accessでは上記ができますので、当然なのかもしれませんが･･･。
このパターンがいくつもあるので、あちこちに応用できそうです。
本当にありがとうございました。

また何かどうしてもわからないことがあれば、ご相談させていただきますので、よろしくお願いします。


ちなみに、もしよければ教えて下さい。
現在勉強に使っている本は、MS公式のものですが、どちらかといえばインストールから基本的な運用管理のほうに話が偏っています。
（バックアップの方法等）
ｓｑｌ文の具体的な記述の上で参考になる書籍は、どういったものがありますか？
大型の書店でｓｑｌの棚を見ても、どうもしっくりくるものがありません。

またインターフェースがウェブ等でなく、Access（ＡＤＰ）なので、極端に参考書籍が少ない状態です。
（手元にあるのは、これ↓のみです）
http://www.amazon.co.jp/%E5%AE%9F%E8%B7%B5Access%E3%82%A2%E3%83%83%E3%83%97%E3%82%B5%E3%82%A4%E3%82%B8%E3%83%B3%E3%82%B0-DB-Magazine-SELECTION-%E6%A0%AA%E5%BC%8F%E4%BC%9A%E7%A4%BE%E3%82%A4%E3%83%B3%E3%83%95%E3%82%A9%E3%83%BC%E3%82%B9/dp/4798117471



重ね重ね図々しくすみません。
もしよかったら何かお教えください。
この度はありがとうございました。

>ちなみに、もしよければ教えて下さい…
状況があいまいなので、難しい質問ですね?

>Accessで運用してきた社内の原価管理システムをＡＤＰに切り替えるべく開発中です。
これが状況だと仮定して、技術用件を推論すると…

1.　いままでACCESSのスタンドアローンシステム(つまりACCESSが直接*.MDBとか*.accdbとかを呼び出す)を利用していた。
2. ADP切り替えて、バックエンドのSQLサーバーをデータソースにしてACCESS上で構築したフォームをそのまま流用する形で再開発したい。

もし、そうだとするとかなりハードルは高いです。ハードルというよりは棒高跳びに近いです。

最悪、システム開発の詳細設計以降を全部やりなおしになるかもしれません。そうなるとSQLサーバーにアクセスをUIにしたほうが早いのか、Webシステムにしてしまったほうが早いのかということになります(世の中はWebシステムにするのが主流なので、そういう本はたくさん見つかるでしょう)

スタンドアローンからADPにすると何が変わるか?といえばシングルユーザーが原則のシステムからマルチユーザーが原則のシステムに変わります。

検索だけなら複数ユーザーが同時に表を読み出してもたいしたことは起きません。しかし、追加・更新・削除のロジックがスタンドアローンのAccess上でコーディング?されていた場合、SQL Serverから見た場合同一性・一貫性を無視してフロントエンドのADPから追加・更新・削除が発行されるとややこしいことが発生します。(更新タイミングの差で一貫性のないデータを吐いたり、表ロックによるエラー返しからデットロックによるフリーズまでさまざまですな)

おそらく、登録・更新系のフォームについているコーディングは全滅かと。

言い換えると、ユーザーが一人しかいなければ(つまり開発中は)、スタンドアローンシステムもADPシステムも違いはないです。しかしユーザーが複数になったとき(つまり実運用で)、ACCESSのコードがどれくらい流用できるのかというのはよく考えたほうがいいでしょう。

それが詳細設計やり直しという意味です。

私が昔、この種のような技術用件に面食らったときは、

A 検索系のフォームはACCESS側にSQLを埋め込むのではなく、SQL Server側のViewでテーブルを結合させ、ACCESS側がわでは単一表を参照しているように見せる。

B 更新系のフォームはデザイン以外全部作り直しで、更新用のコードはSQL Server側にストアドプロシージャで格納し、ACCESS側のボタンはSQL Server側のストアドプロシージャをEXECして、戻ってきたらACCESSの表示画面をrequeryする。

で、逃げました。

簡単にいうと、その手の入門篇教科書に書かれているコードだと、SQL Serverのテーブルのデータをネット越しにクライアントのACCESSにヨッコラショとダウンロードして、更新したデータをヨッコラショとアップロードします。

つまりAccess側でjoinするとボタンを押すたびに、複数テーブルに対してアップロードとダウンロードが発生するのでSQL Serverはとても不幸な目にあいます。

あら金さんへ


お返事ありがとうございます。
入力系のフォームの実験はすでに済んでおりまして、問題なく動きました。
複数名での同一テーブルへの入力作業に関する問題も、先の書籍を参考にしてクリア済です。


＞ACCESSのコードがどれくらい流用できるのかというのはよく考えたほうがいいでしょう。

ご指摘の通り、クエリはすべて書き直しになりました。
カンタンなものであればSQLビューをコピーして流用できるのかもしれないのですが、勉強もかねてすべてコードで書き直しています。

フォーム・レポートについては、入力用・表示or検索用に関わらず、「枠」だけを流用してすべてストアドや関数で書き換えました。
(このたび教えていただいた方法で、すでに書き換えたものもスッキリ手直しさせていただきます)

テーブル構成・正規化については、数年前に実状に合わせてリニューアル済みだったので、そこまでは至らず、データ移行がスムーズでホッとしています。



当社では大半の人間が、検索や一覧表示のみ利用しています。
入力作業をするのは、事務職員が二人と、新規物件を立てる管理職だけです。

だったら何でＳＱＬserverなんかに手出したの？！と言われてしまいそうですが(苦笑)
間接部門の要員が私を含めて人数ギリギリでして、月初・月末に作業が集中するんです。
普段は、ひとつの入力作業（同一のテーブル）に１人しかついていませんが、繁忙期に複数名でやりたくＳＱＬserverに手を出しました。

最初はＳＥの方がサポートしていてくれたのですが、上の方針で取引をやめてしまいまして･･･(^_^;)


「最悪、いつでもAccessに戻せる」ということを前提にしてます。
ADPから既存のMDBに定期的に(１日１回程度)テーブルをインポートして、MDBも残しておく段取りをしております。


素人のやることですので、もうツギハギだらけ･･･といったような感じですが、なんとか完成させようと思っております。

まあ、『この世に、予算がロハのシステム開発ほどさもしきなし』と、もうしますから…お察しします。

>大型の書店でｓｑｌの棚を見ても、どうもしっくりくるものがありません。

SQLのほうは「…逆引き辞典」を活用するのがいいでしょう、大同小異ですから。


今井 聡 (著) 「VB.NET+SQL Server データベースアプリ作成考え方のキモ―DB職人が教える」( http://www.amazon.co.jp/VB-NET-SQL-Server-%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E3%82%A2%E3%83%97%E3%83%AA%E4%BD%9C%E6%88%90%E8%80%83%E3%81%88%E6%96%B9%E3%81%AE%E3%82%AD%E3%83%A2%E2%80%95DB%E8%81%B7%E4%BA%BA%E3%81%8C%E6%95%99%E3%81%88%E3%82%8B-%E4%BB%8A%E4%BA%95/dp/4798022330/ )

なんかはどうでしょうか?

Amazonなか見で目次を見ればなんか納得します。ようするに「プログラマー」戦記ものですね。

欠点はすでに絶版でAMAZON中古でしか入手できません。つまり会社の経費では落ちない、かと(笑)。

9＞しまかよasさん
>>ｓｑｌ文の具体的な記述の上で参考になる書籍は、どういったものがありますか？

書籍でも無いし入門編ではありませんが、一例として以下の Web サイトなんかは勉強にな瑠と思います

リレーショナル・データベースの世界
http://www.geocities.jp/mickindex/database/idx_database.html

あら金さん
たっつぁんさん

オススメ、ありがとうございます。
まだ、チラっとしか見れていないのですが。
また何かあったらご相談させてください。
よろしくお願いします。

この度はありがとうございました。