顧客＆商品＋指定文字列を含む、最新のデータ取得

質問

お世話になります。
前回立てたトピック（http://okwave.jp/qa/q8565634.html）でサンプルデータにミスがあったため、立て直しです。

※顧客＆商品ごとの本申込という文字列が含まれる最新データを取得したい
※商品名の長さは変動します（商品名には実際は番号はありません）
※MySQL5です。

どうぞお知恵をお貸しください。

■テーブルデータ
CREATE TABLE IF NOT EXISTS `TABLE1` (
`SEQ_ID` mediumint(8) NOT NULL AUTO_INCREMENT COMMENT 'シーケンスID',
`TO_USER_NM` varchar(128) DEFAULT NULL COMMENT '顧客',
`MAIL_TITLE` varchar(256) DEFAULT NULL COMMENT '商品',
`SEND_TIME` datetime NOT NULL COMMENT '購入日時',
PRIMARY KEY (`SEQ_ID`)
);

INSERT INTO `TABLE1` (`SEQ_ID`, `TO_USER_NM`, `MAIL_TITLE`, `SEND_TIME`) VALUES
(1 , '顧客A', '『商品1』仮申込', '2014-03-01 13:07:43'),
(2 , '顧客C', '『商品1』仮申込 / 変更1', '2014-03-02 10:06:54'),
(3 , '顧客E', '『商品1』仮申込', '2014-03-03 10:06:54'),
(4 , '顧客E', '『商品1』本申込', '2014-03-04 13:33:51'),
(5 , '顧客B', '『商品1』本申込', '2014-03-05 14:58:41'),
(6 , '顧客A', '『商品1』本申込 / 変更1', '2014-03-06 16:42:56'),
(7 , '顧客D', '『商品1』本申込 / 変更2', '2014-03-07 17:27:33'),
(8 , '顧客C', '『商品1』本申込 / 変更1', '2014-03-08 13:56:29'),
(9 , '顧客A', '『商品2』仮申込', '2014-03-09 18:44:05'),
(10, '顧客A', '『商品2』仮申込 / 変更1', '2014-03-10 10:43:14'),
(11, '顧客C', '『商品1』本申込 / 変更2', '2014-03-11 08:51:02'),
(12, '顧客C', '『商品1』本申込 / 変更3', '2014-03-12 20:33:39'),
(13, '顧客A', '『商品2』本申込', '2014-03-14 18:52:44'),
(14, '顧客B', '『商品1』仮申込', '2014-03-15 16:13:47'),
(15, '顧客B', '『商品1』本申込 / 変更3', '2014-03-16 21:07:35'),
(16, '顧客B', '『商品1』本申込 / 変更4', '2014-03-18 21:07:34'),
(17, '顧客B', '『商品3』仮申込', '2014-03-19 21:42:46'),
(18, '顧客B', '『商品3』本申込', '2014-03-20 22:14:10'),
(19, '顧客A', '『商品2』本申込 / 変更1', '2014-03-21 23:44:06'),
(20, '顧客A', '『商品1』本申込 / 変更5', '2014-03-22 08:22:59'),
(21, '顧客A', '『商品2』仮申込', '2014-04-01 13:07:43'),
(22, '顧客A', '『商品2』本申込 / 変更2', '2014-04-02 10:06:54'),
(23, '顧客B', '『商品2』仮申込', '2014-04-03 10:06:54'),
(24, '顧客B', '『商品2』仮申込 / 変更1', '2014-04-04 13:33:51'),
(25, '顧客B', '『商品2』本申込', '2014-04-05 14:58:41'),
(26, '顧客F', '『商品1』仮申込', '2014-04-06 16:42:56'),
(27, '顧客F', '『商品1』本申込', '2014-04-07 17:27:33'),
(28, '顧客G', '『商品3』仮申込', '2014-04-08 13:56:29'),
(29, '顧客F', '『商品1』本申込 / 変更1', '2014-04-09 18:44:05'),
(30, '顧客G', '『商品3』本申込', '2014-04-10 10:43:14'),
(31, '顧客A', '『商品1』本申込 / 変更6', '2014-04-11 08:51:02'),
(32, '顧客A', '『商品2』本申込 / 変更3', '2014-04-12 20:33:39'),
(33, '顧客F', '『商品1』本申込 / 変更2', '2014-04-14 18:52:44'),
(34, '顧客C', '『商品3』仮申込', '2014-04-15 16:13:47'),
(35, '顧客C', '『商品3』本申込', '2014-04-16 21:07:35'),
(36, '顧客H', '『商品1』仮申込', '2014-04-18 21:07:34'),
(37, '顧客H', '『商品1』本申込', '2014-04-19 21:42:46'),
(38, '顧客H', '『商品1』本申込 / 変更1', '2014-04-20 22:14:10'),
(39, '顧客E', '『商品2』仮申込', '2014-04-21 23:44:06'),
(40, '顧客E', '『商品2』仮申込 / 変更1', '2014-04-22 08:22:59');

■取得したいデータ
38, '顧客H', '『商品1』本申込 / 変更1', '2014-04-20 22:14:10'
35, '顧客C', '『商品3』本申込', '2014-04-16 21:07:35'
33, '顧客F', '『商品1』本申込 / 変更2', '2014-04-14 18:52:44'
32, '顧客A', '『商品2』本申込 / 変更3', '2014-04-12 20:33:39'
31, '顧客A', '『商品1』本申込 / 変更6', '2014-04-11 08:51:02'
30, '顧客G', '『商品3』本申込', '2014-04-10 10:43:14'
25, '顧客B', '『商品2』本申込', '2014-04-05 14:58:41'
18, '顧客B', '『商品3』本申込', '2014-03-20 22:14:10'
16, '顧客B', '『商品1』本申込 / 変更4' '2014-03-18 21:07:34'
12, '顧客C', '『商品1』本申込 / 変更3' '2014-03-12 20:33:39'
7 , '顧客D', '『商品1』本申込 / 変更2' '2014-03-07 17:27:33'
4 , '顧客E', '『商品1』本申込', '2014-03-04 13:33:51'

回答No.4

yambejp · ベストアンサー

＞メール情報をさらにテーブルにして細分化したりしない方向で、そのまま取得できないか

はっきりいえば、SQLでやる作業ではない

しかしできる・できないという意味ではできるのでできるので一応書いておきます。
しかし、こんなことやってたらRDB上達しないですよ
（インデックスは絶対効かないし効率最悪で、データが増えるとすぐいきづまる・・・）

//例
select *
from TABLE1
where (
TO_USER_NM
,substr(MAIL_TITLE,@pos:=instr(MAIL_TITLE,'『')+1,instr(MAIL_TITLE,'』')-@pos)
,SEND_TIME
) in
(
select TO_USER_NM as 顧客
,substr(MAIL_TITLE,@pos:=instr(MAIL_TITLE,'『')+1,instr(MAIL_TITLE,'』')-@pos) as 商品
,max(SEND_TIME)
from TABLE1
where substr(MAIL_TITLE,instr(MAIL_TITLE,'』')+1,3)='本申込'
group by 顧客,商品

)
order by SEND_TIME desc

※ポイント
substr(MAIL_TITLE,@pos:=instr(MAIL_TITLE,'『')+1,instr(MAIL_TITLE,'』')-@pos)
というのが商品名です

お礼

何度も恐れ入ります。
お教えいただいたSQLで取得したかったデータを取得することが出来ました。
こちらのわがままにつきあってくださり本当にありがとうございます。

客先のシステムをヘタにいじらずに情報を取得出来てほっとしております。
ありがとうございました。

回答No.3

yambejp

ちゃんとやるなら
(1)顧客テーブル、商品テーブルを別途つくって正規化する
(2)申込状況については正規化するかsetやenumで処理する
(3)変更については仕様がわからないのでなんともいえない
メモレベルならvarcharで持ってもいいし、標準化するなにかがあるなら
それをもとに正規化してもよい

としたうえで

(4)それを前提に
レコードのID,顧客ID,商品ID,申込状況,変更状況,購入日時
の項目を整備したテーブルをつくり集計する

補足

何度もご意見いただきありがとうございます。

顧客テーブル、商品テーブル等は存在するのですが、今回やりたいのは、メールでのやり取りを保存したテーブルからのメール情報自体の取得のため、なんとかメール情報をさらにテーブルにして細分化したりしない方向で、そのまま取得できないかとあがいているところです。
不可能であれば本申込だけでも絞れれば手動で出来ることではあるので、もし出来れば…と質問させていただいております。

このままもうしばらく質問は締め切らずにおきたいと思います。
いろいろありがとうございます。

回答No.2

bin-chan

やらかしてました。

Ｘ）where tbl2.MAIL_TITLE like '%本申込%
○）where tbl2.MAIL_TITLE like '%本申込%'

補足

確認しました、訂正ありがとうございます。

回答No.1

bin-chan

環境が無いので試験してません。
whereのワイルドカードが違うかも。
字下げで全角スペース使ってるので注意してください
カッコの外のwhereに出てくる3列をカッコで囲むのがポイントのはず。

--先にカッコの中を実行してみてください。
　select tbl2.TO_USER_NM, tbl2.MAIL_TITLE, max(SEND_TIME) max_SEND_TIME
　from TABLE1 tbl2
　where tbl2.MAIL_TITLE like '%本申込%
　group by tbl2.TO_USER_NM, tbl2.MAIL_TITLE
;
--ここまで　がOKなら以下を実行

--以下、全文、
select tbl1.*
from TABLE1 tbl1
where (tbl1.TO_USER_NM, tbl1.MAIL_TITLE, tbl1.SEND_TIME) in
(
　select tbl2.TO_USER_NM, tbl2.MAIL_TITLE, max(SEND_TIME) max_SEND_TIME
　from TABLE1 tbl2
　where tbl2.MAIL_TITLE like '%本申込%
　group by tbl2.TO_USER_NM, tbl2.MAIL_TITLE
)
;

補足

回答ありがとうございます。
試してみたのですが、本申込を含むレコードが全て出てきてしまいました。
カッコ内、全文のどちらのSQLでも同じ（並び順だけ違う）でした。

また、こちらの質問の書き方が悪かったせいだと思いますが、商品（MAIL_TITLE）カラムで同じ商品として扱いたいのは『』内の名称のみです。
商品カラム全体ではありません。
前回のSQLで使用していた商品名取得用のSQL部分は、
substr( MAIL_TITLE, 1, instr( MAIL_TITLE, '』' ) -1 )
です。
回答のお役に立てればいいのですが。。。

もう少し悩んでみます。