• 締切済み

MYSQLを用いた集計について

MySQLを用いた、クロス集計（？）のデータについてどこを探しても見つからなかったので、投稿します。 DBＡとDBＢ、DBＣをLEFT JOINを行って、DBＣの複数条件の全てのに適合する、DBＢの件数をＡのID単位で取得したいと思っています。 ＜テーブル構成＞ DB：Ａ －－－－ id PK B_id FK flag －－－－ DB：Ｂ －－－－ id PK C_id FK flag －－－－ DB：Ｂ －－－－ id PK flag －－－－ そこで色々試行錯誤してみたのですが、良い方法が見つからず、現在は確認を兼ねて、以下の様な形で抽出するリストを構築する所 までは行いました。 －－－ SELECT *,count(DB_B.id) FROM (DB_A LEFT JOIN DB_B on DB_A.B_id = DB_B.id) JOIN DB_C on DB_B.C_id = DB_C.id WHERE DB_A.flag = 100 AND (DB_C.flag = 1 OR DB_C.flag = 2 OR DB_C.flag = 3) GROUP BY DB_B.id HAVING count(DB_C.id) = 3 －－－ ここで抽出されるリストをDB_Aのid別に件数を取得するにはどうすればよろしいでしょうか？ もちろん、上のコードでは無理だろうと予測はついているのですが、ここから先に進めず…。 ちなみに、環境は以下のとおりです。 MySQL：4.0.27 PHP：4.4.4 ※ちなみに、同じような形式でDB＿Ｄの複合条件、DB＿Ｅの複合条件を掛け合わせていく形もあり得るため、それに準じた方法・知恵をご教授いただけると幸いです。 よろしくお願致します。

  • MySQL
  • 回答数2
  • ありがとう数3

みんなの回答

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

いくつかやり方がありそうですが、 前回書いたとおり集計したものをテンポラリに入れておいて再度 joinしてみました。 （もうすこし効率的なやり方がありそうな気はしますが、とりあえず） create temporary table temp_shop select shop.city_id,count(*) as count from shop left join shop_detail as d1 on d1.shop_id=shop.shop_id and d1.detail_name='海老' left join shop_detail as d2 on d2.shop_id=shop.shop_id and d2.detail_name='鯛' where 1 and d1.deta_id is not null and d2.deta_id is not null group by city_id; select city.city_id,city.city_name,temp_shop.count from city left join temp_shop on temp_shop.city_id=city.city_id

uzukit
質問者

お礼

テンポラリを用いて、答えが出てきました。 頂いた回答だと、テンポラリの中でLEFT JOINを繰り返す為、非常に時間がかかりましたが、それぞれ単一のテンポラリテーブルとして情報を生成し、最後にLEFT JOINでくっつけてそれぞれJOINしたテーブルの固有カラムをis not nullしてやれば、早かったです。 私の解法： create temporary table temp_flag select *, count(shop_id) as cnt from shop_detail WHERE detail_name='鯛' OR detail_name='海老' group by shop_id having cnt = 2; create temporary table temp_shop as sh select *, count(city_id) as cnt_c from shop left join temp_flag as flg sh.shop_id = flg.shop_id WHERE flg.detail_name is not null group by city_id; select city.city_id,city.city_name,sh.cnt_c from city left join temp_shop as sh sh.city_id = city.city_id WHERE sh.shop_name is not null

uzukit
質問者

補足

テンポラリを使うやり方がいまいちわかってない（と言うより、リファレンスに書かれてありませんでした）ので、探りながらやってみます。 ありがとうございます。

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

もう少しわかりやすい実例をつけてもらうと回答しやすいと思います。 今見た感じでは基本的に1対多のJOINを２回している時点でカウントは できないと思います。 やるなら最初の集計でテンポラリに落として、それに次のJOINをする ような流れではないでしょうか

uzukit
質問者

補足

返答ありがとうございます。 お察しの通り、1対NのJOINを複数回繰り返し、その中での集計を行った上でのカウント取得となります。 実例（若干実データに近いのですが）を出すと、この様な形になります。 DB名：city ===================== city_id | city_name ===================== 1 | 世田谷区 2 | 渋谷区 3 | 新宿区 4 | 太田区 5 | 江戸川区 ===================== DB名：shop =============================== shop_id | city_id | shop_name =============================== 1 | 1 | 店舗１ 2 | 2 | 店舗２ 3 | 3 | 店舗３ 4 | 2 | 店舗４ 5 | 3 | 店舗５ 6 | 3 | 店舗６ 7 | 4 | 店舗７ 8 | 5 | 店舗８ =============================== DB名：shop_detail =============================== deta_id | shop_id | detail_name =============================== 1 | 1 | 海老 2 | 1 | 鯛 3 | 2 | 海老 4 | 2 | 玉子 5 | 2 | 鯛 6 | 3 | イカ 7 | 3 | 海老 8 | 3 | 玉子 =============================== この中で、海老と鯛どちらもある店舗が、各区で何件あるかを割り出したいと思っています。 今回の場合は世田谷区＝1件、渋谷区＝1件となりますが、この中で「世田谷区」「1件」「渋谷区」「1件」を 出すために、ひとつのSQL文でまとめたいと思っています。 先に提示した確認方法ですと、店舗1と店舗2に該当する2件がある事を確認できるのですが、それを区毎にまとめる事が出来ません。 また、同じ様に、shop_detailと同じような1対Nの検索条件が増えた時に同じ様にまとめていく事が出来ません。 （1対Nのデータのキーカラムはshop_idになります） それで、解決策を求めて～となります。 一応、質問を出した後で考えた（実験はしてないのですが）方法としては ・1対N毎に検索式を構築し、UNION ALLでまとめる。 ・各検索で全件引っかかるデータのみを[city][shop_id]の配列に格納する。 ・更にそれをカウントし検索式数分が入っていない場合は、そのshop_idの配列を捨てる ・最後にcityのカウントを取る でした。 どう見ても力業なので、なるべくなら避けたいと思っています。。。 もっと効率的な方法はないでしょうか。。。

  1. カテゴリ
  2. [技術者向] コンピューター
  3. データベース
  4. MySQL

関連するQ&A

  • MySQL 複数テーブルのフィールドにUPDATE

    よろしくお願いします。 PHP5、MySQL5、 PEAR DB、Smarty　にて開発しております。 標題にもありますように、LEFT JOINを使ってUPDATEできる方法を探しております。 ●Ａテーブル（親） id ｜ del_flag ----------------- 1 ｜ 0 2 ｜ 0 3 ｜ 0 4 ｜ 0 ●Ｂテーブル id ｜ del_flag ----------------- 1 ｜ 0 3 ｜ 0 4 ｜ 0 ●Ｃテーブル id ｜ del_flag ----------------- 1 ｜ 0 2 ｜ 0 3 ｜ 0 4 ｜ 0 上記のような３つ、またはもしくはそれ以上テーブルがあったとします。 そこで、 Ａテーブルの id 2 のdel_flagを1　とアップデートしたときに、 同時に全てのＢ、Ｃのテーブルの id 2 のdel_flagも　1　としたく思います。 Ｂテーブルには、id 2 が存在しないため、結果的にはＣテーブルだけが更新されますが、 Ａ、Ｂ，Ｃともに同一のid値　がある場合も考慮して、 アップデートしたいと思います。 そこで、PHP内で、一つ一つのテーブルを地道にアップデートすることは可能だとおもいますが、 一度のＳＱＬでdel_flagの値を１とできる方法はございませんでしょうか？ お手数ですが、 具体的なクエリを書いていただけると、大変助かります。 いろいろ調べましたが、なかなか上手くいかず・・・・。 よろしくお願いいいたします。 　

    • ベストアンサー
    • MySQL
  • COUNT関数が上手く設定できない

    COUNT関数が上手く設定できなくて困っています。 DBに案件テーブルと担当者テーブルを設定し、案件に紐付く担当者を検索できるようにしようと思っています。 案件に対して複数の担当者が担当になることがあり、案件テーブルに担当者テーブルをLeft Joinして、最後にGroup byでグルーピングすることによって案件自体は検索できるのですが、ページネーションのためCOUNT関数を使って案件数を求めようとすると、正しい数値が得られません。 select count(a.id) counts // ここをa.* とすることで案件情報を検索することはできます。 from ANKEN a left join TANTOU t on a.id = t.id where flag = 0 group by a.id 正しい件数を取得するにはどのようにすれば良いでしょうか？

    • ベストアンサー
    • MySQL
  • テーブルごとのカウント

    PHP5.2+mysql 5.0.45で開発を行っております。 SQLに関する質問なのですが 以下のことが可能かどうかご教授いただきたく。 4つのテーブルがあります。 （例は適当です。項目の名称等は無視してください。） テーブルA ID Name Kana テーブルB ID NameID Pref City テーブルC ID NameID Tel Fax テーブルD ID NameID email CellPhone とします。 A.ID＝1000の時各テーブルのレコード数が A:B:C:D＝1:3:2:2となっています。 SQLの出力結果として A.ID A.Name A.Kana B.Count(ID) C.Count(ID) D.Count(ID) という、6項目を出力したいのですが 方法がわかりません。 試してみたのは select A.ID,A.Name,A.Kana,Count(B.ID),Count(C.ID),Count(D.ID) from A left join B on A.ID = B.NameID left join C on A.ID = C.NameID left join D on A.ID = D.NameID where A.ID = 1000 group by A.ID,A.Name,A.Kana ですが 結果、 A.ID = 1000 A.Name = Name A.Kana = Kana Count(B.ID) = 3 Count(C.ID) = 3 Count(D.ID) = 3 となってしまいます。 冷静に考えるとそうなんですが・・・ もしうまく結果を取得できる方法があればご教授いただきたく よろしくお願いいたします。

    • ベストアンサー
    • MySQL
  • MYSQLでSQLSERVERのリンクサーバーのようなことは可能ですか

    MYSQLでSQLSERVERのリンクサーバーのようなことは可能ですか？ AとBのMYSQLサーバーがあり、AのDB接続からBのDBを参照したいのですが、方法はありますでしょうか？ 一つのSQL文にて下記のようなことを実現したいです。 SELECT * FROM servernamea.dbname.tablename LEFT JOIN servernameb.dbname.tablename ON servernamea.dbname.tablename.id = servernameb.dbname.tablename.id どなたかよろしくお願いします。

    • ベストアンサー
    • MySQL
  • DBを問わないSQLのチューニングが学べる本はありますか？

    以下のSQLはほぼ全てのDBで使用できると思いますが どちらが高速なのでしょうか？このような、 DBを問わずにSQL文に関する基礎的なチューニングが学べる本はありますでしょうか？ (1)JOINを使わずに結合 select * from A,B,C WHERE A.a=B.a AND A.b =C.b (2)JOINを使用して結合 select * from A left join B on A.a=B.a left join C on A.b=C.b

  • AccessにおいてフィールドがＮＵＬＬの値を０に変換するには

    下記のようなＳＱＬで件数を取得したいと考えています。 テーブルＢの件数がＮＵＬＬの場合、差がＮＵＬＬになってしまいます。 このような場合、差を０にしたいです。 どのように修正したらよいでしょうか。 SELECT A.ID, (A.件数-B.件数) AS 差 FROM A LEFT JOIN B ON A.ID = B.ID;

  • LEFT JOINが2つあるSQL文でANDの意味

    ■下記SQL文の意味を教えてください SELECT a.*, b.being_name FROM alive a 　LEFT JOIN being b ON a.hoge_id = b.id 　LEFT JOIN call c ON c.call_id = a.hoge_id 　　AND f.hoge_id = 12 　WHERE f.hoge_id = 12 OR b.id = 12 ※12の部分は動的に切り替わります ・LEFT JOINが2つあるので、3つのテーブルを結合しているのでしょうか？ ・左テーブルは「alive a」で、この右側に2つのテーブルが結合している、という認識でよいでしょうか？ ＞SELECT フィールド名 FROM テーブル名 WHERE 条件式1 AND 条件式2 ＞「AND」は２つの条件式の論理積 ・上記内容をネットで見かけたのですが、「AND」は、「WHERE」の前に来てもいいのでしょうか？ それともこのSQLの「AND」は違う使い方をしているのでしょうか？ 何か、LEFT JOINに関係しているのでしょうか？

    • ベストアンサー
    • MySQL
  • 入れ子を使わずにレコードの総計を出したい

    こんにちわ。PostgreSQLのSQL文について質問させてください。 以下のSQL文を実行したとき、 # select c.id from C c left join S s on (s.id=c.id) where c.num = 1 group by c.id having count(s.id) >= 5; id ---- 7 5 (2 rows) というデータが表示されるんですが、このSQL文で抽出したデータの総件数を求めるにはどうしたらいいでしょうか？ 入れ子を使うと、 # select count(*) from (select c.id from C c left join S s on (s.id=c.id) where c.num = 1 group by c.id having count(s.id) >= 5) tmp; というふうに書け、 count ------- 2 (1 row) と表示されるのはわかりますが、入れ子を使わずに上のように表示させるにはどうしたらよいでしょうか？ よろしくお願いいたします。

  • 複数JOINしているとCOUNTが正しく取得できな

    LAMP環境で開発をしています。 SQL文でCOUNTを求める際に、まとめて結果を求めようとして上手く行きません。 状況としては以下です。 テーブルdはidをkeyにa,b,c３つのテーブルとjoinしています。 id = 1の場合、テーブルa,b,cにマッチするレコードがそれぞれに４個、１個、０個あります。 ひとつひとつを SELECT COUNT(CASE WHEN a.name IS null THEN 1 ELSE null END) as a_count FROM d INNER JOIN a ON a.id = d.id WHERE d.id = 1 として結果を求めると4,1,0と出るのですが、まとめて SELECT COUNT(CASE WHEN a.name IS null THEN 1 ELSE null END) as a_count, COUNT(CASE WHEN b.name IS null THEN 1 ELSE null END) as b_count, COUNT(CASE WHEN c.name IS null THEN 1 ELSE null END) as c_count FROM d INNER JOIN a ON a.id = d.id INNER JOIN b ON b.id = d.id INNER JOIN c ON c.id = d.id WHERE d.id = 1 とすると28,5,0という値が返されます。 どのように書けば正しい4,1,0を得られるのでしょうか？ よろしくお願いします。

    • ベストアンサー
    • MySQL
  • テーブル結合の件

    今回以下の内容を mysql Client API version 3.23.49 PHP/4.4.5 で動作させようと思っています。 sqlに関してあまり理解していません。 table member id name local 1 yamada saga 2 kawasaki osaka 3 tanaka miyagi 4 ozawa aomori table net id watch bag price day 1 2 0 \10,000 12/3 3 1 2 \50,000 12/6 1 0 3 \60,000 12/9 4 1 0 \15,000 12/15 table shop id watch bag price day 3 5 1 \48,000 12/1 3 5 2 \80,000 12/6 1 3 3 \60,000 12/10 3 4 2 \50,000 12/12 1 5 3 \60,000 12/13 結果 id name local net shop 1 yamada saga 2 2 2 kawasaki osaka 0 0 3 tanaka miyagi 1 3 4 ozawa aomori 1 0 という契約にいたった件数を得たいのですがどうすればいいのでしょうか？ SELECT a. * , count( b.id ) AS idcount1, count( c.feed_id ) AS idcount2 FROM member AS a LEFT JOIN net AS b ON a.feed_id = b.feed_id LEFT JOIN shop AS c ON a.feed_id = c.feed_id GROUP BY a.feed_id という形でやってみたのですが 片方のカウントがうまく表示できません。 どうすればいいのでしょうか？ 宜しくお願いいたします。

    • ベストアンサー
    • MySQL

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する