id,urlというテーブルにおいて、url中のファイル名が同じものを抜

id,urlというテーブルにおいて、url中のファイル名が同じものを抜き出したい。

こんなテーブルがあります。
id url
1 http://okwave.jp/test/abc.wav
2 http://okokok.jp/456.jpg
3 http://okokok.jp/abc.wav
......続く

urlは必ずファイル名.拡張子で終わっています。

これらのURL中のファイル名.拡張子が同じもの(上のid1とid3などが例です)を抜き出して、
SELECT ファイル名,COUNT(同じだった数)

というふうに表示させたいのですが、どのようなSQLを組めばよいでしょうか？
PHPでも構いません。

ご教授願います。

No.3 ベストアンサー 回答者： yambejp 回答日時： 2009/12/24 23:44

なんとも言えませんが、とりあえず中間テーブルのようなものを

つくって、indexを張る必要があるかもしれませんね。

create table hoge(id int NOT NULL,fname varchar(100),INDEX(id,fname));
insert into hoge select id,SUBSTRING_INDEX(url, '/',-1) FROM 元テーブル;
select fname,count(*) FROM hoge group by fname;

この回答へのお礼

できました！
何分サーバーが古いもので、実行に10分もかかりましたが……

本当にありがとうございました。
大変お世話になりました！

お礼日時：2009/12/25 00:23

No.2 回答者： yambejp 回答日時： 2009/12/24 22:55

すみません、曲解しました。

SUBSTRING_INDEX()を使えば簡単かもしれません。

SELECT SUBSTRING_INDEX(url, '/',-1) AS FNAME,COUNT(*) AS COUNT FROM test GROUP BY FNAME;

1つしかないものを除外するとか必要でしたらHAVINGをつかってください

この回答へのお礼

おお！この関数は知りませんでした。便利です！
追加で申し訳ないのですが、実はさらに問題が在るのです。

レコード数が50万件程度ありまして…重いです。
いただいたSQL文の語尾に
SELECT SUBSTRING_INDEX(url, '/',-1) AS FNAME,COUNT(*) AS COUNT FROM test GROUP BY FNAME LIMIT 0,10000
としたところで、LIMITより後ろの文を全て実行したあとにLIMITをするわけですから、意味がありません。

一応試してみたところ、重すぎてサーバーごと吹っ飛んでしまいました。
なにか打開策はありますでしょうか？

お礼日時：2009/12/24 23:21

No.1 回答者： yambejp 回答日時： 2009/12/24 21:14

拡張子を最初から分けておけばよいのでは？

無理やりするならこんな感じでも・・・

SELECT
if(substr(url, length(url) - 1,1)='.',substr(url, length(url) - 0),
if(substr(url, length(url) - 2,1)='.',substr(url, length(url) - 1),
if(substr(url, length(url) - 3,1)='.',substr(url, length(url) - 2),
''))) AS ext
,count(*)
FROM `test` WHERE 1
group by ext

拡張子は３文字であるという保証はありません。４文字以上の
場合は適宜調整ください

この回答への補足

少し補足させてください。

SELECT ファイル名,(同じだった数)
というのは、例えば、

id url
1 ～abc.wav
2 ～123.wav
3 ～123.wav
4 ～abc.wav
5 ～123.wav

の場合、期待する出力は

ファイル名 同じだった数
abc.wav 2
123.wav 3

のようなイメージです。
また、拡張子は必ず3文字です。

補足日時：2009/12/24 22:35