A～B にデータがある人以外の人をHITさせる

質問

顧客検索で、ある期間内に来店がない人を検索しようとしていますが、全然いい手が思い浮かびません。

具体的には、

以下テーブルにて、
CREATE TABLE raiten (m_id char(8), dt date);
insert into raiten values
("a01", '2004-07-01'),
("a01", '2004-08-02'),
("a01", '2004-09-01'),
("a01", '2004-10-01'),
("a02", '2004-07-02'),
("a02", '2004-10-10'),
("a03", '2004-07-10'),
("a03", '2004-11-01'),
("a03", '2004-12-01'),
("a04", '2004-07-01'),
("a04", '2005-07-02'),
("a05", '2004-07-01'),
("a05", '2004-08-02'),
("a06", '2004-09-02'),
("a07", '2004-06-10'),
("a07", '2004-07-15'),
("a07", '2004-09-02'),
("a08", '2004-07-15'),
("a09", '2004-07-05'),
("a09", '2004-07-15'),
("a09", '2004-07-30'),
("a09", '2004-08-15'),
("a09", '2004-09-15'),
("a09", '2004-10-15'),
("a10", '2004-07-15'),
("a10", '2004-07-06'),
("a10", '2004-09-15');

2004年08月に来店がない人を検索したいのです。
(2004-08-01 ～ 2004-08-31 まで）

来店がある人ならばすぐ出るのですが、
来店がない人は、その期間来店がないが他の期間に来店があるため、うまくデータが出てこないのが現状です。

8月に来店があるのは a01,a05,a09 の三人なので、
それ以外が出てくるようにはできないでしょうか。

回答No.6

taka451213 · ベストアンサー

こんばんは。

補足、拝見しました。
なるほど！！その方がいいですね。
BETWEENを使う方がかっこいいかも・・・？

では頑張ってください。
(^^ゞ

回答No.5

taka451213

こんにちは。

ちょっとベタでした・・・。
SELECT m_id, SUM(CASE DATE_FORMAT(dt, '%Y/%m') WHEN '2004/08' THEN 1 ELSE 0 END) AS cnt
FROM raiten
GROUP BY m_id
HAVING cnt = 0

これかな？
(^^ゞ

お礼

さらにありがとうございます！

こちらの構文でもそのままで想定した結果が得られました。
こちらのほうが若干シンプルかな？

それにしても副問い合わせがなくても何とかなるんですね＾＾

何度もありがとうございました！！

補足

ちなみに、日単位でデータを出すには

SELECT m_id, SUM(CASE WHEN ('2004-08-01' <= dt and dt <= '2004-08-31') THEN 1 ELSE 0 END) AS cnt FROM raiten GROUP BY m_id HAVING cnt = 0

で出てきました。

回答No.4

taka451213

こんにちは。

すみません・・・、ちょっと（かなり？）勘違いしてました・・・。

テンポラリーテーブルを使用しても、副問い合わせが使えないのでつらいですね。
というわけで・・・、

SELECT m_id, COUNT(m_id) AS cnt1, SUM(CASE DATE_FORMAT(dt, '%Y/%m') WHEN '2004/08' THEN 0 ELSE 1 END) AS cnt2
FROM raiten
GROUP BY m_id
HAVING cnt1 = cnt2

m_idごとの出現回数と、m_idごとで指定月以外の出現回数合計を比較して、同じだったら指定月には来店していない・・・、という事ですが・・・。

これまた、動くかどうかはわかりません・・・。
(^^ゞ

お礼

何度もありがとうございます。
テストした結果、想定通りの結果になりました！

構文はそのまま使えました。
ありがとうございました！

回答No.3

taka451213

こんにちは。

副問い合わせが使えないのは、やっぱり厳しいですね・・・。

以下、ちょっとインチキっぽいですが、

SELECT m_id, CASE DATE_FORMAT(dt, '%Y/%m') WHEN '2004/08' THEN '0' ELSE '1' END AS 条件
FROM raiten
GROUP BY m_id, 条件
HAVING 条件 = '1'

テストしてない（MySQLの構文がわかってない・・・）ので、動かないかも知れませんが・・・、
考え方としてはあってると思います。

後はMySQLに詳しい人にお任せします・・・。
(^^ゞ

お礼

ありがとうございます。
テストをいたしましたが、a01 ～ a10 の全てのデータがHITしてしまいます。

確かにやりたいことはわかるのですが、該当顧客のデータがその月以外にも複数あるためにほかの月に反応してうまくデータが引っ張れなかったりします。

別に一文にこだわっているわけではありませんので、実現できる方法が分かればよいのですが、なかなかうまくはいかないようです。

8月にきている人を出すことはできるので、
メンバーの元データから
このHITしたデータを消せればできるのでしょうが、、、

やっぱり現状のデータだけでは無理なのですかね。

PHPなどでif文でまわせれば簡単なんだけど、
データが多くて重いだろうから使えないし。

回答No.2

jurarumin

仮想テーブルを使えばできない事もありません。
仮想テーブルで８月に来店した人のIDを取得して、全体のデータから除外すれば。

例は、イメージですので正しく動作する保障はありません。

（例）
Select
raiten.m_id
From
raiten,
(Select m_id
From raiten
Where dt BETWEEN '2004-08-01' AND '2004-08-31' ←日付型に変換する？
) as In_raiten
Where
raiten.m_id <> In_raiten.raiten
Group By
raiten.m_id

お礼

ありがとうございます。
テストをいたしましたが全てのデータが出てきてしまいました。
（ただし副問い合わせが使えない関係上、テンポラリテーブルを使用しています。これが原因だと思います。）

CREATE TEMPORARY TABLE a SELECT m_id as m,dt as d FROM raiten WHERE dt BETWEEN '2004-08-01' AND '2004-08-31' GROUP BY m;

SELECT * FROM raiten,a WHERE m_id <> m GROUP BY m_id;

回答No.1

helonpa

DBソフトウェアに依存するSQLもありますので、環境を書かれては如何でしょうか。

補足

SQLはMysqlのカテゴリなのでMysqlです。
バージョンは 4.0.21 です。

テストはWin2000serverで運用していて、本番はFreeBSDを使用しています。
ちなみにWin2000serverはPS4を適用しています。
よろしくお願いします。