• ベストアンサー
  • すぐに回答を!

複数テーブルの集計その2

お世話になります。 先日テーブルの集計について教えていただき、下記集計結果を取得することができたのですが、 新たに自由形50~平泳ぎ50までの合計を追加したいのです。(一人で4種目参加する場合は4と数える) テーブル1 AreaID   AreaName --------------------- 1 北海道 2 東北 テーブル3 ID AreaID   Name  Item1   Item2  Item3 Item4 --------------------------------------------------------- 1   2    鈴木    2    1    4     3 2   1    田中    1    2    null   null 3   2    伊藤    3    null   2    4  「集計結果」       参加人数 自由形50 自由形100 背泳ぎ50 平泳ぎ50  追加部分 --------------------------------------------------------------- 北海道     1     1       1      0      0       2 東北      2     1       2      2      2       7   $sql="select AreaName, count(distinct ID) 参加人数, count(case when Item=1 then 1 else null end) as 自由形50, ・・・略 from t1 as x left join (select ID,AreaID,1 as Item from t3 where Item1=1 or Item2=1 or Item3=1 or Item4=1 union all select ID,AreaID,2 as Item from t3 where Item1=2 or Item2=2 or Item3=2 or Item4=2 ) as y on x.AreaID=y.AreaID group by AreaName order by x.AreaID というようにおしえていただきました。 null以外を取得するSQLをカウントとselectに追加すればいいのかと思い試したのですが期待した値を取得できません。 count(case when Item=AAA then 1 else null end) as abc,と union all select ID,AreaID,AAA as Item from テーブル3 where ItemID1<>'' or ItemID2<>'' or ItemID3<>'' or ItemID4<>'' or (ItemID1<>'' and ItemID2<>'') ・・・ バージョンはMySQL4.1.18です。 よろしくお願い致します。

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

  • MySQL
  • 回答数2
  • 閲覧数172
  • ありがとう数2

質問者が選んだベストアンサー

  • ベストアンサー
  • 回答No.1

前回、SQLを提示した者です。 まず、前回のSQLをおさらいしましょう。 前回のSQLを、グループ化とcountをやめ、どういう行が作られているか確認してみましょう。 <前回のSQL> select * from t1 as x left join ( select ID,AreaID,1 as Item from t3 where Item1=1 or Item2=1 or Item3=1 or Item4=1 union all select ID,AreaID,2 as Item from t3 where Item1=2 or Item2=2 or Item3=2 or Item4=2 union all select ID,AreaID,3 as Item from t3 where Item1=3 or Item2=3 or Item3=3 or Item4=3 union all select ID,AreaID,4 as Item from t3 where Item1=4 or Item2=4 or Item3=4 or Item4=4 ) as y on x.AreaID=y.AreaID order by x.AreaID ; 上記のSQLでは、テーブル3の各人毎に、種目を「列で管理」していたものを、「別の行」で管理するようにしています。 今回、追加で求めるものは「のべの種目数」のようなので、上記で生成した行から、集計するcount関数を追加すれば実現可能と思います。 前回のSQLに追加するのは、 count(case when Item between 1 and 4 then 1 else null end) as 追加部分 だけです。 <今回のSQL> select AreaName, count(distinct ID) as 参加人数, count(case when Item=1 then 1 else null end) as 自由形50, count(case when Item=2 then 1 else null end) as 自由形100, count(case when Item=3 then 1 else null end) as 背泳ぎ50, count(case when Item=4 then 1 else null end) as 平泳ぎ50, count(case when Item between 1 and 4 then 1 else null end) as 追加部分 from t1 as x left join ( select ID,AreaID,1 as Item from t3 where Item1=1 or Item2=1 or Item3=1 or Item4=1 union all select ID,AreaID,2 as Item from t3 where Item1=2 or Item2=2 or Item3=2 or Item4=2 union all select ID,AreaID,3 as Item from t3 where Item1=3 or Item2=3 or Item3=3 or Item4=3 union all select ID,AreaID,4 as Item from t3 where Item1=4 or Item2=4 or Item3=4 or Item4=4 ) as y on x.AreaID=y.AreaID group by AreaName order by x.AreaID ; <注意事項> 質問者さんが考えた部分で、nullの扱いに勘違いがあるようなので、補足説明しておきます。 (1)nullと0バイト文字は別物です。 nullを判別するには、「列名 is null」や「列名 is not null」で判別します。「=」や「<>」では、通常、判別できません。

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

質問者からのお礼

chukenkenkouさん、いつもありがとうございます。 前回に続き大変勉強になりました。 nullの使い方間違っていたんですね。ご指導ありがとうございました。 また、select ID,AreaID,1…の1について調べてみて「定数を指定」というのがあったのですが、よく理解できませんでした。 countで使うためなのでしょうか。 よろしければご教授ください。

その他の回答 (1)

  • 回答No.2

#1回答者です。 >select ID,AreaID,1…の1について調べてみて「定数を指定」 ITEM1~ITEM4のいずれかに1が入っている行数分、種目IDとして1(定数で指定)を拾うようにしています。 同様に、ITEM1~ITEM4のいずれかに2が入っている行数分、種目IDとして2(定数で指定)を拾うようにしています。 しかし、前回までに提示したSQLは、種目IDが1~4を予め抽出してunionする仕組みにしていました。これでは、種目IDが追加された場合、その分だけ、unionするselect文を追加する必要がありました。 そこで、次のようなSQLにした方が、シンプルで分かりやすいですね。 <SQL例> select AreaName, count(distinct ID) as 参加人数, count(case when Item=1 then 1 else null end) as 自由形50, count(case when Item=2 then 1 else null end) as 自由形100, count(case when Item=3 then 1 else null end) as 背泳ぎ50, count(case when Item=4 then 1 else null end) as 平泳ぎ50, count(case when Item between 1 and 4 then 1 else null end) as 追加部分 from t1 as x left join ( select ID,AreaID,Item1 as Item from t3 union all select ID,AreaID,Item2 as Item from t3 union all select ID,AreaID,Item3 as Item from t3 union all select ID,AreaID,Item4 as Item from t3 ) as y on x.AreaID=y.AreaID and Item is not null group by AreaName order by x.AreaID ; unionするときは、単純にITEM1~ITEM4を、別の行として得ています。仮に種目IDが追加された場合は、selectのcount関数のところだけ変更すればいいことになります。

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

質問者からのお礼

ご回答ありがとうございます。 定数の意味がなんとなくですがわかりました。 select文はもっと勉強します! シンプルな構文のほうもさっそく試してみたいと思います。 本当にありがとうございました。

関連するQ&A

  • 複数テーブルの集計

    お世話になっています。 複数テーブルの集計がわからないので質問させてください。 テーブル3のItem1~4にはテーブル2のItemIDを登録します。 エリア毎の参加人数をcsvデータを出力させたいです。 テーブル3においてテーブル2のItemIDが4つあるので、一人に対して4行必要なのかと思いましたが、テーブルの変更ができません。 テーブルの結合まではできましたがその後ができません。 テーブルの変更ができませんので、結合するためにテーブル2の構造とデータが同じテーブルを他に3つ作成しています。 どうぞよろしくお願いします。 テーブル1 AreaID   AreaName --------------------- 1 北海道 2 東北 3 関東 4 北陸 テーブル2 ItemID ItemName --------------------- 1 自由形50 2 自由形100 3 背泳ぎ50 4 平泳ぎ50 テーブル3 ID AreaID   Name  Item1   Item2  Item3 Item4 --------------------------------------------------------- 1   2    鈴木    2    1    4     3 2   3    佐藤    1    null   3    null 3   1    田中    1    2    null   null 4   2    伊藤    3    null   2    4  5   3    川村    null   2    3    null 「集計結果」       参加人数 自由形50 自由形100 背泳ぎ50 平泳ぎ50 --------------------------------------------------------------- 北海道    1     1     1     0     0  東北     2     1     2     2     2   関東     2     1     1     2     0   北陸     0     0     0     0     0

    • ベストアンサー
    • MySQL
  • MySQLでの集計

    いつもお世話になっております。MySQLを利用しておりまして質問です。 同じ構成のテーブルが5つあり、選択されたテーブルを集計するSQLを作成しています。 カラムを簡単に構成を書くと ID、名前、ステータス1、ステータス2 のような形です。 テーブル1、2、3が選択された場合 テーブル1と2でIDの重複のない一覧を作成し、IDが被っていた場合はステータスをそれぞれ大きいほうを保持するようにします。 そして次にテーブル1、2を合わせたテーブルとテーブル3で同じように集計をしたいのですが、ここがうまく出来ません。 実際の環境ではなく簡素化したものなのでおかしな点があるかもしれませんがこのようになっています。 SELECT TTT1.id,TTT1.名前,TTT1.st1,TTT1.st2 FROM(     SELECT id,名前,ステータス1 as st1,ステータス2 as st2     FROM(        SELECT *        FROM テーブル1 T1        WHERE T1.id NOT IN(SELECT T2.id FROM テーブル2 T2)        UNION All        SELECT *        FROM テーブル2 T2        WHERE T2.id NOT IN(SELECT T1.id FROM テーブル T1)     ) as TT1     UNION     SELECT id,名前,st1,st2     FROM(         SELECT T1.id,T1.名前,         (CASE T1.ステータス1 or T2.ステータス1 WHEN "2" THEN "2" WHEN "1" THEN "1" WHEN "0" THEN "0" ELSE null END) as st1,         (CASE T1.ステータス2 or T2.ステータス2 WHEN "2" THEN "2" WHEN "1" THEN "1" WHEN "0" THEN "0" ELSE null END) as st2,         FROM テーブル1 T1,テーブル2 T2         WHERE T1.id = T2.id AND NOT(T1.st1 IS null AND T1.st2 IS null AND T2.st1 IS null AND T2.st2 IS null) ) as TT2 )as TTT1 /* UNION ALL SELECT id,名前,st1,st2 FROM (TTT1とテーブル3を比較したもの)as TTT2 */ /**/で囲ったところでTTT1が呼び出せれば、テーブル1のところをTTT1にし、テーブル2をテーブル3にすれば出来ると思うのですが、そういうことは無理なのでしょうか? また集計するテーブルの数が不特定なので一つずつ集計をする形を取りましたが、 もっとこういう風にやればいいと言う方法はございますでしょうか? よろしくお願いいたします。

    • ベストアンサー
    • MySQL
  • 複数(2つ)のテーブルで検索を行いたい

    MySQL 3.23です。 select count(*) as hit from freedata where (A=10 or B=10); という構文と select count(*) as hit from sharedata where A=10; という構文を一行にしたOR検索を行いたいのですが、方法はありますか? 要は、同一のキーワードで、二つのテーブルを検索対象にしたいのです。ご教授よろしくお願い致します。

    • ベストアンサー
    • MySQL
  • 年齢分布テーブルの再集計SQL

    テーブルA 年齢,人数 20,15 21,11 22,26 .... 30,5 31,88 ... 60,5 上記テーブルAからのSQLで下記のデータが欲しいと思っております。 【欲しい結果】 年齢範囲,人数 20~24,50 25~29,33 30~34,199 ... ただし、 select '20~24'as 年齢範囲,count(*)as 人数 from テーブルA where 条件1 union select '25~29'as 年齢範囲,count(*)as 人数 from テーブルA where 条件2 union select '30~34'as 年齢範囲,count(*)as 人数 from テーブルA where 条件3 ... というSQLは使いたくないです。(テーブルAの記述が長めなので、同じものを複数記述したくないのです) また結果が 項目名:20~24,25~29,30~34,... レコード:50,33,199,... というのも遠慮したいです。 CASE文やsum、groupを使えば、できそうな気がするのですが、、、 ご教授お願い致します。

  • 同一テーブルの同一フィールドで複数条件 and 指定

    http://oshiete1.goo.ne.jp/kotaeru.php3?q=1069795 で質問した続きです。 うっかり詳細のテストをしてないのに締め切ってしまいました。 テーブルA 「member」 | id | name | テーブルB 「item」 | id | name | テーブルC 「item_check」 | member_id | item_id | というテーブルがあります。 テーブルAはメンバーで一人一つのデータが入っています。 テーブルBはメンバーが持つであろうアイテムのマスタです。 テーブルCはメンバーがどのアイテムを持っているかを示しているテーブルです。 テーブルCに関しては同じメンバーでも複数のアイテムを持つこともあるので複数のデータが入る場合もあります。 で、やろうとしているのは、 「アイテムA と アイテムBを持っているメンバー」 という検索を行いたいのです。 select distinct a.id, a.name from member as a,item as b,item_check as c where a.id = c.member_id and c.item_id = b.id and (c.item_id = '2' or c.item_id = '3') select member.*,item.name from member, item, item_check where member.id=item_check.member_id and item.id=item_check.item_id and item.id in (1, 3) group by member.id で or 検索はできますが、 and 検索をすると 「Empty set」となってしまいます。 select distinct a.id, a.name from member as a,item as b,item_check as c where a.id = c.member_id and c.item_id = b.id and (c.item_id = '2' and c.item_id = '3') なにかいい手はないでしょうか。

    • ベストアンサー
    • MySQL
  • 助けて>< 3つのテーブルで簡単な集計

    下記のような3種類のテーブルがあります。 これを集計して、指定したユーザーの指定月のアクセス日時と売上げ金額を表にしたいと思っています。 こんな感じに。 +--+--------+----------+-------+-----+ | id | username| date   | access | sales | +--+--------+----------+-------+-----+ | 1 | admin | 2010-11-13 |   2 | 5000 | | 1 | admin | 2010-11-14 |   2 | 5000 | | 1 | admin | 2010-11-15 |  1 | 20000 | +--+--------+----------+-------+-----+ SELECT u.id AS id, u.username, DATE(a.created_at) AS date, COUNT(*) AS access, SUM(s.amount) AS sales FROM sf_guard_user u INNER JOIN sales s ON u.username = s.user_id LEFT JOIN access_log a ON u.username = a.user_id WHERE (u.username = 'admin' AND a.created_at > '2010-11-01 00:00:00' AND a.created_at < '2010-11-30 23:59:59') GROUP BY date ORDER BY a.created_at; とやってみたのですがダメでした;; ■ユーザー情報テーブル mysql> select id,username from user; +--+---------+ | id | username | +--+---------+ | 1 | admin   | +--+---------+ ■アクセスログテーブル mysql> select * from access_log; +--+-------+--------+------------------+ | id | user_id | ip     | created_at      | +--+-------+--------+------------------+ | 1 | admin  | 127.0.0.1 | 2010-11-13 21:56:54 | | 2 | admin  | 127.0.0.1 | 2010-11-13 21:56:54 | | 3 | admin  | 127.0.0.1 | 2010-11-14 21:56:54 | | 4 | admin  | 127.0.0.1 | 2010-11-14 21:56:54 | | 5 | admin  | 127.0.0.1 | 2010-11-15 21:56:54 | +--+-------+--------+------------------+ ■売上げ金額テーブル mysql> select * from sales; +--+-------+------+------------------+ | id | user_id | amount| created_at      | +--+-------+------+------------------+ | 1 | admin  |  5000 | 2010-11-13 21:56:54 | | 2 | admin  |  5000 | 2010-11-14 21:56:54 | | 3 | admin  |  5000 | 2010-11-15 21:56:54 | | 4 | admin  |  5000 | 2010-11-15 21:56:54 | +--+-------+------+------------------+

    • ベストアンサー
    • MySQL
  • 複数表での集計値について

    以下のようなテーブル構造。SQLで 集計値を求めたいのですがCNTの値が tableCの件数を取得してしまいます。 (取得したいのはtableA.ID毎のtableB.ID2の件数) 宜しくお願いします。 ・tableA ------------- ID ------------- ・tableB ------------- ID | ID2 ------------- ・tableC ------------- ID2 | TIME ------------- SELECT tableA.ID ,SUM(tableC.TIME) AS TIME ,COUNT(tableB.ID2) AS CNT FROM tableA,tableB,tableC WHERE tableA.ID = tableB.ID AND tableB.ID2 = tableC.ID2 GROUP BY tableA.ID

    • ベストアンサー
    • MySQL
  • MySQL: 複数テーブルのcount

    複数テーブルの総行数(count値)を取得したいのですが、下記だとテーブルA,B,Cの結果が バラバラに出力されてしまいます。 【入力条件】 mysql> select COUNT(*) AS 列ID from テーブルA      union all      select COUNT(*) AS 列ID from テーブルB      union all      select COUNT(*) AS 列ID from テーブルC; 【出力結果】       +-----+       | 列_ID |       +-----+       |  1  | ←テーブルAの合計:行数       |  2  | ←テーブルBの合計:行数       |  3  | ←テーブルCの合計:行数       +-----+ 総行数:6を一発で取得するには、どのようにしたらよろしいのでしょうか? 大変恐縮ですが、ご教授よろしくお願いいたいます。 <<MySQLのverは5,5です。

    • ベストアンサー
    • MySQL
  • MySQLでJOINを使った検索について

    MySQLについて質問があります。 下記のような2テーブルがあります。 ----------------------------- ・item 商品情報を格納。 ・usersitem ユーザーが所有している商品の個数を格納。 ----------------------------- この2つのテーブルから2つのリストを取り出したいと考えています。 【A】特定のユーザーが複数所有している商品の一覧 【B】特定のユーザーが所有していない商品の一覧 【A】は出来たのですが、【B】のSQL文がわかりません。 どうかご教授いただけませんでしょうか。 ■テーブルを作成したSQL ----------------------------- CREATE TABLE `test`.`item` ( `itemid` SERIAL NOT NULL DEFAULT NULL UNIQUE, `itemname` VARCHAR( 256 ) ); CREATE TABLE `test`.`usersitem` ( `id` SERIAL NOT NULL DEFAULT NULL UNIQUE, `userid` INT, `itemid` INT, `count` INT ); ----------------------------- ■【A】を実現したSQL 条件:userid「1」のユーザーがcount「2」以上の一覧。 ----------------------------- SELECT * FROM `item` LEFT JOIN `usersitem` ON (`item`.`itemid` = `usersitem`.`itemid`) WHERE `usersitem`.`userid` = 1 AND `usersitem`.`count` >= 2 ----------------------------- ■【B】を実現しようとしたが違っていたSQL 条件:userid「1」のユーザーがcount「0」以下、または登録されていない一覧。 ----------------------------- SELECT * FROM `item` LEFT JOIN `usersitem` ON (`item`.`itemid` = `usersitem`.`itemid`) WHERE ( `usersitem`.`userid` = 1 AND `usersitem`.`count` <= 0 ) OR `usersitem`.`userid` != 1 ----------------------------- 結果: 個数情報が登録されていない商品が表示されない。 違うユーザーの情報が表示されてしまう。 使用しているのは MySQL 5.5.29です。 よろしくお願いいたします。

  • 同じ構成のテーブルの条件付き結合

    同じ構成のテーブルがテーブル1~テーブル6まであり、6つのテーブルを集計するSQLを作成しています。 カラムは ID、名前、日付1、日付2、ステータス1、ステータス2 です。 IDがプライマリーキーになっており、テーブル1にしかないIDや全てのテーブルに登録されているIDなどもあり、IDの一覧を作成したいです。 IDが重複していた場合、名前はどれを残しても構わないのですが(どのテーブルか指定する必要があるのならばとりあえずテーブル番号が若い方の名前を残すようにしてください。) 日付1は最古の日付 日付2は最新の日付 ステータスは共に優先順位が1>2>0>nullとなっており優先順位が高いほうを残すようにし、ステータスが両方nullの場合は集計しないようにしたいです。 テーブル1 ID、名前、日付1、日付2、ステータス1、ステータス2 111、あああ、2001/1/1、2001/1/1、2、2 222、いいい、2001/1/1、2001/1/1、2、2 333、ううう、2001/1/1、2001/1/1、null、null 555、おおお、2001/1/1、2001/1/1、2、2 テーブル2 ID、名前、日付1、日付2、ステータス1、ステータス2 111、aaa、2002/2/2、2002/2/2、0、null 222、iii、2002/2/2、2002/2/2、1、0 333、uuu、2002/2/2、2002/2/2、null、null 444、eee、2002/2/2、2002/2/2、1、1 結果 ID、名前、日付1、日付2、ステータス1、ステータス2 111、あああ、2001/1/1、2002/2/2、2、2 222、いいい、2001/1/1、2002/2/2、1、2 444、eee、2002/2/2、2002/2/2、1、1 555、おおお、2001/1/1、2001/1/1、2、2 このようなことを6つのテーブルを集計して行いたいです。 SELECT t1.id,MIN(t1.日付1)as 日付1,MAX(t1.日付2)as 日付2,CASE When t1.ステータス1="1" then "1" else t1.ステータス1 end ,CASE When t1.ステータス2="1" then "1" else t1.ステータス2 end FROM( SELECT * FROM テーブル1 UNION All SELECT * FROM テーブル2 UNION All SELECT * FROM テーブル3 UNION All SELECT * FROM テーブル4 UNION All SELECT * FROM テーブル5 UNION All SELECT * FROM テーブル6 )as t1 WHERE NOT(t1.ステータス1 IS null AND t1.ステータス2 IS null) GROUP BY id 日付は出来ていると思うのですが、ステータスで1の優先がまだ出来ていないので修正お願いいたします。

    • ベストアンサー
    • MySQL