MySQLで、日付条件での抽出SQL文で困っています。

Javaで、MySQLのSQL文を作っていますが、
下記の抽出結果を実現できません。

もしよろしければ、SQL文をご記述頂けると幸いです。

仕様
１．今日以降に予約のある顧客の予約＋顧客データーは、全て抽出。※除く、論理削除レコード。
２．今日以降に予約がなく、過去に予約がある顧客の場合は、過去の最大の予約日＋予約開始時間のレコードを１つ抽出。※除く、論理削除レコード。
３．過去、未来に予約のない顧客は、顧客情報のみ抽出。

＜テーブル例＞

■顧客テーブル contomer

※idは、プリマリキ―

id name tel del

1 山田 03-0000-0000 0
2 田中 03-0000-0001 1 <-- 論理削除レコード
3 北村 03-0000-0002 0

■予約テーブル revation

※idは、プリマリキ―

id costomer_id revation_date revation_starttime del

1 1 2015-12-25 09:00 0
2 1 2015-12-25 11:00 1 <--論理削除レコード
3 1 2015-12-26 12:00 0
4 2 2015-12-24 17:00 0
5 2 2015-12-24 13:00

//////////////////////////////////////////////////////////////////////////////////////////////////////////////

■抽出結果

costomer_id name tel revation_date revation_starttime

1 山田 03-0000-0000 2015-12-25 09:00
1 山田 03-0000-0000 2015-12-26 12:00
2 田中 03-0000-0001 2015-12-24 17:00
3 北村 03-0000-0002 null null

以上です。

izkn

2015/12/25 04:01

こちらの質問が他のユーザから「質問の範囲が広すぎる」という評価を受けていますわからない点を明確にし、調査したこと・試したことと共に記入していただくと、回答が得られやすくなります。

liguofeng29

2015/12/25 04:02

Orlofsky

2015/12/25 04:07

退会済みユーザー

2015/12/25 04:14

2015/12/25 05:56

2015/12/25 06:37

2015/12/25 07:37

2015/12/25 07:59

2015/12/26 00:56

退会済みユーザー

2015/12/26 12:15

moredeep

2015/12/28 05:53

退会済みユーザー

2015/12/30 14:08

行動規範の内容に同意します

回答3件

掲示板はSQLを丸投げする場ではありません。SQL入門から勉強してください。
MySQL初心者入門講座

投稿2015/12/25 04:10

Orlofsky

総合スコア16417

White_Rabbit

2015/12/25 06:22

えっ、ここのサイト規約には、技術の質問は、初心者は禁止などの規定がありませんが、 Orlofskyさん、ここのサイトの使い方や運営方針を読んでから、書きこみしましょう。 Orlofskyさんには、まだむずかしいのかもしれませんが、社会人になると、それらをまもることを学びますor身に付きます。従って、他の事もそうですが、ルールにそって行動できるよう、且、規則に沿ったことができるようになるとよいでしょう。頑張ってください。

tanat

2015/12/25 08:37 編集

横からすみません。推奨していない質問 https://teratail.com/help/avoid-asking >コードをください・デバッグしてください等の丸投げの質問何かを作りたいのでコードを書いてほしい、学校の課題を解いてほしい等の質問は、具体的にプログラミングで困っている質問ではないと考え、推奨していません。問題や質問は実際に調査や作業に取り組み、具体的なところで生まれると考えるためです。まずは実際に作業に取り組み、つまづいたところで投稿をしてみてください。と明記されています。これは質問フォームにもわかりやすいリンクで記載されています。質問からは「何をどのように試して、どうつまづいたのか」についての情報がが一切ないので「丸投げ」と判断されても仕方が無いと思いますよ。ここのサイトの使い方や運営方針を読んでから、書きこみしましょう。

kutsulog

2015/12/25 08:44

初心者の質問禁止とは書いてありませんが、「評価を下げる」のリンクにマウスカーソルを合わせると丸投げはサイトポリシーに反する質問と定義されて非推奨となっていますから Orlofskyさんの回答はサイトポリシーにのっとったものと判断できますよ?

退会済みユーザー

2015/12/25 09:19

”規約”の話ならWhite_Rabbitさんの言っていることは間違ってないです。ただ、他の方々の回答にもあるように”課題の丸投げ”は推奨されていません。ここはあくまでも問題に対してサポートする場であり、問題の解答をもらう場ではありません。ご自身で考えて実行してみたSQLを質問の中に記述していればこんなことにならなかったかと思います。横から失礼しました。

White_Rabbit

2015/12/26 00:37 編集

はい、ご指摘の通り丸投げにならないように、仕様だけではなく、データのパターン例や抽出結果例をを具体的に明確に記述させていただき、質問内容も明確になるようにしております。みなさんは、運営様とお話しをしたことがないとかがえておりますがいかがでしょうか？それで、メニューや記述に書かれていることをそのまま受けて追っていると考えております。私は、なんども運営様へメールでやり取りしております。 ※そこは運営様の記述文の問題ですので、どのように、運営様と質問したかは、あえて書きません（行動力があり本来の運営規約や規則を確認したか、それとも、提示されたも記述不足やミスのことをそのままうのみにするか、それは、人それぞれで、確認時間がない、確認をすることすら思いつかない、確認とかせずに言われたままをうのみにする、その他、色々な方がいらっしゃいます）。 ※余談ですがご注意です。ご高齢でしたら、言われたことや書かれていることをうのみにして、振り込み詐欺などに合わないようご注意ください。何が正しいかを確認すべきところへ、勘違いや失敗をする前に、ご確認することをお勧めいたします。横から失礼しました（私の質問の回答内容とは違い、私の質問自体の議論ですので、そこは運営様へ本質を確認していない人の意見は私の求めている回答の範疇外ですので退任行儀な記述をあえて致します）。

swordone

2015/12/26 00:58

丸投げの意味わかって言っていますか？具体的に「ここのコードがわからない」ではなく、「これを実現するためのコードを教えて」という、結局は「仕事の依頼」ですよね？

退会済みユーザー

2015/12/26 01:49

どうやら”丸投げ”という意味が分かってないみたいですね。 https://ja.wikipedia.org/wiki/%E4%B8%B8%E6%8A%95%E3%81%92 今回のご質問は記述内容的に「仕様とデータパターン、想定結果を記述しますのでSQLを提供してください。」と捉えることができます。これを”丸投げ”と言わずになんというのでしょうか？あー、エンジニアは話が通じないってこういうことなのか。

退会済みユーザー

2015/12/30 14:15 編集

なんか盛り上がってたので運営に問い合わせてみました。以下、運営からの返答 ----------------------------------------------------- いつもご利用ありがとうございます。teratailサポートチームです。この度は、お問い合わせありがとうございます。こちらの質問は、テーブルの情報と欲しい情報のみを記載してSQLを求めるという質問で、仰るとおり丸投げの質問に該当します。 https://teratail.com/questions/23399 これらの質問は、問題解決というよりも作業依頼となるため、推奨しておりません。問題や質問は実際に調査や作業に取り組み、具体的なところで生まれると考えるためです。 https://teratail.com/help/avoid-asking teratailではこれらに該当するという理由で質問の削除等をすることは基本的にしませんが、こういった質問を多くするユーザー様に対しては、個別に注意の連絡をさせていただくことがあります。今後もteratailをよろしくお願いいたします。 ----------------------------------------------------- White_Rabbitさん、ここのサイトの使い方や運営方針を読んでから、書きこみしましょう。 White_Rabbitさんには、まだむずかしいのかもしれませんが、社会人になると、それらをまもることを学びますor身に付きます。従って、他の事もそうですが、ルールにそって行動できるよう、且、規則に沿ったことができるようになるとよいでしょう。頑張ってください。

行動規範の内容に同意します

ベストアンサー

非効率だけど、以下のＳＱＬで実現できるはず、やって見てください。

-- 今日以降に予約のある顧客の予約＋顧客データーは、全て抽出。※除く、論理削除レコード。
select
cust.id,
cust.name,
cust.tel,
re.revation_date,
re.revation_starttime
from
contomer cust
left join revation re on re.costomer_id = cust.id and re.del = 0 and re.revation_date > now()
where
cust.del = 0
and re.costomer_id is not null

-- 今日以降に予約がなく、過去に予約がある顧客の場合は、過去の最大の予約日＋予約開始時間のレコードを１つ抽出。※除く、論理削除レコード。
select
cust.id,
cust.name,
cust.tel,
T2.revation_date,
T2.revation_starttime
from
contomer cust
left join (select
re2.revation_date,
re2.revation_starttime
from
revation re2
where re2.id =
( select t.id
from
(select
max(re.revation_date) as revation_date, re.id, re.costomer_id
from
revation re
where
re.costomer_id = cust.id
and re.del = 0
group by re.id, re.costomer_id ,re.revation_date
) t
)
) T2

where
cust.del = 0
and not exists (select 1 from revation re where re.costomer_id = cust.id and re.del = 0 and re.revation_date > now())
and exists (select 1 from revation re where re.costomer_id = cust.id and re.del = 0 and re.revation_date <= now())

-- 過去、未来に予約のない顧客は、顧客情報のみ抽出。
select
cust.id,
cust.name,
cust.tel
from
contomer cust
where
not exists (select 1 from revation re where re.costomer_id = cust.id and re.del = 0)

投稿2015/12/25 06:17

YiLi

総合スコア96

White_Rabbit

2015/12/25 06:24

ご回答ありがとうございます。心より感謝致しております。ありがとうございます。大変お手数をおかけし、申し訳ございません。早速、クエリーを作成して、まずは、DBツール上で、実行してみます。繰り返しになりますが、お手数をお掛け致しました。

kutsulog

2015/12/25 08:15

条件１のSQLはLEFT JOINしたあとIS NOT NULLの制約をかけるくらいならJOINでよくないですか? 条件２のSQLはLEFT JOINする結合条件がないですし、GROUP BYの項目に主キー?が入っているので集約できていないですよ?

YiLi

2015/12/25 08:54

＞＞kutsulog さんご指摘有難うございます。これからちゃんと正しい答えを出せるよう頑張ります。＾＿＾

White_Rabbit

2015/12/26 00:44 編集

＞YiLi様ご回答ありがとございます。大変感謝しております。これはこのサイトの方針と個人孤児的な意見でのおねがいですが、YiLi様のように、このサイトでは難易度が難しいや容易などに制限はございません。幅広い層の開発者に公開されているサイトでございます。従いまして、YiLi様のようにこのようにSQL文などのご提示のご回答を頂ける方を、質問者はお待ちしておりますので、間違いなどをお気にせずにご回答頂ければ幸いです。こまっている質問者がいる状況の中で、YuLi様にお時間があるときにしても、どんどん回答して頂ければ幸いです。それが、このサイトの存在意義であり、有効になるかと考えております。※回答欄に、どこかのフルーツ名のような大手の会社のサポートと同じように、何度、色々質問しても、マニュアルのURLを一文で送ってきて自分で調べろ！！で終わらせる会社と同じ体質の回答者がいらしゃる中で、日本大手のサポートのように適切で明確なサポートと同じように、ご回答を頂けるYiLi様はすばらしい方の方と考えます。そして、それに間違いや、さらに良い方法があるかは、さらに、ここのサイトのコメント機能で、意見をみなさんで出し合って頂いて、良いものになっていければ、なお幸いです。 SQLのご提示とても感謝しております。ありがとうございました。大変お手数をお掛け致しました。

kutsulog

2015/12/28 01:15

>> YiLiさんケチつけてばかりでもなんなので私の方でも条件②だけSQL書いてみました SELECT CST.id , CST.name , REV.revation_date , REV.revation_starttime -- 最後の予約日時を取得 FROM ( SELECT id , MAX(ADDDATE(revation_date, revation_starttime)) dtetim FROM REVATION SRC WHERE del = 0 GROUP BY id ) MAX -- 最後の予約情報を取得 JOIN REVATION REV ON REV.id = MAX.id AND MAX.dtetim = ADDDATE(REV.revation_date, REV.revation_starttime) -- 顧客情報を取得 JOIN CONTOMER CST ON CST.id = REV.id AND CST.del = 0 -- 今日以降の予約がある人は除外 WEHRE MAX.dtetim < curdate() ・日付と時刻でわかれているので足し合わせた結果のMAX値を使って最後の予約情報を取得する・日付と時刻を足したもののMAXが今日以降の人は今日以降に予約がある人このへんがみそですかね ※MAX表を作ってJOINしているところはEXISTSを使ってもいいかもしれません MySQLの環境がないのでテストしていませんがたぶん動くと思います

行動規範の内容に同意します

仕様1を実現するSQL、仕様2を実現するSQL、仕様3を実現するSQL
それぞれを作ってUNION ALLでつなげば希望の結果が得られるはずです。

そんなに難しくないはずなので頑張ってください。

投稿2015/12/25 04:29

kutsulog

総合スコア985