下記の目的を満たすSQLの構文を教えてください｡【目的】客がある時間に買った商品と価格のﾚｺｰﾄﾞが入ったﾃｰﾌﾞﾙAがあり (※1人のﾚｺｰﾄﾞは複数)､その中から各客…

人力検索はてな

ﾓﾊﾞｲﾙ版を表示しています｡PC版はこちら

下記の目的を満たす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
が結果として選択されるように｡

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

●質問者: nao14
●ｶﾃｺﾞﾘ:ｺﾝﾋﾟｭｰﾀ学習･教育
○ 状態 :終了
└ 回答数 : 2/2件

▽最新の回答へ

▽1 ● JULY

4つのﾌｨｰﾙﾄﾞが id, date, item, price という名前で､ﾃｰﾌﾞﾙ名が table1 だったとして､

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

nao14さんのｺﾒﾝﾄ
早速ありがとうございます｡これを実行する場合､group by id, date, item でないといけないと思うのですが(select句で選択するため)､それをしてしまうと､1人1ﾚｺｰﾄﾞが実現できず､やはり現状と何もかわらないﾃﾞｰﾀになってしまいます｡ ※table1(質問ではﾃｰﾌﾞﾙA)は既に､id､date､item､priceでｸﾞﾙｰﾌﾟ化した状態です｡更にご意見頂けるならありがたいです｡よろしくお願い致します｡ ※accessのSQLﾋﾞｭｰを使用しています｡

JULYさんのｺﾒﾝﾄ
> これを実行する場合､group by id, date, item > でないといけないと思うのですが(select句で選択するため)､ん？勘違いされているような気が... group by で指定するﾌｨｰﾙﾄﾞが､select で表示を指定したﾌｨｰﾙﾄﾞとして列挙されていること､が条件であって､select で表示を指定したﾌｨｰﾙﾄﾞ全てが､group by に指定されなければいけない､という訳ではありません｡例えば､ select date, item, max(price) from table group by id は NG になります｡下記は､mysql での例ですが､基本的な考え方は一緒です｡ http://sasuke.main.jp/sqlgroupby.html > ※table1(質問ではﾃｰﾌﾞﾙA)は既に､id､date､item､priceでｸﾞﾙｰﾌﾟ化した状態です｡ Access は知らないので､｢ｸﾞﾙｰﾌﾟ化した状態｣というのが､よく分かりませんが､SQL 文自体は､変わらないと思います｡

nao14さんのｺﾒﾝﾄ
group byとselect句の関係を勘違いしていたようですみません｡ただ､頂いた構文のままだと実行できず､ group by以下にすべてのｶﾗﾑを入れると実行できるので､てっきりそういう仕組みかと｡｡｡他の原因があるようですね｡試行錯誤してみます｡

うぃんどさんのｺﾒﾝﾄ
本題とは関係ないことですが､双方話が食い違っているので横槍失礼します｡＞select id, date, item, max(price) from table1 group by id 回答欄のような表記のdateやitemに対して､ MySQLは適当に処理してくれるのですが､他のﾃﾞｰﾀﾍﾞｰｽでは曖昧さ回避のためｴﾗｰになります｡すなわちJULYさんの回答ならびに返答はMySQLにおいてのみ有効となるものです｡今回の件はAccessであるとｺﾒﾝﾄに記載されているので､ nao14さんが認識しておられたSELECTとGROUP BYの関係は間違いではありません｡なお､私の回答は別の回答欄に書くこととしますので､そちらを参照してください｡

▽2 ● うぃんど

ﾍﾞｽﾄｱﾝｻｰ

1件ずつ抽出したいならば､
ﾚｺｰﾄﾞをﾕﾆｰｸにする必要がありますので､
例えば下記のような具合に番号を振ります｡
(ﾃｰﾌﾞﾙにｵｰﾄﾅﾝﾊﾞｰ型ﾌｨｰﾙﾄﾞを追加するだけならば､
ﾌｫｰﾑやﾚﾎﾟｰﾄなどの改造をしなくて済むので楽ですよ｡)

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, 通し番号);

JULYさんのｺﾒﾝﾄ
ﾌｫﾛｰありがとうございます｡でも､いまいちﾋﾟﾝと来ないのですが､下記のﾍﾟｰｼﾞだと､Access を使って私の書いたようなｸｴﾘｰでも行けそうに見えます｡ http://www.netlaputa.ne.jp/~mkoba/sql/sql_05.html > ﾚｺｰﾄﾞをﾕﾆｰｸにする必要がありますので､ひょっとしたら､主ｷｰの有無とかで変わってくるのでしょうか？あと､下記のﾍﾟｰｼﾞは ADODB 経由で VBA から実行した場合のようですが､その辺の違いとかもあるのでしょうか｡ http://www.accessclub.jp/sql/17.html

nao14さんのｺﾒﾝﾄ
>http://www.netlaputa.ne.jp/~mkoba/sql/sql_05.html 私の経験からですが､ select句には､ ?group byで指定したｶﾗﾑ ?集約関数(max､min､countなど)付きのｶﾗﾑを入れないと実行されませんでした｡ group byで指定しないｶﾗﾑを､集約関数も付けずにselect句に入れると､ｴﾗｰになります｡回答ずれてたらすみません､無視してください｡｡｡

nao14さんのｺﾒﾝﾄ
>windofjulyさまｸｴﾘ例ありがとうございます｡これ､試してみます！

うぃんどさんのｺﾒﾝﾄ
＞行けそうに見えます｡＞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 1)group by id たとえば､idがgggggの場合は下記3ﾚｺｰﾄﾞが1つのｸﾞﾙｰﾌﾟとして扱われます｡ ggggg 2000/1/1/0：00 商品A ￥500 ggggg 2000/1/1/0：00 商品B ￥300 ggggg 2000/1/1/0：00 商品C ￥200 2)select id - GROUP BYで指示されている項目同じｸﾞﾙｰﾌﾟに属していれば､どのﾚｺｰﾄﾞから取ってきてもidはgggggですから結果はgggggです｡ 3)max(price) - 集約関数最大の値ですから答えは500です｡ 4)time､item あれ？どのﾚｺｰﾄﾞから持ってくればいいのですか？ここで､MySQLは偶然最初に見つかった値を持ってきますが､多くのﾃﾞｰﾀﾍﾞｰｽは曖昧さ回避のため､この時点でｴﾗｰになります｡ Accessでも偶然最初に見つかった値を持ってこさせることはできますが､偶然に頼ったものなので､当然ながら関係無い情報を持ってくる可能性もあり､今回のような場合には使いません｡ちなみにFirst関数です｡＞主ｷｰの有無とかで変わってくるのでしょうか？＞ADODB 経由今回の件にはどちらも関係ありません｡ nao14さんの 2012/06/13 10:19:57 の認識は正しいです｡

JULYさんのｺﾒﾝﾄ
あぁ､今分かった！ありがとうございます！気がついてしまえば､｢何してんね､このｱﾎ＞自分｣ですね(^^;｡

うぃんどさんのｺﾒﾝﾄ
｢SQLの基礎を学んでください｡｣なんて･･･失礼なこと書いちゃいました｡ごめんなさい｡

nao14さんのｺﾒﾝﾄ
>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 お手間でなければ教えてくださいませ｡

うぃんどさんのｺﾒﾝﾄ
ｴｲﾘｱｽ(ﾃｰﾌﾞﾙ AS t1 とﾃｰﾌﾞﾙ AS t2)で判りにくければ､ﾃｰﾌﾞﾙt1をｺﾋﾟｰしてﾃｰﾌﾞﾙt2を作ったと想定してみると良いです｡ >|sql| 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｣に対しては､下記のような式を評価します｡ >|sql| WHERE 1 = (SELECT TOP 1 通し番号 FROM t2 WHERE t2.ID = ggggg ORDER BY 価格 DESC, 通し番号) ||< この式の結果は真となりますので､SELECTで指示されたとおりに出力します｡ t1の｢eeeee,2000/01/03,商品A,300,4｣に対しては､下記のような式を評価します｡ >|sql| WHERE 5 = (SELECT TOP 1 通し番号 FROM t2 WHERE t2.ID = eeeee ORDER BY 価格 DESC, 通し番号) ||< この式の結果は偽となりますので､このﾚｺｰﾄﾞはｽﾙｰします(＝出力しません)

nao14さんのｺﾒﾝﾄ
ご丁寧にありがとうございます｡ｴｲﾘｱｽの事は何とか飲み込めたと思うのですが､引っ掛かっているのは､order by のかかり方でした｡ ( SELECT TOP 1 通し番号 …? FROM t2 …? WHERE t2.ID = t1.ID …? ORDER BY 価格 DESC …? ) 普通だとorder byは全体にかかるので､ ?を実行した後に?の並び替えをする､というｲﾒｰｼﾞを持っています｡ただ､TOPという関数(？)が入ると､ ?の並び替えをした上で?を実行するようになるのですか？自分でも調べてみたのですが､ちょっとわかりませんでした｡混乱しており､すみません｡

うぃんどさんのｺﾒﾝﾄ
丸囲み数字はﾒｰﾙのほうでは？になるので､使わないほうがいいみたいです｡さて､動作を言葉にすると次のような具合です｡ 1．FROM に示されたﾃｰﾌﾞﾙから､ 2．WHERE の条件にあうﾚｺｰﾄﾞだけを､ 3．ORDER BY の順で並び変えて､ 4．SELECT に示されたもの(通し番号)を出力しろただし､TOP 1(1番目だけ)でいい今回の場合は以上のような流れになってますが､ちょっと変えただけで流れが大きく変わる場合もあります｡このような動作の詳細についてはAccess関連の本にはほとんど載ってません｡ (Accessの操作方法までで終わってますよね) SQL入門系とかﾃﾞｰﾀﾍﾞｰｽ概論系の本を読んでSQLの基礎を学び､さらに､Access独特のｸｾを覚えて対応してくという流れになりますので､あせらず､ゆっくり時間をかけて学んでください｡

nao14さんのｺﾒﾝﾄ
ご回答ありがとうございます｡ SQLを基礎から学びつつ実践していきたいと思います！！！