下記の目的を満たすSQLの構文を教えてください。

Question

nao14

2

1もっと見る

コンピュータ学習・教育

下記の目的を満たすSQLの構文を教えてください。

【目的】
客がある時間に買った商品と価格のレコードが入ったテーブルAがあり
(※1人のレコードは複数)、
その中から各客のレコードを一つに絞り込みたい(客1名につき1レコードに)。
絞込みの条件は、一番高い価格が入ったレコードであること。
1人に同価格が存在する場合は、そのうちいずれか。
※同一時間に複数商品をレジに通せるので、1人で同時間に複数レコードあり。

【例】　
(ID)　　　(日付)　　　　(商品)　(価格)
ggggg　2000/1/1/0：00　　商品A　￥500
ggggg　2000/1/1/0：00　　商品B　￥300
ggggg　2000/1/1/0：00　　商品C　￥200
eeeee　2002/3/1/0：00　　商品A　￥300
eeeee　2002/3/1/0：00　　商品B　￥400
eeeee　2002/3/1/0：00　　商品C　￥400

★この場合
ggggg　2000/1/1/0：00　　商品A　￥500
eeeee　2002/3/1/0：00　　商品B　￥400
または、
ggggg　2000/1/1/0：00　　商品A　￥500
eeeee　2002/3/1/0：00　　商品C　￥400
が結果として選択されるように。

何日か考えたのですが答えが出ず、お知恵をお借りしたいです。
よろしくお願いします。

回答の条件

1人50回まで

登録：2012/06/12 12:55:44
終了：2012/06/13 16:17:10

No.1

JULY9662472012/06/12 13:15:48

４つのフィールドが id, date, item, price という名前で、テーブル名が table1 だったとして、

select id, date, item, max(price) from table1 group by id

他2件のコメントを見る

group byとselect句の関係を勘違いしていたようですみません。
ただ、頂いた構文のままだと実行できず、
group by以下にすべてのカラムを入れると実行できるので、
てっきりそういう仕組みかと。。。
他の原因があるようですね。試行錯誤してみます。

2012/06/12 17:12:03

本題とは関係ないことですが、双方話が食い違っているので横槍失礼します。

＞select id, date, item, max(price) from table1 group by id
回答欄のような表記のdateやitemに対して、
MySQLは適当に処理してくれるのですが、
他のデータベースでは曖昧さ回避のためエラーになります。
すなわちJULYさんの回答ならびに返答はMySQLにおいてのみ有効となるものです。

今回の件はAccessであるとコメントに記載されているので、
nao14さんが認識しておられたSELECTとGROUP BYの関係は間違いではありません。

なお、私の回答は別の回答欄に書くこととしますので、そちらを参照してください。

2012/06/12 21:55:52

きゃづみぃ 2012/06/12 13:10:33

http://oshiete.goo.ne.jp/qa/1792963.html

これに似てるね。

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

リクエスト送信済

回答リクエストを送信したユーザーはいません

うぃんど · Accepted Answer · 2012-06-12T22:02:14+09:00

No.2

うぃんど262511492012/06/12 22:02:14

１件ずつ抽出したいならば、
レコードをユニークにする必要がありますので、
例えば下記のような具合に番号を振ります。
（テーブルにオートナンバー型フィールドを追加するだけならば、
　フォームやレポートなどの改造をしなくて済むので楽ですよ。）

ID	日付	商品	価格	通し番号
ggggg	2000/01/01	商品A	500	1
ggggg	2000/01/01	商品B	300	2
ggggg	2000/01/01	商品C	200	3
eeeee	2000/01/03	商品A	300	4
eeeee	2000/01/03	商品B	400	5
eeeee	2000/01/03	商品C	400	6
eeeee	2000/01/03	商品D	400	7

クエリの例

SELECT ID, 日付, 商品, 価格
FROM テーブル AS t1
WHERE 通し番号 = (SELECT TOP 1 通し番号 FROM テーブル AS t2 WHERE t2.ID = t1.ID ORDER BY 価格 DESC, 通し番号);

他9件のコメントを見る

フォローありがとうございます。

でも、いまいちピンと来ないのですが、下記のページだと、Access を使って私の書いたようなクエリーでも行けそうに見えます。
http://www.netlaputa.ne.jp/~mkoba/sql/sql_05.html

> レコードをユニークにする必要がありますので、

ひょっとしたら、主キーの有無とかで変わってくるのでしょうか？

あと、下記のページは ADODB 経由で VBA から実行した場合のようですが、その辺の違いとかもあるのでしょうか。
http://www.accessclub.jp/sql/17.html

2012/06/13 10:01:25

>http://www.netlaputa.ne.jp/~mkoba/sql/sql_05.html
私の経験からですが、
select句には、
①group byで指定したカラム
②集約関数(max、min、countなど)付きのカラム
を入れないと実行されませんでした。
group byで指定しないカラムを、
集約関数も付けずにselect句に入れると、エラーになります。
回答ずれてたらすみません、無視してください。。。

2012/06/13 10:19:57

>windofjulyさま
クエリ例ありがとうございます。これ、試してみます！

2012/06/13 10:23:01

＞行けそうに見えます。
＞http://www.netlaputa.ne.jp/~mkoba/sql/sql_05.html

SQLの基礎を学んでください。
そのページでもGROUP BYに出てきていないものをSELECTに書いたりはしてません。

GROUP BY 句で指示したフィールドは、
そのままSELECT句に書けます。
GROUP BY 句で指示していないフィールドは、
SELECT句で集約関数を使って集計したりできます。
（例外的にMySQLは独自判断の結果を返す場合があり、
　トラブルの種になる場合があります。）

JULYさんの回答をもう一度持ち出します。
select id, date, item, max(price) from table1 group by id

１）group by id
たとえば、idがgggggの場合は下記３レコードが１つのグループとして扱われます。
ggggg　2000/1/1/0：00　　商品A　￥500
ggggg　2000/1/1/0：00　　商品B　￥300
ggggg　2000/1/1/0：00　　商品C　￥200

２）select id　-　GROUP BYで指示されている項目
同じグループに属していれば、
どのレコードから取ってきてもidはgggggですから結果はgggggです。

３）max(price) - 集約関数
最大の値ですから答えは500です。

４）time、item
あれ？　どのレコードから持ってくればいいのですか？

ここで、MySQLは偶然最初に見つかった値を持ってきますが、
多くのデータベースは曖昧さ回避のため、この時点でエラーになります。

Accessでも偶然最初に見つかった値を持ってこさせることはできますが、
偶然に頼ったものなので、当然ながら関係無い情報を持ってくる可能性もあり、
今回のような場合には使いません。ちなみにFirst関数です。

＞主キーの有無とかで変わってくるのでしょうか？
＞ADODB 経由

今回の件にはどちらも関係ありません。

nao14さんの 2012/06/13 10:19:57 の認識は正しいです。

2012/06/13 10:41:45

あぁ、今分かった！ありがとうございます！
気がついてしまえば、「何してんね、このアホ＞自分」ですね(^^;。

2012/06/13 11:02:44

「SQLの基礎を学んでください。」なんて・・・失礼なこと書いちゃいました。ごめんなさい。

2012/06/13 11:11:19

>windofjulyさま

頂いたクエリで実行でき、欲しい内容が抽出できました。
本当にありがとうございます。

勉強のために教えて欲しいのですが。

(SELECT TOP 1 通し番号 FROM テーブル AS t2 WHERE t2.ID = t1.ID ORDER BY 価格 DESC, 通し番号)
頂いた()内の構文だけ、自分なりの翻訳ができません。

大きな意味では、
｢価格の降順に並べた状態で一番上の通し番号を取って来い｣
だと思うのですが、
()内の、t2・where・orderbyの文脈の順番が捉えきれないです。

(
SELECT TOP 1 通し番号
FROM テーブル
)AS t2
WHERE t2.ID = t1.ID
ORDER BY 価格 DESC
↑こういう意味の区切りでいいんでしょうか？

でもそう区切っても、
意味の通る文章にならないというか文脈がわからないです。

t2の生成段階で条件にt2が入っている、ような矛盾に見えてしまい。。。
理解度が話にならないレベルであれば、一旦諦めて暗記しますwww

お手間でなければ教えてくださいませ。

2012/06/13 12:25:36

エイリアス（テーブル AS t1 とテーブル AS t2）で判りにくければ、
テーブルt1をコピーしてテーブルt2を作ったと想定してみると良いです。

SELECT ID, 日付, 商品, 価格
FROM t1
WHERE 通し番号 = (SELECT TOP 1 通し番号 FROM t2 WHERE t2.ID = t1.ID ORDER BY 価格 DESC, 通し番号);

WHEREはt1から1レコード読み込む度に下記のような判定を行うことで、
必要なレコードだけを抽出しています。

t1の「ggggg, 2000/01/01,商品A,500,1」に対しては、下記のような式を評価します。

WHERE 1 = (SELECT TOP 1 通し番号 FROM t2 WHERE t2.ID = ggggg ORDER BY 価格 DESC, 通し番号)

この式の結果は真となりますので、SELECTで指示されたとおりに出力します。

t1の「eeeee,2000/01/03,商品A,300,4」に対しては、下記のような式を評価します。

WHERE 5 = (SELECT TOP 1 通し番号 FROM t2 WHERE t2.ID = eeeee ORDER BY 価格 DESC, 通し番号)

この式の結果は偽となりますので、このレコードはスルーします（＝出力しません）

2012/06/13 14:04:44

ご丁寧にありがとうございます。
エイリアスの事は何とか飲み込めたと思うのですが、
引っ掛かっているのは、order by のかかり方でした。

(
SELECT TOP 1 通し番号　…①
FROM t2 　…①
WHERE t2.ID = t1.ID　　…①
ORDER BY 価格 DESC　　…②
)

普通だとorder byは全体にかかるので、
①を実行した後に②の並び替えをする、というイメージを持っています。

ただ、TOPという関数(？)が入ると、
②の並び替えをした上で①を実行するようになるのですか？

自分でも調べてみたのですが、ちょっとわかりませんでした。
混乱しており、すみません。

2012/06/13 15:29:38

丸囲み数字はメールのほうでは？になるので、
使わないほうがいいみたいです。

さて、
動作を言葉にすると次のような具合です。
１．FROM に示されたテーブルから、
２．WHERE の条件にあうレコードだけを、
３．ORDER BY の順で並び変えて、
４．SELECT に示されたもの（通し番号）を出力しろ
　　ただし、TOP 1（1番目だけ）でいい

今回の場合は以上のような流れになってますが、
ちょっと変えただけで流れが大きく変わる場合もあります。

このような動作の詳細についてはAccess関連の本にはほとんど載ってません。
（Accessの操作方法までで終わってますよね）

SQL入門系とかデータベース概論系の本を読んでSQLの基礎を学び、
さらに、Access独特のクセを覚えて対応してくという流れになりますので、
あせらず、ゆっくり時間をかけて学んでください。

2012/06/13 15:59:30

ご回答ありがとうございます。

SQLを基礎から学びつつ実践していきたいと思います！！！

2012/06/13 16:16:59

うぃんど · Accepted Answer · 2012-06-12T22:02:14+09:00

No.2

うぃんど262511492012/06/12 22:02:14ここでベストアンサー

１件ずつ抽出したいならば、
レコードをユニークにする必要がありますので、
例えば下記のような具合に番号を振ります。
（テーブルにオートナンバー型フィールドを追加するだけならば、
　フォームやレポートなどの改造をしなくて済むので楽ですよ。）

ID	日付	商品	価格	通し番号
ggggg	2000/01/01	商品A	500	1
ggggg	2000/01/01	商品B	300	2
ggggg	2000/01/01	商品C	200	3
eeeee	2000/01/03	商品A	300	4
eeeee	2000/01/03	商品B	400	5
eeeee	2000/01/03	商品C	400	6
eeeee	2000/01/03	商品D	400	7

クエリの例

SELECT ID, 日付, 商品, 価格
FROM テーブル AS t1
WHERE 通し番号 = (SELECT TOP 1 通し番号 FROM テーブル AS t2 WHERE t2.ID = t1.ID ORDER BY 価格 DESC, 通し番号);

他9件のコメントを見る

フォローありがとうございます。

でも、いまいちピンと来ないのですが、下記のページだと、Access を使って私の書いたようなクエリーでも行けそうに見えます。
http://www.netlaputa.ne.jp/~mkoba/sql/sql_05.html

> レコードをユニークにする必要がありますので、

ひょっとしたら、主キーの有無とかで変わってくるのでしょうか？

あと、下記のページは ADODB 経由で VBA から実行した場合のようですが、その辺の違いとかもあるのでしょうか。
http://www.accessclub.jp/sql/17.html

2012/06/13 10:01:25

>http://www.netlaputa.ne.jp/~mkoba/sql/sql_05.html
私の経験からですが、
select句には、
①group byで指定したカラム
②集約関数(max、min、countなど)付きのカラム
を入れないと実行されませんでした。
group byで指定しないカラムを、
集約関数も付けずにselect句に入れると、エラーになります。
回答ずれてたらすみません、無視してください。。。

2012/06/13 10:19:57

>windofjulyさま
クエリ例ありがとうございます。これ、試してみます！

2012/06/13 10:23:01

＞行けそうに見えます。
＞http://www.netlaputa.ne.jp/~mkoba/sql/sql_05.html

SQLの基礎を学んでください。
そのページでもGROUP BYに出てきていないものをSELECTに書いたりはしてません。

GROUP BY 句で指示したフィールドは、
そのままSELECT句に書けます。
GROUP BY 句で指示していないフィールドは、
SELECT句で集約関数を使って集計したりできます。
（例外的にMySQLは独自判断の結果を返す場合があり、
　トラブルの種になる場合があります。）

JULYさんの回答をもう一度持ち出します。
select id, date, item, max(price) from table1 group by id

１）group by id
たとえば、idがgggggの場合は下記３レコードが１つのグループとして扱われます。
ggggg　2000/1/1/0：00　　商品A　￥500
ggggg　2000/1/1/0：00　　商品B　￥300
ggggg　2000/1/1/0：00　　商品C　￥200

２）select id　-　GROUP BYで指示されている項目
同じグループに属していれば、
どのレコードから取ってきてもidはgggggですから結果はgggggです。

３）max(price) - 集約関数
最大の値ですから答えは500です。

４）time、item
あれ？　どのレコードから持ってくればいいのですか？

ここで、MySQLは偶然最初に見つかった値を持ってきますが、
多くのデータベースは曖昧さ回避のため、この時点でエラーになります。

Accessでも偶然最初に見つかった値を持ってこさせることはできますが、
偶然に頼ったものなので、当然ながら関係無い情報を持ってくる可能性もあり、
今回のような場合には使いません。ちなみにFirst関数です。

＞主キーの有無とかで変わってくるのでしょうか？
＞ADODB 経由

今回の件にはどちらも関係ありません。

nao14さんの 2012/06/13 10:19:57 の認識は正しいです。

2012/06/13 10:41:45

あぁ、今分かった！ありがとうございます！
気がついてしまえば、「何してんね、このアホ＞自分」ですね(^^;。

2012/06/13 11:02:44

「SQLの基礎を学んでください。」なんて・・・失礼なこと書いちゃいました。ごめんなさい。

2012/06/13 11:11:19

>windofjulyさま

頂いたクエリで実行でき、欲しい内容が抽出できました。
本当にありがとうございます。

勉強のために教えて欲しいのですが。

(SELECT TOP 1 通し番号 FROM テーブル AS t2 WHERE t2.ID = t1.ID ORDER BY 価格 DESC, 通し番号)
頂いた()内の構文だけ、自分なりの翻訳ができません。

大きな意味では、
｢価格の降順に並べた状態で一番上の通し番号を取って来い｣
だと思うのですが、
()内の、t2・where・orderbyの文脈の順番が捉えきれないです。

(
SELECT TOP 1 通し番号
FROM テーブル
)AS t2
WHERE t2.ID = t1.ID
ORDER BY 価格 DESC
↑こういう意味の区切りでいいんでしょうか？

でもそう区切っても、
意味の通る文章にならないというか文脈がわからないです。

t2の生成段階で条件にt2が入っている、ような矛盾に見えてしまい。。。
理解度が話にならないレベルであれば、一旦諦めて暗記しますwww

お手間でなければ教えてくださいませ。

2012/06/13 12:25:36

エイリアス（テーブル AS t1 とテーブル AS t2）で判りにくければ、
テーブルt1をコピーしてテーブルt2を作ったと想定してみると良いです。

SELECT ID, 日付, 商品, 価格
FROM t1
WHERE 通し番号 = (SELECT TOP 1 通し番号 FROM t2 WHERE t2.ID = t1.ID ORDER BY 価格 DESC, 通し番号);

WHEREはt1から1レコード読み込む度に下記のような判定を行うことで、
必要なレコードだけを抽出しています。

t1の「ggggg, 2000/01/01,商品A,500,1」に対しては、下記のような式を評価します。

WHERE 1 = (SELECT TOP 1 通し番号 FROM t2 WHERE t2.ID = ggggg ORDER BY 価格 DESC, 通し番号)

この式の結果は真となりますので、SELECTで指示されたとおりに出力します。

t1の「eeeee,2000/01/03,商品A,300,4」に対しては、下記のような式を評価します。

WHERE 5 = (SELECT TOP 1 通し番号 FROM t2 WHERE t2.ID = eeeee ORDER BY 価格 DESC, 通し番号)

この式の結果は偽となりますので、このレコードはスルーします（＝出力しません）

2012/06/13 14:04:44

ご丁寧にありがとうございます。
エイリアスの事は何とか飲み込めたと思うのですが、
引っ掛かっているのは、order by のかかり方でした。

(
SELECT TOP 1 通し番号　…①
FROM t2 　…①
WHERE t2.ID = t1.ID　　…①
ORDER BY 価格 DESC　　…②
)

普通だとorder byは全体にかかるので、
①を実行した後に②の並び替えをする、というイメージを持っています。

ただ、TOPという関数(？)が入ると、
②の並び替えをした上で①を実行するようになるのですか？

自分でも調べてみたのですが、ちょっとわかりませんでした。
混乱しており、すみません。

2012/06/13 15:29:38

丸囲み数字はメールのほうでは？になるので、
使わないほうがいいみたいです。

さて、
動作を言葉にすると次のような具合です。
１．FROM に示されたテーブルから、
２．WHERE の条件にあうレコードだけを、
３．ORDER BY の順で並び変えて、
４．SELECT に示されたもの（通し番号）を出力しろ
　　ただし、TOP 1（1番目だけ）でいい

今回の場合は以上のような流れになってますが、
ちょっと変えただけで流れが大きく変わる場合もあります。

このような動作の詳細についてはAccess関連の本にはほとんど載ってません。
（Accessの操作方法までで終わってますよね）

SQL入門系とかデータベース概論系の本を読んでSQLの基礎を学び、
さらに、Access独特のクセを覚えて対応してくという流れになりますので、
あせらず、ゆっくり時間をかけて学んでください。

2012/06/13 15:59:30

ご回答ありがとうございます。

SQLを基礎から学びつつ実践していきたいと思います！！！

2012/06/13 16:16:59

下記の目的を満たすSQLの構文を教えてください。

ベストアンサー

うぃんど262511492012/06/12 22:02:14

その他の回答（1件）

JULY9662472012/06/12 13:15:48

うぃんど262511492012/06/12 22:02:14ここでベストアンサー

コメント（1件)

この質問への反応（ブックマークコメント）