条件指定と重複チェック後のカウント

質問

MySQLを最近触り始めたばっかりで
頭を抱えています
どなたか知恵を貸してくれませんでしょうか；

ID |code|process
100|A |a001
100|A |a001
100|A |a002
100|B |a003
200|C |a004

*ID単位のAに対するprocess数を表示したい
*重複processは1とカウント

ID |process数
100|2
200|0

最終的に上記のように表示させたいのですが
方法が分かりません。。。

whereで A を条件指定にすると
code Aを持たないID 200が表示されなくなるので
whereを使用することもできず・・・

条件指定と重複チェック後のカウントが出来る方法はありませんでしょうか？

回答No.3

chukenkenkou · ベストアンサー

#2回答者です。

>このSQLをちょこっと変更すれば
>すぐ出来るのかなぁと思ってたのですが

やりたいと言っていることとやっていることが、違っていますよ？
テストデータによって、一見、近い結果が得られているように見えているだけで、やっていることはデタラメなんですけど？

#2で示したSQLは、分解すればMySQL 4.1でもすぐに応用が利くし、多くのヒントが含まれていたはずなのに、見向きもされなかったようでまったく不本意です。

MySQL 4.1で使えないのは、インラインビューだけなので、これを一時表にすれば使えます。

【表定義＆データ】
create table t1
(id int,
code char(1),
process char(4));
insert into t1 values
(100,'A','a001'),
(100,'A','a001'),
(100,'A','a002'),
(100,'B','a003'),
(200,'C','a004');

【一時表作成＆検索】
-- 04-01
drop temporary table if exists wt1;
create temporary table wt1
select * from t1 where code='A';
select x.id,count(distinct y.process) as process数
from t1 as x left join wt1 as y
on x.id=y.id
group by x.id
;

お礼

ありがとうございます。
無事に表示することができました。

#2で教えて頂いたSQLは
どこをどうすればいいのかさっぱり分からなかったので
流してしまいました。
大変申し訳ございませんでした。

回答No.2

chukenkenkou

MySQLの質問をする場合は、バージョンを明記してください。
せっかく具体的なSQL例を提示しても、あなたの環境では使えない場合があり得ます。

【SQL例】MySQL 5.0以降
select x.id,count(distinct y.process)
from t1 as x left join (select * from t1 where code='A') as y
on x.id=y.id
group by x.id
;

【備考】
条件式やORDER BY、GROUP BYなどで演算を行うと、インデクスが定義されていても有効利用されないため、性能を重視する場合は注意してください。

お礼

ご指摘ありがとうございます。

調べてみましたら
mysql Ver 14.7 Distrib 4.1.18, for Win32 (ia32)
と表示されました。

回答No.1

nikuq

こんな感じでしょうか？

select
ID,count(concat(ID,code,process)) as process_num
from TABLE_NAME
group by concat(ID,code,process)

テストしてないので、できてるかわかりませんが、
ID,code,processで一つの単語として認識させ、それをグループ化し、グループ内のレコード数をカウントしています。
これだと、表示結果は恐らく、

ID|code|process
100|2
100|1
200|1

となると思います。
最初の100|1は、100|A|a001の合計数で、
2行目の100|1は、100|B|1002の合計数で、
3行目の20|1は、200|C|a004の合計数
になります。
200|C|a004を0としてカウントする方法は、あるのかなぁ？
ちょっと分かりません。
すみません。中途半端で。。。

お礼

回答ありがとうございます。

教えて頂いたconcatを使って試してみましたが
Aを条件として絞り込む事が上手くできませんでした。。。

今、悩んでいるprocess数をカウントする前に
IDごとのcode数を数える文を作成したSQLは

select ID,
sum(case when code = 'A' then 1 else 0 end) as code_count
from table_A
group by ID;

結果
ID|code_count
100|3
200|0

と表示することが出来ました。

このSQLをちょこっと変更すれば
すぐ出来るのかなぁと思ってたのですが
どうにも上手くいかないです；