• 締切済み
  • すぐに回答を!

Groupの中の条件に合致するデータを抜き出したい

Groupの中の条件に合致するデータを抜き出したい いくつかのテーブルからGroupでくくったデータを抜き出しました。 その中から条件に合致するデータに絞りたいのですが、 思った結果となってくれません。 DB:MySQL 5.1.50 以下のようなことをしたいと考えています。 [元のデータ] ID   | 日付   | 担当   | 金額 A-01  |2010-09-01 |Angas    |1000 A-01  |2010-08-31 |Mayor    |1500 A-01  |2010-08-25 |Angas    |2000 B-01  |2010-08-30 |Roger    |1350 B-01  |2010-09-01 |Angas    |2000 B-01  |2010-08-20 |Roger    |1350 [結果として欲しいデータ] ID   | 日付   | 担当   | 金額 A-01  |2010-09-01 |Angas    |1000 A-01  |2010-08-31 |Mayor    |1500 B-01  |2010-08-30 |Roger    |1350 B-01  |2010-09-01 |Angas    |2000 ちょっとデータ例が貧弱ですが・・・ IDと担当でグループ化した中で、日付が最も大きいレコードを抜き出したい。 というのが目的です。 どのようにSQLを書くと良いのでしょうか? IDと担当でグループ化までは書けたのですが、 「グループのデータの中で日付で降順としたものの最新」というのを どのように書いたものか悩んでいます。 PS.こういうSQLの書き方で悩んだ時に、どうやって情報を集めると 良い情報に出合えるかの助言も頂ければ助かります。 よろしくお願いします。

共感・応援の気持ちを伝えよう!

  • 回答数5
  • 閲覧数214
  • ありがとう数11

みんなの回答

  • 回答No.5

#3です。 >『同じテーブルを、id、担当で結びつけ、 >bにaの日付より大きなものが無い >aのデータを抽出する。』 そうですね。 自分より大きい日付が存在しない = 自分が一番大きい日付 ということです。 #2さんの書かれている、 >order by した上位のものがgroup byで優先されるという保証はありません。 >またgroup byした場合、日付と金額の紐づけも確実ではありません。 ですが、group by というのは標準のSQLでは 集約項目を除いたselect項目を全て記述する必要があります。 MYSQLではそれを拡張し、group by にない項目もselectできるようになっています。 ただし、その項目の値がグループ内で一定でない場合は結果も一定にはなりません。 下記にそのことが書かれています。 http://dev.mysql.com/doc/refman/5.1/ja/group-by-hidden-fields.html

共感・感謝の気持ちを伝えよう!

関連するQ&A

  • 連続したデータを条件とするSQL文の書き方

    下記のようなデータを扱っております。 ID | Point 1 | 10 2 | 20 3 | 30 4 | 20 5 | 30 6 | 10 7 | 20 8 | 30 9 | 30 上記データにおいて、Pointが10,20,30と連続している時の Pointが30でのID値を取得するSQL文が分かりません。 上記データであれば、ID3と8が条件に合致しており、取得したいID値となります。 ID5や9はPointが30ですが、『Pointが10,20,30と連続している』という条件に合致していない為、取得したいID値ではありません。 これを叶えるSQL文を教えて頂けないでしょうか。 (SQLiteを使用しています。)

  • MySQLで特定のグループの上位3件を取得したい。

    ちょっと壁に当たったので、わかる範囲でお答え頂きたいのですが、 以下のようなテーブルがあったとします。 id user price date 1 A 1000 2013/5/31 2 A 1200 2013/6/1 3 B 1000 2013/5/20 4 A 1500 2013/5/12 5 C 1300 2013/5/31 6 C 1400 2013/5/14 7 C 1000 2013/5/6 8 B 1100 2013/5/24 9 B 1200 2013/5/30 10 B 1100 2013/5/4 11 A 1800 2013/4/12 12 C 900 2013/4/6 ・・・ 次に取得したいのは、 A 1200 2013/6/1 A 1000 2013/5/31 A 1500 2013/5/12 C 1300 2013/5/31 C 1400 2013/5/14 C 1000 2013/5/6 B 1200 2013/5/30 B 1100 2013/5/24 B 1000 2013/5/20 このようなデータなのですが、 条件: 1.userでグループ化したうちの日付降順で並べる 2.そのuserのデータの中で、日付降順で並べる 3.userのデータが複数あっても、取得するのは新しいものから3件のみ この条件を満たすSQL文はどのように記述すればよいか、お知恵をお貸し下さい。 よろしくお願いします。

    • ベストアンサー
    • MySQL
  • SQLで部分的にGROUP BYしたいとき

    はじめまして。 SQLで部分的にGROUP BYで集計したいのですが、 どうもしっくりくるSQLがかけません。 多分CASEあたりを使うと綺麗で高速なSQLがかけると思っています。 皆様のお知恵をお借りしたく投稿しました。 具体的には以下のような出納帳データで 出納帳 日付     金額 2012/8/20 1000 2012/8/20 2000 2012/8/21 -1000 2012/8/21 -2000 2012/8/22 3000 2012/8/23 4000 2012/8/24 -3000 2012/8/24 -4000 2012/8/27 5000 ↓集計 日付     金額 2012/8/20 1000 2012/8/20 2000 2012/8/21 -3000(-1000と-2000を集約) 2012/8/22 3000 2012/8/23 4000 2012/8/24 -7000(-3000と-4000を集約) 2012/8/27 5000 というように、マイナスの金額は集約してしまいたいのですが、どのようなSQLが最適でしょうか? とりあえず、UNIONかなと思い、 (SELECT 日付, 金額 FROM 出納帳 WHERE 金額>=0 UNION ALL SELECT 日付, 金額 FROM 出納帳 WHERE 金額<0 GROUP BY 日付 ) ORDER BY 日付 とプラス金額とマイナス金額にわけUNIONしたのですが、 もっと綺麗(高速)にやる方法があるのではないか?と思い投稿いたしました。 なにかヒントございましたら、ご教授ください。 DB環境:Oracle 11g

  • 回答No.4
  • moousi
  • ベストアンサー率70% (21/30)

#1です。 #2さんのSQLは、要件が目に見える形で素晴らしいです。 #3さんのSQLは、簡潔で素晴らしいです。 質問主さま、 #1のSQLは、悪い例として扱い参考にしてください。 自己結合がぱっと頭に浮かぶようになりたいです^^;

共感・感謝の気持ちを伝えよう!

質問者からのお礼

moousiさん、たびたびありがとうございます。 同じ結果を求めるSQLって、幾筋もあるんですね。 それだけに特性を理解していないと、結果の出方も変わるということがわかりました。 > 質問主さま、 > #1のSQLは、悪い例として扱い参考にしてください。 moousiさんに回答をつけていただいたおかげで、 これだけの別の例も見ることができましたし、 おかげで、今回の例でいくと条件の甘さも新たに見つかりました。 たとえば、max(日付)が2レコード以上の場合など。 ありがとうございます。

  • 回答No.3

not exists を使ってはどうでしょうか? select * from t1 a where  not exists(   select * from t1 b   where a.id = b.id   and a.担当 = b.担当   and a.日付 < b.日付)

共感・感謝の気持ちを伝えよう!

質問者からのお礼

yamada_gさん、回答ありがとうございます。 お礼が遅くなってすいません。 ぱっと見たときに解釈できませんでした。(^^; 『同じテーブルを、id、担当で結びつけ、 bにaの日付より大きなものが無い aのデータを抽出する。』 日本語で書くと、こんな感じでしょうか? existsって、普段は、ほとんど使ったことがありませんが、 使えそうですね。 ありがとうございます。

  • 回答No.2
  • yambejp
  • ベストアンサー率51% (3827/7415)

#1さんのロジックはNGのはず。 order by した上位のものがgroup byで優先されるという保証はありません。 またgroup byした場合、日付と金額の紐づけも確実ではありません。 もしやるならこんな感じになります (1)IDと担当で集計した最大の日付をだし (2)IDと担当と日付が合致するデータを抽出する select ID, 日付, 担当, 金額 from テーブル WHERE (ID,日付,担当)IN (select ID,max(日付),担当 from テーブル group by ID,担当)

共感・感謝の気持ちを伝えよう!

質問者からのお礼

yambejpさん、回答ありがとうございます。 教えていただいたSQLにフムフムと感心しております。 とっても簡単なSQLなのかもしれませんが、 なかなか慣れないと見えてこなくて・・・ ただ、ここで疑問が湧いたのですが > order by した上位のものがgroup byで優先されるという保証はありません。 > またgroup byした場合、日付と金額の紐づけも確実ではありません。 この辺の「仕様」というのは、どうやって見つけてこられるのでしょうか? #1さんから教えていただいたSQLで例示の結果が得られたのですが、 逆に得られないような場合のデータを考え中です。

  • 回答No.1
  • moousi
  • ベストアンサー率70% (21/30)

こんな感じになると思います。 select id ID, date(date)日付, tanto 担当, amount 金額 from (select * from table1 order by date desc) a group by id,tanto; あらかじめ日付降順で並べ替えて置いたものをgroup byすると、日付が新しいものが選ばれます。 情報を集めるというか、SQL文を理解するのが結局早いのだ、と思います。 私も道半ばで、偉そうなことは言えない(^^;) ttp://www.geocities.jp/mickindex/index.html ttp://el.jibun.atmarkit.co.jp/g1sys/2009/02/sql-6be6.html 一応、テストで使ったテーブル構成を載せておきます。 create table table1 (id char(4), date datetime, tanto char(20), amount int); insert into table1 values ('A-01','2010-09-01','Angas',1000), ('A-01','2010-08-31','Mayor',1500), ('A-01','2010-08-25','Angas',2000), ('B-01','2010-08-30','Roger',1350), ('B-01','2010-09-01','Angas',2000), ('B-01','2010-08-20','Roger',1350);

共感・感謝の気持ちを伝えよう!

質問者からのお礼

moousiさん、ご回答ありがとうございます。 お礼が遅くなって申し訳ございません。 実際に、教えていただいたSQLにて結果と同様のレコードが 抽出されたことを確認いたしました。 他につけていただいた回答も参考にさせていただきます。 参考URLもありがとうございます。 やはり一番の近道は、SQLの理解ですよね。

関連するQ&A

  • SQL MAX関数を検索条件にする。

    SQL MAX関数を検索条件にする。 わかる方がいれば助けてください。 下記のようなデータのテーブルあります。   ID        品番      金額          日付 OUTLET 210   00182-1080   5600         20090915 OUTLET 214  00182-1080   5600         20090916 OUTLET 217  00182-1080   5600         20090917 OUTLET 50  00182-1080   5600         20090609 OUTLET 437  00191-1082   3300         20100305 BLK09SS703  00191-1082   5500         20090703 OUTLET 141  00191-1082   5500         20090717 OUTLET 170  00191-1082   5500         20090806 このテーブルデータの各品番について最新日付のデータのみを出力したいのですが、 方法がわかりません。group byでHAVING検索なども試しましたがうまくいきませんでした。 <抽出したいデータ>   ID        品番      金額          日付 OUTLET 217  00182-1080   5600         20090917 OUTLET 437  00191-1082   3300         20100305 どなたかお願いします。 前にも同じような質問をさせていただきましたが、 解決できずにこまっています。

  • group by 関数で表示するカラムを選ぶ方法

    いつもお世話になっております。 MySQLで、group by関数を使用した際の表示カラムをコントロールしたいのですが、やり方が分りません。 具体的には id | e_mail | date 1 | t1@t.jp| 2005-05-21 2 | t1@t.jp| 2005-05-22 3 | t1@t.jp| 2005-05-23 4 | t1@t.jp| 2005-05-24 5 | t1@t.jp| 2005-05-25 6 | t1@t.jp| 2005-05-26 7 | t1@t.jp| 2005-05-27 8 | t1@t.jp| 2005-05-28 というデータがあります。 このデータをe_mailでグループ化した場合に、表示されるe_mailを好きなidのものに置き換えたいのです。 通常のSQL文 select id,e_mail,date from table_name group by e_mail だと、どのidのt1@t.jpというアドレスが表示されるのかコントロールできません。 好きなidやdateのt1@t.jpというe_mailを抽出する方法をご教授頂けると大変助かります。 宜しくお願い致します。

    • ベストアンサー
    • MySQL
  • SQLのcount()とgroup by

    以下のようなテーブルがあったとき、 tbl1 id | score 1 | 100 1 | 200 1 | 300 2 | 200 2 | 100 3 | 300 3 | 400 3 | 500 3 | 600 idでまとめて個数を出すとすると、 SELECT count(*) FROM tbl1 group by id とすれば count(*) 3 2 4 と結果が返ってきますが、この結果にidを足して id | count(*) 1 | 3 2 | 2 3 | 4 というようなSQLはどうやって書けばいいのでしょうか? MySQLでは、 SELECT id, count(*) FROM tbl1 group by id と書けるのですが、これは標準的なSQLではNGなそうなので、気になって質問しました。 よろしくお願いします。

  • [MYSQL] GROUP BY による結果レコードの取得について

    こんにちは。 今回は MYSQL の GROUP BY によって、複数レコードのグループ化を行った場合についてお尋ねしたいです。 id, val という二つのフィールドをもつテーブルがあったとして、複数レコードに対して、id でグループ化を行うとします。 その時、val の合計を求めたいのですが、 SELECT id,SUM(val) FROM tablename GROUP BY id となる SQL構文により SUM(val) を取得します。 この場合、SUM(val) を降順にソートした結果レコードを得ることはできますでしょうか? 通常は ORDER BY を用いてこれを行うと思うのですが、このケースではよくわかりません。 また、PEAR によって、これらの結果レコードを連想配列で取得しようと思っています。 結果レコード $rs に対して、 $data = $rs->fetchRow(DB_FETCHMODE_ASSOC); とすると、 $data の連想配列のキーが SUM(val) となってしまいます。 変数に括弧が含まれるのは具合がよくないので、これらを回避する方法はありませんでしょうか? 以上2点おわかりの方がいればお答えいただけないでしょうか。

    • ベストアンサー
    • PHP
  • SQLで部分的にGROUP BYしたいとき(その2

    こんばんわ。 先日こちら(http://okwave.jp/qa/q7666702.html) で質問させていただきましたが、まだ問題があったため、 再度質問させていただきます。 以下のような20カラムある「出納帳」テーブルがあって、 同一日付の金額を集約してしまいたいです。 出納帳 ID 日付   金額 伝票 ・・・(全20カラム) 1 2012/8/20 1000 A01 ・・・ 2 2012/8/20 2000 A02 ・・・ 3 2012/8/21 -1000 B01 ・・・ 4 2012/8/21 -2000 B02 ・・・ 5 2012/8/22 3000 A03 ・・・ 6 2012/8/23 4000 A04 ・・・ 7 2012/8/24 -3000 B03 ・・・ 8 2012/8/24 -4000 B04 ・・・ 9 2012/8/27 5000 A05 ・・・ ↓「日付」で金額集約 ID 日付   金額 伝票 ・・・(全20カラム) 1 2012/8/20 1000 A01 ・・・ 2 2012/8/20 2000 A02 ・・・  2012/8/21 -3000    ・・・(-1000と-2000を集約) 5 2012/8/22 3000 A03 ・・・ 6 2012/8/23 4000 A04 ・・・  2012/8/24 -7000    ・・・(-3000と-4000を集約) 9 2012/8/27 5000 A05 ・・・ と集約キー以外はNULLにしたいのですが、 またまた、 ( SELECT ID, 日付, 金額, 伝票, ・・・ FROM  出納帳 WHERE  金額>=0 UNION ALL SELECT NULL, 日付, SUM(金額), NULL, ・・・ FROM  出納帳 WHERE  金額<0 GROUP BY 日付 ) ORDER BY 日付 とUNIONをする方法しか思いつかなかったのですが、 UNIONせずに端的に表現できるSQLはありますでしょうか?

  • SQLでグループ化して降順表示がうまくいきません

    個人的にMysql、php環境でサイトを作っております。 そこで、素人質問で申し訳ありませんが、SQLでお伺いしたい点があります。 例えば下のようなテーブルがあります。 ▽果物テーブル 果物ID | 果物名 | 売れた日付 1 | リンゴ | 2008/11/1 2 | みかん | 2008/11/2 3 | みかん | 2008/11/3 4 | リンゴ | 2008/11/4 これを、 『最近売れた』果物順に、『グループ化』して並べ替えたいと思い、 下記のようなsqlを組みました。 $sql = "SELECT 果物名, FROM 果物テーブル GROUP BY 果物名 ORDER BY 売れた日付 DESC"; 意図としてはリンゴ、みかんの順に並べ替えたいのですが、 みかん、リンゴの順に表示されてしまいます。 想像では、グループ化したとき、古い日付の方が残ってしまい、 | リンゴ | 2008/11/1 | みかん | 2008/11/2 この部分を対象にして日付を降順にしてしまうからだと思うのですが、 これをうまく解決する方法が分かりません。 MSアクセスなどですと先に二重にクエリをかけるなどすることで 「先に降順で並べ替えたクエリを出し、その後グループ化させる」 など処理が簡単なのですが、phpではどうするのかいまひとつ分からず・・・ 申し訳ありませんがよろしくお願いします。

    • ベストアンサー
    • PHP
  • ある条件のデータを取り出したいのに...

    ある一部の条件に沿ったデータを取り出したのですが、取り出す事ができません。 DB設計自体を変更しなくてはいけないと思ったので相談しました。 下記の条件でできるのかどうかをアドバイス頂けないでしょうか? お願いします。 select * FROM Atable, Btable, Ctable WHERE Atable.group_id = '$GROUP' AND Atable.group_id = Btable.group_id ORDER BY total_day DESC

  • GROUP BYで集約されるときのソートを変えたい

    GROUP BYで集約されるときのソートを変えたい MySQLの5.1を使用しています。 テーブル"tbl"には、"no","id","score"の3つのフィールドがあり、 "no"は主キーです。 +---+----+-------+ | no | id | score | +---+----+-------+ | 1 | 10 | 10002 | | 2 | 10 | 10000 | | 3 | 10 | 10008 | | 4 | 11 | 10004 | | 5 | 12 | 10006 | +---+----+-------+ 上記の表から、 scoreの値が高い順にグループ化してソートしたいのですが、 以下のSQL文ではscoreが最初に登録されたものに集約されてからグループ化されてソートしてしまうため、 思い通りの結果が得られずに困っています。 ↓具体例 SELECT no, id, score FROM tbl ORDER BY score DESC GROUP BY id; +---+----+-------+ | no | id | score | +---+----+-------+ | 5 | 12 | 10006 | | 4 | 11 | 10004 | | 1 | 10 | 10002 | +---+----+-------+ ↓欲しいソート順 +---+----+-------+ | no | id | score | +---+----+-------+ | 3 | 10 | 10008 | | 5 | 12 | 10006 | | 4 | 11 | 10004 | +---+----+-------+ テンポラリテーブルはなるべく使用せずソートしたいのですが、 このような事は可能なのでしょうか? ご存知の方がいらっしゃいましたら教えていただけると助かります。 よろしくお願いします。

    • ベストアンサー
    • MySQL
  • GROUP BY使用時のソートについて

    OracleSqlにて「GROUP BY」使用した日付のデータを取得するSql文を作成したのですが、 「ORDER by」句に「DESC」を付けても降順ソートが行えません。 作成したSql文は以下の通りなのですがなぜ降順ソートが行えないのでしょうか? 「GROUP BY」を使用するとソート出来ないと言うことなのでしょうか? それとも日付型なのでソートが出来ないと言うことなのでしょうか? どなたかご存知の方おりましたらご教授下さい。 ------------------------------------------------- SELECT TO_CHAR(RECORDDATE,'YYYY/MM/DD') ,RECORDYEAR ,RECORDMONTH ,RECORDDAY FROM STOCKTBL WHERE TO_CHAR(RECORDDATE,'YYYY/MM/DD')<='2002/03/07' AND TO_CHAR(RECORDDATE,'YYYY/MM/DD')>='2002/02/22' GROUP BY TO_CHAR(RECORDDATE,'YYYY/MM/DD') ,RECORDYEAR ,RECORDMONTH ,RECORDDAY ORDER by TO_CHAR(RECORDDATE,'YYYY/MM/DD') ,RECORDYEAR ,RECORDMONTH ,RECORDDAY DESC -------------------------------------------------

  • group by とhaving

    こんにちは。 グループ化した後に条件で摘出する方法について教えて頂けますでしょうか。 例えば、以下のようなテーブルを作ったとして、 テーブル id tag_no 1 5 1 6 1 7 1 8 1 9 2 5 2 10 2 11 3 5 3 9 3 10 指定したtag_noを持つidを摘出したいのですが、tagnoを複数指定するのにはどのようにしたらよいのでしょうか・・・。 select table from group by id having tag_no = 5 and tag_no = 9; として、 idの1と2を取り出す・・・といったことをやりたいのですが、 実際にやってみるとempty setとなります。 このテーブルで無理ならテーブルの変更もありなのですが、 一晩悩んでも思い浮かばず・・・。 mysqlはvar5.5です。 お力添えをよろしくお願い申し上げます。

    • ベストアンサー
    • MySQL