お世話になります。
このたび今後の業務に必要な為、SQLを勉強することになりました。そこでこのような問題が出たのですがどうも理解できません。
【商品マスタ】
商品コード 商品名 単価
0001 ボール 100
0002 バット 200
0003 グローブ 300
【仕入先マスタ】
仕入先コード 仕入先
001 A
002 B
003 C
【トラン】
購入番号 商品コード 仕入先コード 数量
1 0001 001 1
2 0001 002 2
3 0001 003 3
4 0001 001 4
5 0001 002 5
6 0002 003 6
7 0002 001 7
8 0002 002 8
9 0003 003 9
10 0003 001 10
11 0004 004 20
上記の商品、仕入先、トランのテーブルから以下のように出力をするものです。
商品コード、商品名、仕入先コード、仕入先名、数量、単価、金額
0001 ボール 001 A 5 100 500
0001 ボール 002 B 7 100 700
0001 ボール 003 C 3 100 300
0002 バット 001 A 7 200 1400
0002 バット 002 B 8 200 1600
0002 バット 003 C 6 200 1200
0003 グローブ 001 A 10 300 3000
0003 グローブ 003 C 9 300 2700
0004 (null) 004 (null) 20 (null) (null)
ネットを見てもいまいちピンと来ず行き詰っています。ヒント等ありましたらよろしくお願い致します。
A 回答 (6件)
- 最新から表示
- 回答順に表示
No.5
- 回答日時:
#1 です。
#1 の例1は、実は、PostgreSQL7.3で確認したものです。(テーブル名、カラム名は半角英数にしました)
ちゃんとANSI SQLに準拠しているかは、ちょっと自信ないです。
ちなみに例2(Oracle風)は、実行環境が無いので動作確認してません。
大きな間違いは無いと思いますが。
質問者さんはDBMSを明示されていませんが、このカテゴリ=「その他(データベース)」だと、Access の可能性が高いですね。
今までの回答で解決されましたでしょうか?
No.4
- 回答日時:
>#3どの
確認しました。
ほんとですね。
??
最初に試した時、何がいけなかったんだろう?
1つ賢くなりました、ありがとうございます。
質問者を混乱させたならすみませんでした。
No.3
- 回答日時:
>#2どの
#1の書かれた一般的な(ANSI構文の)SQLは、ACCCESS流の表記にするだけで、
特に問題なく動作しますよ。
(というか、これしきのクエリが処理できないようでは困る)
SELECT
トラン.商品コード,
商品マスタ.商品名,
トラン.仕入先コード,
仕入先マスタ.仕入先,
Sum(トラン.数量) AS 数量,
商品マスタ.単価,
Sum(トラン.数量)*商品マスタ.単価 AS 金額
FROM
(トラン LEFT JOIN 商品マスタ ON トラン.商品コード = 商品マスタ.商品コード)
LEFT JOIN 仕入先マスタ ON トラン.仕入先コード = 仕入先マスタ.仕入先コード
GROUP BY
トラン.商品コード,
商品マスタ.商品名,
トラン.仕入先コード,
仕入先マスタ.仕入先,
商品マスタ.単価
;
なお、ACCESS2002で確認しました。
No.2
- 回答日時:
アクセスで#1の方の一般的なSQLがうまくいかなかったので、私なりに、やってみました。
アクセスでは、SUMとGROUP BYがうまくいかないので、
最初にトランを商品コードと仕入先コードで数量の和を求めておきます(クエリ:ビュー)
SELECT 商品コード, 仕入先コード, SUM(数量) AS 集約数量
FROM トラン
GROUP BY 商品コード, 仕入先コード;
あとは、この集計済みトランについて表の結合を行います。
SELECT T.商品コード, SY.商品名, T.仕入先コード, SI.仕入先 AS 仕入先名, T.集約数量 AS 数量, SY.単価, (T.集約数量*SY.単価) AS 金額
FROM (数量集約 AS T LEFT JOIN 商品 AS SY ON T.商品コード=SY.商品コード) LEFT JOIN 仕入先 AS SI ON T.仕入先コード=SI.仕入先コード;
段階を分けているだけでやってることは同じです。
No.1
- 回答日時:
条件は、下記の様になるのでしょうか?
1.【トラン】テーブルと【商品マスタ】テーブルの「商品コード」項目が同じものを結合。
ただし、【商品マスタ】テーブルに該当するレコードが無くても、【トラン】テーブルのレコードは全て出力。
2.【トラン】テーブルと【仕入先マスタ】テーブルの「仕入先コード」項目が同じものを結合。
ただし、【仕入先マスタ】テーブルに該当するレコードが無くても、【トラン】テーブルのレコードは全て出力。
3.「商品コード」と「仕入先コード」が同一のものは、一つにまとめる。
4.【商品マスタ】の「単価」と【トラン】の「数量」(同一の商品・仕入先の合計)から金額を算出。
テーブル同士の結合は、『JOIN』句、または、『WHERE』句で条件を記述します。
また、一方のテーブルは全てのレコードを出力するため、外部結合をする必要が有ります。
http://www.atmarkit.co.jp/fnetwork/rensai/sql06/ …
http://www.techscore.com/tech/sql/06_02.html
複数のレコードを一つにまとめるには、『GROUP BY』句を使用します。
出力する項目で集約関数を使うもの以外は、全て『GROUP BY』句に記述する必要が有ります。
(DBMSによって、例外あり)
ご質問の場合は、まとめる条件は「トラン.商品コード」「トラン.仕入先コード」だけですが、「商品名」などの他の出力項目も『GROUP BY』句に記述します。
http://www.atmarkit.co.jp/fnetwork/rensai/sql03/ …
http://www.techscore.com/tech/sql/05_08.html
金額を算出するには、集約関数のSUM()を使って「数量」の合計を算出し、「単価」と掛け合わせます。
列名を付けたい場合は、「AS」句を使用すると良いでしょう。
http://www.techscore.com/tech/sql/05_07.html
一般的なSQLでは、下記の様になると思います。
例1-------------------------------------------------------------------------
SELECT
トラン.商品コード,
商品マスタ.商品名,
トラン.仕入先コード,
仕入先マスタ.仕入先名,
SUM(トラン.数量) AS 数量,
商品マスタ.単価,
商品マスタ.単価 * SUM(トラン.数量) AS 金額
FROM トラン
LEFT OUTER JOIN 商品マスタ ON (トラン.商品コード = 商品マスタ.商品コード)
LEFT OUTER JOIN 仕入先マスタ ON (トラン.仕入先コード = 仕入先マスタ.仕入先コード)
GROUP BY
トラン.商品コード,
商品マスタ.商品名,
トラン.仕入先コード,
仕入先マスタ.仕入先名,
商品マスタ.単価
----------------------------------------------------------------------------
例2 Oracle風----------------------------------------------------------------
SELECT
トラン.商品コード,
商品マスタ.商品名,
トラン.仕入先コード,
仕入先マスタ.仕入先名,
SUM(トラン.数量) AS 数量,
商品マスタ.単価,
商品マスタ.単価 * SUM(トラン.数量) AS 金額
FROM トラン, 商品マスタ, 仕入先マスタ
WHERE トラン.商品コード = 商品マスタ.商品コード(+) AND トラン.仕入先コード = 仕入先マスタ.仕入先コード(+)
GROUP BY
トラン.商品コード,
商品マスタ.商品名,
トラン.仕入先コード,
仕入先マスタ.仕入先名,
商品マスタ.単価
----------------------------------------------------------------------------
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- 経営情報システム accessでの請求管理について 12 2022/06/11 16:20
- Visual Basic(VBA) EXCEL関数LOOKUPとFILTERについての質問です 1 2022/12/21 05:53
- その他(Microsoft Office) Excelで総数量を変動させたい 2 2022/11/04 23:49
- Excel(エクセル) VBA でvlookup エラーなどは削除したい 8 2022/12/30 04:03
- Visual Basic(VBA) 【VBA】複数行あるカンマ区切りのデータを全て縦に一列に並べたい 5 2022/04/13 17:03
- その他(Microsoft Office) EXCEL VLOOKUPに関する質問 5 2023/02/08 11:38
- Excel(エクセル) エクセル count関数で出てきた数だけの行数をリンクで参照する 3 2022/08/10 12:02
- Excel(エクセル) ある文字を含む際に、値を返す数式についてです 5 2022/08/28 16:58
- その他(ビジネス・キャリア) スポット取引とは? 1 2023/04/06 15:23
- その他(買い物・ショッピング) JANコードの登録について 1 2022/07/23 14:19
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
EXISTSを使ったDELETE文
-
前月の取得について
-
SQL文で、合計が0のレコードを...
-
アクセス★非課税、課税の合計金...
-
ACCESSでクエリ作成時複数のフ...
-
日付により変動する「単価」を...
-
各部署、各チーム、個人ごとの...
-
Accessの実行時エラーについて
-
はじめまして。スーパーbeginne...
-
Accessのテーブル構成について
-
表の結合の仕方
-
ACCESS ツリービューの作り方
-
inner join,left joi...
-
差し込み後、元データを変更し...
-
SELECT 文 GROUP での1件目を...
-
エクセルで最後の文字だけ置き...
-
1、Rstudioで回帰直線を求める...
-
INSERT文でフィールドの1つだ...
-
処理件数を非表示にしたい
-
for whichの使い方
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
EXISTSを使ったDELETE文
-
SQL文で、合計が0のレコードを...
-
Accessの実行時エラーについて
-
ACCESS ツリービューの作り方
-
日付により変動する「単価」を...
-
異なるデータベース間のテーブ...
-
前月の取得について
-
アクセス フィールド名に変数...
-
ACCESSでクエリ作成時複数のフ...
-
DBが複雑すぎる
-
ACCESS 商品毎の最新の単価を設...
-
最大値を含むレコードの抽出
-
Access2013で商品に複数の単価...
-
Excel VBA ADODB
-
アクセスにて月末日付を取得し...
-
ACCESSでDLookUpすると、不正な...
-
フィールドの数値を四捨五入の...
-
店舗での日報での良い例を教え...
-
Excelのユーザーフォームで別の...
-
SQLserverでのUPDATE文について
おすすめ情報