mysql:14102
From: 忠犬 KEN公 <忠犬 KEN公 <ken_ken_1962@xxxxxxxxxx>>
Date: Sun, 08 Jul 2007 21:14:58 +0900
Subject: [mysql 14102] Re: ログのカウント方法
(1)left joinで日付の範囲をwhereで指定すると、最終結果からその日付の範囲外の データが 除外されます。日付の範囲外のデータも欲しい場合、onで条件指定します。 (2)count関数中にcase式を入れて、一度で複数の日付の範囲を集計できるようにしま す。 (3)標準SQLや他のRDBMSでは、group by使用時、selectで選択できるのは、group by で 指定した列か集計関数(集合関数)だけです。 select c1,c2,count(*) from t1 group by c1 という指定は、「c2」がgroup byに含まれていないため、標準SQLや他のRDBMSでは 文法 エラーになります。MySQLでは独自仕様として、文法エラーとしないが、c1でグ ループ化することで、 c2も一意にならないなら、結果は保証しないとしています。 今回は、「部品一覧」表のidとnameで一対一の関係のため、MySQLの拡張仕様を利 用できます。 <SQL例> select material.id, name, count(case when dt between '2007-03-01 00:00:00' and '2007-03-31 23:59:59' then 1 else null end) as a1, count(case when dt between '2007-04-01 00:00:00' and '2007-04-30 23:59:59' then 1 else null end) as a2, count(case when dt between '2007-05-01 00:00:00' and '2007-05-31 23:59:59' then 1 else null end) as a3, count(case when dt between '2007-06-01 00:00:00' and '2007-06-30 23:59:59' then 1 else null end) as a4, count(case when dt between '2007-07-01 00:00:00' and '2007-07-31 23:59:59' then 1 else null end) as a5 from material left join log_mate on material.id=log_mate.mate_id and dt between '2007-1-31 0:0:0' and '2007-7-31 23:59:59' group by id,name order by id,name ; >久光と申します > > >MySQL 5.0.27です。 > >下記のような部品一覧テーブルと部品の使用履歴テーブルがあります。 > >部品一覧(material) >============= >id name >------------- >1 ネジ >2 クギ >3 ワイヤ >4 フタ >============= >(実物は約9000行あります) > > >使用履歴(log_mate) >================================ >id 部品id 日時 > (mate_id) (dt) >-------------------------------- > 1 4 2007-03-31 09:52:11 > 2 1 2007-03-31 10:47:18 > 3 3 2007-04-06 03:27:43 > 4 1 2007-04-27 14:52:10 > 5 2 2007-05-22 10:19:59 > 6 2 2007-06-04 19:10:45 > 7 1 2007-06-05 05:43:35 > 8 4 2007-06-23 00:52:19 > 9 3 2007-06-24 07:06:32 >10 1 2007-07-08 12:30:18 >================================ >(実物は約79万行あります) > > >半年に一回、部品別使用状況一覧を作成しているのですが > >SELECT material.id, material.name, count(log_mate.id) >FROM `material` left join `log_mate` on material.id=log_mate.mate_id >WHERE log_mate.dt >= "2007-1-1 0:0:0" >AND log_mate.dt < "2007-2-1 0:0:0" >GROUP BY material.id >ORDER BY material.id > >このようなSQLを日付の部分を書き換えながら6回実行して 結果をEXCELに貼 >付けてます。 > > これの不便なところは期間内に使用がなかった部品がリストから除外 >され >てしまうので、6回分をただEXCELに貼付けただけでは部品 >名がずれてしまう >のです。count(log_mate.id)が0も出してくれると助かる のですが。 > > >1)期間内に使用がない部品については0回として、下記のよう にとにかく >部品一覧テーブル(material)にあるもの全ての使用回数を出力す >るにはどん >なSQLにしたらいいでしょうか。 >================== >id name a >------------------ >1 ネジ 34 >2 クギ 12 >3 ワイヤ 0 >4 フタ 46 >================== > > >2)1回のSQL文で下記のような一覧が出力できるとなおう >れしいのですが可 >能でしょうか。 >========================================== >id name a1 a2 a3 a4 a5 a6 >------------------------------------------ >1 ネジ 34 21 0 5 45 36 >2 クギ 12 32 22 14 39 0 >3 ワイヤ 0 15 38 17 26 13 >4 フタ 46 37 31 28 41 35 >========================================== > > > PHPと組み合わせてブラウザに一覧表を作成させる方法ならで きると思う >ので、そうしちゃおうかなとも思っているのですが、SQLだけで できる方法 >があるならその方が便利だし知識も増えて応用がきくと思うので知って >おき >たいのです。よろしくお願いします。 > > > >================================= >== HISAMITSU ISSEI == >== DS1I-HSMT@xxxxxxxxxx == >http://www.ne.jp/asahi/pro/his/ >================================= > > > _________________________________________________________________ 職場でのメッセンジャー活用法を全5話でお届け!続々公開中。 http://messenger.live.jp/oshigoto/index.htm
14100 2007-07-08 16:41 [Hisamitsu Issei <ds1] ログのカウント方法 14101 2007-07-08 19:04 ┣["F.Y" <fumi_sby@xxxx] -> 14102 2007-07-08 21:14 ┗[忠犬 KEN公 <ken_ken_] @ 14103 2007-07-09 21:19 ┗[Hisamitsu Issei <ds1] 14104 2007-07-09 21:55 ┣["F.Y" <fumi_sby@xxxx] 14106 2007-07-10 11:35 ┗[hama <smilestyle55@x]