至急でSQLについて教えていただきたいです。

質問

【至急で知りたいです】アクセス ＳＱＬ文について

アクセス・クエリについて初心者であり、恐縮ではございます。
以下を求めたい時にどのようなＳＱＬで注文をすればよいかご指導いただきたいです。

内容：
・注文テーブル：
列は購入日・顧客コード・顧客種別・年代・レジコード・商品ＮＯ・商品名・購入数・購入金額
2011/05/01 11111 通常 30代 12345 9999 お菓子 1 500
2011/05/01 11111 通常 30代 12345 9998 ご飯 2 1000
2011/05/01 11112 新規 10代 12346 9999 お菓子 1 500

・期間限定割引テーブル
列は商品NO・開始日・終了日
9999 2011/04/10 2011/04/20
9998 2011/05/01 2011/05/10

抽出したい内容
(1)注文テーブルにある購入日で日付を指定の上、（betweenで2010/06/01-2011/05/31までなど）
商品NOを特定の1品目、もしくは複数選択をした上で、その商品に対する期間内の購入数・購入金額を求めたい。
ただし、"期間限定割引テーブル"に差し掛かる期間のみ、別の列で分けて表示させたい。
（つまり割引期間と通常期間を列で分けることで売上の差を見極めたいのです。）

例えば、商品NOを9998で指定した場合の希望内容
購入日 商品No 商品名 通常購入数 割引購入数 通常購入金額 割引購入金額
2011/05/01 9998 ご飯 0 100 0 50000
2011/05/11 9998 ご飯 30 0 24000 0

(2)注文テーブルにある購入日で日付を指定の上、（上記(1)と同様）商品NOを特定の1品目、もしくは複数選択をした上で、
年代別で顧客種別ごとの客数を知りたい場合

例えば、商品NOを9998で指定した場合の希望内容
商品NO年代 リピート 初めて 新規 フリー
9998 10代 2000 500 100 30
9998 20代 3000 1000 200 50
9998 30代 5000 1050 400 100 ← 客数です。

※上の客数は顧客種別かレジコードをカウントすれば良いと思いますが、一人のお客様が一回の会計で5品目買ったらレコードは5つになるため、重複を防ぐ必要があります。（対象の品物を何回購入したことがあるか表示したいので、、）
また、"顧客種別"は通常・新規・フリーの三種類の言葉で分類分けされて登録されてますが、通常のみ、設定した抽出対象期間前にも1回でも購入があれば"リピート"としてカウント、過去に購入なく抽出対象期間内で初めて購入があった場合は"初めて"にカウントしたいです。

複雑かもしれませんが、どなたかご指導をよろしくお願いします

回答No.1

o_chi_chi · ベストアンサー

下記でどうでしょう

(1)集約は商品NOのみ（購入日は通常日か割引日として表示）
---
SELECT
switch(t.購入日 < w.開始日 or t.購入日 > w.終了日,'通常日',true ,'割引日') as 日付
,t.商品NO
,sum(switch(t.購入日 < w.開始日 or t.購入日 > w.終了日,t.購入数,true ,0)) as 通常購入
,sum(switch(t.購入日 < w.開始日 or t.購入日 > w.終了日,0,true ,t.購入数)) as 割引購入
,sum(switch(t.購入日 < w.開始日 or t.購入日 > w.終了日,t.購入金額,true ,0)) as 通常購入金額
,sum(switch(t.購入日 < w.開始日 or t.購入日 > w.終了日,0,true ,t.購入金額)) as 割引購入金額
FROM T_注文 t
INNER JOIN
T_期間限定割引 w
ON t.商品NO = w.商品NO
WHERE t.商品NO = '9998'
and t.購入日 between #2010/06/01# and #2011/05/31#
GROUP BY
t.商品NO,switch(t.購入日 < w.開始日 or t.購入日 > w.終了日,'通常日',true ,'割引日')
---

(2)2011/01/01以前に購入のある人をリピートそうでない人を初めてに振り分け
---
SELECT
t.商品NO
, t.年代
, sum(switch(t.顧客種別='通常' and k.顧客コード is not null, 1 , true ,0)) as リピート
, sum(switch(t.顧客種別='通常' and k.顧客コード is not null, 0 , true ,1)) as 初めて
, sum(switch(t.顧客種別='新規' , 1 , true ,0)) as 新規
, sum(switch(t.顧客種別='フリー', 1 , true ,0)) as フリー
FROM T_注文 t
LEFT JOIN
(SELECT 顧客コード
FROM T_注文
WHERE 購入日<#2011/01/01#) k
ON t.顧客コード = k.顧客コード
WHERE t.購入日=#2011/05/01#
and t.商品NO = '9999'
GROUP BY t.商品NO, t.年代

お礼

回答ありがとうございます。非常に勉強になります。
いただいた文面を早速、明日社内で検証してみたいと思います。
文面を元に自分でも理解できるよう頑張ります。

できればもう一つ伺えれば幸いです。

（２）の質問の際、年齢を軸に各顧客別の客数を求めましたが、
この軸を期間中一人あたり購入個数に置き換える事は可能でしょうか。

例えば
商品NO,期間中の購入個数,リピート,初めて,新規,フリー
9999 1（個）　400　100　30　5
9999 2　　　　 200 30 10 2
9999 3 　　　　100 10 1 0
・・・・・

先日の場合は年代別に客数を求める内容でしたが、
抽出設定期間内の購入個数別に順じた客数です。
（1個買った人が何名いて、、、という形で客層別の特性を掴みたいと考えています、、）
"リピート"や"初めて"の定義は前回と同様です。

よいご提案があれば何卒ご指導をお願いいたします。

補足

再三ですみません。

初心者で大変申し訳ありませんが、略語の意味も
教えていただけると助かります。

恐らくは
T：注文テーブル
W：期間限定割引テーブル
だと思いますが、
（２）の構文に4か所ある"Ｋ"はなにの略になりますでしょうか。

何卒よろしくお願いします。

回答No.5

o_chi_chi

＞期間中一人あたり購入個数に置き換える

(2)のSQLの年代を購入個数に置き換えれば

いけると思いますよ。

回答No.4

o_chi_chi

NO1です。

"k"は

(SELECT 顧客コード
FROM T_注文
WHERE 購入日<#2011/01/01#)

上記のクエリ全体の別名です。

テーブル名.項目名

と書くところを

テーブル as t

と別名をつけると

t.項目名

でアクセルできます。

でテーブルをクエリに置き換えたのが上記です。

ただしわかりやすい別名にしないと

逆にSQLが読みにくくなります。

お礼

ご返信が遅くなり申し訳ありません。

色々と丁寧なご説明ありがとうございました。
非常にうれしいです。
昨日会社で不慣れながら入力し試した上で
基本的な仕組みも少しずつですが理解できてきました。

またご質問をさせていただくこともあるかもしれませんが、
その時は何卒よろしくお願いします。

回答No.3

OrangeCup150

■(1)
select r.購入日, r.商品ＮＯ, r.商品名,
sum(IIF(r.購入日 not between ts.開始日 AND 終了日, r.購入数, null)) as 通常購入数,
sum(IIF(r.購入日 not between ts.開始日 AND 終了日, r.購入金額, null)) as 通常購入金額,
sum(IIF(r.購入日 between ts.開始日 AND 終了日, r.購入数, null)) as 割引購入数,
sum(IIF(r.購入日 between ts.開始日 AND 終了日, r.購入金額, null)) as 割引購入金額
from 注文テーブル as r left outer join 期間限定割引テーブル as ts on r.商品ＮＯ = ts.商品ＮＯ
where r.購入日 between #06/01/10# AND #05/31/11#
AND r.商品ＮＯ in ('9999', '9998')
group by r.購入日, r.商品ＮＯ, r.商品名
order by r.購入日, r.商品ＮＯ

■(1)日付種別を加えた場合
select r.購入日, r.商品ＮＯ, r.商品名,
IIF(r.購入日 not between ts.開始日 AND 終了日, '通常', '割引') as 日付種別,
sum(IIF(r.購入日 not between ts.開始日 AND 終了日, r.購入数, null)) as 通常購入数,
sum(IIF(r.購入日 not between ts.開始日 AND 終了日, r.購入金額, null)) as 通常購入金額,
sum(IIF(r.購入日 between ts.開始日 AND 終了日, r.購入数, null)) as 割引購入数,
sum(IIF(r.購入日 between ts.開始日 AND 終了日, r.購入金額, null)) as 割引購入金額
from 注文テーブル as r left outer join 期間限定割引テーブル as ts on r.商品ＮＯ = ts.商品ＮＯ
where r.購入日 between #06/01/10# AND #05/31/11#
AND r.商品ＮＯ in ('9999', '9998')
group by r.購入日, r.商品ＮＯ, r.商品名, IIF(r.購入日 not between ts.開始日 AND 終了日, '通常', '割引')
order by r.購入日, r.商品ＮＯ

こういうとき、知名度の低い not between が役に立ちます。

■(?)商品、購入数別の集計
select r.商品ＮＯ, r.商品名,r.購入数,
count(IIF(r.顧客種別='通常' AND r2.顧客コード is not null,1,null)) as リピート,
count(IIF(r.顧客種別='通常' AND r2.顧客コード is null,1,null)) as はじめて,
count(IIF(r.顧客種別='新規', 1, null)) as 新規,
count(IIF(r.顧客種別='フリー', 1, null)) as フリー
from 注文テーブル as r left outer join (select 顧客コード from 注文テーブル where 購入日 < #06/01/10#) as r2 on r.顧客コード=r2.顧客コード
where r.購入日 between #06/01/10# AND #05/31/11#
AND r.商品ＮＯ in ('9999', '9998')
group by r.商品ＮＯ,r.商品名,r.購入数
order by r.商品ＮＯ,r.商品名,r.購入数

多分、こんなとろこですね。

>初心者で大変申し訳ありませんが、略語の意味も
> 教えていただけると助かります。
ＳＱＬの構文について

テーブル名、クエリは別名を指定できます。
処理速度が良くなるので一般的に好んで使われます。

構文: テーブル名 as 別名
例文: 注文テーブル as r

構文: テーブル名 別名
例文: 注文テーブル r

構文: クエリ as 別名
例文: (select 顧客コード from 注文テーブル) as r2

詳細は、Access のヘルプの クエリ → Access SQL: 基本的な概念、用語、および構文 を一読することをおすすめします。

参考：複数商品を選択的に指定する場合には下のように表もどきを使用するといいかもしれません。
create table para(商品ＮＯ text(4) not null);
insert into para(商品ＮＯ) values('9999');
insert into para(商品ＮＯ) values('9998');
insert into para(商品ＮＯ) values('9997');

AND r.商品ＮＯ in (select 商品ＮＯ from para)

お礼

早急なご返信、非常に助かります！
お忙しいところありがとうございました！嬉しいです。

未経験のＳＱＬを使わないと進まないような業務で
そんな職場内では他に知識がある人がおらず質問ができないため
八方塞の状態でした、、、

早速今日職場で試してみます。

回答No.2

o_chi_chi

NO１です。

ちょっとミスに気がつきました。

誤り:
, sum(switch(t.顧客種別='通常' and k.顧客コード is not null, 0 , true ,1)) as 初めて

正しい
, sum(switch(t.顧客種別='通常' and k.顧客コード is null, 1 , true ,0)) as 初めて

ほかにもミスあるかもしれません

お礼

昨日に色々試してみた結果、少し不具合があったので
良い方法があれば教えていただけると助かります。

注文テーブルを以下で仮定して試してみました。

購入日 レジコード　顧客コード　顧客種別　商品NO 購入金額　購入個数　年代
2011/01/01　　　　111111 123456 通常 100 ¥100 1 20代
2011/01/02 111112 129999 通常 100 ¥300 3 50代
2011/01/02 111112 129999 通常 200 ¥200 1 50代
2011/01/02 111112 129999 通常 500 ¥500 2 50代
2011/02/01 111113 122222 新規 100 ¥100 1 90代
2011/02/05 111114 133333 新規 400 ¥400 1 10代
2011/02/11 111115 144444 通常 1000 ¥1,000 1 30代
2011/02/11 111115 144444 通常 100 ¥100 1 30代
2011/03/01 111116 155555 新規 100 ¥300 3 20代
2011/03/01 111116 155555 新規 200 ¥400 2 20代
2011/03/05 111117 166666 通常 500 ¥250 1 30代
2011/03/15 111118 177777 通常 400 ¥800 2 40代
2011/03/16 111119 188888 通常 100 ¥100 1 50代
2011/03/16 120000 123456 通常 100 ¥1,000 10 20代
2011/03/16 120000 123456 通常 400 ¥400 1 20代
2011/03/16 120000 123456 通常 200 ¥400 2 20代
2011/03/30 121000 144444 通常 100 ¥300 3 30代
2011/03/30 121000 144444 通常 1000 ¥3,000 3 30代
2011/03/30 121000 144444 通常 500 ¥750 3 30代

(1)注文テーブルにある購入日で日付を指定の上、（betweenで2010/06/01-2011/05/31までなど）
商品NOを特定の1品目、もしくは複数選択をした上で、その商品に対する期間内の購入数・購入金額を求めたい。

これを以下の構文で試してみました。

SELECT t.購入日, t.商品NO,
Sum(iif(t.購入日<w.開始日 or t.購入日>w.終了日,t.購入個数,0)) AS 通常購入,
Sum(Switch(t.購入日<w.開始日 Or t.購入日>w.終了日,0,True,t.購入個数)) AS 割引購入,
Sum(Switch(t.購入日<w.開始日 Or t.購入日>w.終了日,t.購入金額,True,0)) AS 通常購入金額,
Sum(Switch(t.購入日<w.開始日 Or t.購入日>w.終了日,0,True,t.購入金額)) AS 割引購入金額
FROM 注文テーブル AS t INNER JOIN 割引テーブル AS w ON t.商品NO = w.商品NO
WHERE (((t.商品NO)=100) AND ((t.購入日) Between #1/1/2011# And #3/30/2011#))
GROUP BY t.購入日, t.商品NO;

実行結果

購入日 商品NO 通常購入　割引購入　通常購入金額 割引購入金額
2011/01/01 100 1 1 ¥100 ¥100　　　　　
2011/01/02 100 3 3 ¥300 ¥300　　　　　
2011/02/01 100 2 0 ¥200 ¥0
2011/02/11 100 2 0 ¥200 ¥0
2011/03/01 100 3 3 ¥300 ¥300
2011/03/16 100 11 11 ¥1,100 ¥1,100
2011/03/30 100 3 3 ¥300 ¥300

上記のようになりました。
ちなみに割引テーブルは以下の通りです。

商品NO 商品名 開始日 終了日
300 ごはん 2011/03/01 2011/03/30
300 ごはん 2011/01/01 2011/01/31

割引テーブル内で一つの商品に対し、複数割引期間が設定されております。

この割引テーブルで仮定すると、欲しい結果は2月の"通常購入"と"通常購入数"にその時の売り上げを出したいと考えております。
現在の結果の場合、割引テーブルで設定された割引期間内の"通常購入"と"通常購入数"にも数字が入ってしまっています、、。

（2)注文テーブルにある購入日で日付を指定の上、（上記(1)と同様）商品NOを特定の1品目、もしくは複数選択をした上で、年代別で顧客種別ごとの客数を知りたい場合

※上の客数は顧客種別かレジコードをカウントすれば良いと思いますが、一人のお客様が一回の会計で5品目買ったらレコードは5つになるため、重複を防ぐ必要があります。（対象の品物を何回購入したことがあるか表示したいので、、）
また、"顧客種別"は通常・新規・フリーの三種類の言葉で分類分けされて登録されてますが、通常のみ、設定した抽出対象期間前にも1回でも購入があれば"リピート"としてカウント、過去に購入なく抽出対象期間内で初めて購入があった場合は"初めて"にカウントしたいです。

これを以下構文で試してみました。

SELECT
t.商品NO
, t.年代
, sum(switch(t.顧客種別='通常' and k.顧客コード is not null, 1 , true ,0)) as リピート
, sum(switch(t.顧客種別='通常' and k.顧客コード is not null, 0 , true ,1)) as 初めて
, sum(switch(t.顧客種別='新規' , 1 , true ,0)) as 新規
, sum(switch(t.顧客種別='フリー', 1 , true ,0)) as フリー
FROM 注文テーブル t
LEFT JOIN
(SELECT 顧客コード
FROM 注文テーブル
WHERE 購入日<#2011/02/28#) k
ON t.顧客コード = k.顧客コード
WHERE t.購入日 between #2011/03/01# and #2011/03/31#
and t.商品NO = １00
GROUP BY t.商品NO, t.年代

抽出結果

商品NO 年代 リピート 初めて 新規 フリー
100 20代 1 1 1 0
100 30代 2 0 0 0
100 50代 0 1 0 0

たとえば、3月1日～3月31日までの期間で抽出をすると
商品NO100の商品を買った30代のリピートの方は1名（顧客NO144444）だけに
なると思いますが、抽出結果では2名になってしまいます。

・対象の商品"100"を設定した対象期間内（上記の構文でいうと2011/03/01-2011/03/31)
でしか購入がなかった場合は"初めて"としてカウント

・対象の商品"100"を設定した対象期間内（上記の構文でいうと2011/03/01-2011/03/31)
で購入があり、且つその設定日付以前にも同じ商品の購入があった場合は"リピート"として
カウント

というのが出来ないか頑張っています、、。

お手数をおかけしますが、なにとぞご教授をお願いいたします。