PHP5.2.17 と MySQL5.0.95 での SQL文について質問です。

クイズ問題が入っている Q_master から、問題を出すため一つレコードを抽出したいのですが、以下の条件に沿って抽出するにはどのようなSQL文を書けばいいですか?
そもそも一つのSQL文で以下のような複雑なことができるのですか?

条件の優先順位は上から順番通りです。
1.clear_log の user_id が $userid で、かつ、 clear_log に存在しない Q_id
(特定のユーザーが正解した問題は省く)
2.try_log の user_id が $userid で、かつ、 try_log の try_date が $x 日以上経過している
(特定のユーザーに一度出題した問題は間隔をあけて出題)
3.Q_master の Q_disp を値の低い順
(Q_disp とは出題回数で新しく登録されたクイズを先に出題 ・・・ ただし、 Q_disp が30を超えていたらこの条件は無視)
4.Q_master の Q_oic を値の高い順
(Q_oic とは、問題を5段階で数値化、数値の低い問題は出題回数を減らす)
5.Q_master の Q_rate を値の高い順
(Q_rate とは回答率で優しい問題から出題)

テーブル構造は補足に記入しました。
よろしくお願いしますm(_ _)m

回答の条件
  • 1人5回まで
  • 13歳以上
  • 登録:2013/08/26 11:16:13
  • 終了:2013/08/28 04:42:56
id:appfb

質問者から

appfb2013/08/26 11:36:53

クイズマスタ

テーブル名:Q_master(pkey Q_id)
Q_id(int), Q_disp(int), Q_rate(float), Q_oic(int)
 1, 15, 0.98, 4
 2, 2,  0.56, 3
 3, 0,  0.00, 3
 4, 7,  0.68, 4
 5, 7,  0.54, 3
 6, 2,  0.72, 3
 7, 5,  0.76, 2
 .....

クイズトライlog(このテーブルは、回答されるたびに間違いでも正解でも記録される)

テーブル名:try_log(pkey try_id)
try_id(int), Q_id(int), user_id(int), try_date(datetime)
 .....

クイズクリアlog(このテーブルは、クイズに正解した時のみに記録される)

テーブル名:clear_log(pkey Q_id, user_id)
Q_id(int), user_id(int), clear_date(datetime)
 .....

ベストアンサー

id:Sampo No.1

Sampo回答回数556ベストアンサー獲得回数1042013/08/26 23:06:24

ポイント100pt

SQLならもちろん1クエリでいけます。

SELECT Q_id
FROM Q_master AS main
WHERE
	-- 条件1
	Q_id NOT IN (SELECT c.Q_id FROM clear_log AS c WHERE c.user_id={$userid})
ORDER BY
	-- 条件2
	(SELECT COUNT(*) FROM try_log AS t 
	 WHERE t.user_id={$userid}
	 	AND t.Q_id=main.Q_id
	 	AND try_date > ADDDATE(NOW(), {$x})) ASC, -- 最近答えていれば1, そうでなければ0
	-- 条件3
	(CASE WHEN Q_disp>30 THEN 30 ELSE Q_disp END) ASC,
	-- 条件4
	Q_oic,
	-- 条件5
	Q_rate DESC,
	-- 同条件ならランダム
	RAND()
LIMIT 0, 1;
id:Sampo

ADDDATE(NOW(), {$x})
じゃなくて
ADDDATE(NOW(), -{$x})
じゃないと「最近x日以内」にならないか。

2013/08/26 23:07:48
id:appfb

ご回答ありがとうございます。
JOIN ~ ON を使うことばかり考え、条件2を入れるのに四苦八苦しておりました。
現在様々なテストデータを入れてみますがパーフェクトです。
初めて見る個所も多く大変勉強になります。
ありがとうございました。

2013/08/27 00:58:51

その他の回答(0件)

id:appfb

質問者から

appfb2013/08/26 20:41:15

質問文を編集しました。詳細はこちら

id:Sampo No.1

Sampo回答回数556ベストアンサー獲得回数1042013/08/26 23:06:24ここでベストアンサー

ポイント100pt

SQLならもちろん1クエリでいけます。

SELECT Q_id
FROM Q_master AS main
WHERE
	-- 条件1
	Q_id NOT IN (SELECT c.Q_id FROM clear_log AS c WHERE c.user_id={$userid})
ORDER BY
	-- 条件2
	(SELECT COUNT(*) FROM try_log AS t 
	 WHERE t.user_id={$userid}
	 	AND t.Q_id=main.Q_id
	 	AND try_date > ADDDATE(NOW(), {$x})) ASC, -- 最近答えていれば1, そうでなければ0
	-- 条件3
	(CASE WHEN Q_disp>30 THEN 30 ELSE Q_disp END) ASC,
	-- 条件4
	Q_oic,
	-- 条件5
	Q_rate DESC,
	-- 同条件ならランダム
	RAND()
LIMIT 0, 1;
id:Sampo

ADDDATE(NOW(), {$x})
じゃなくて
ADDDATE(NOW(), -{$x})
じゃないと「最近x日以内」にならないか。

2013/08/26 23:07:48
id:appfb

ご回答ありがとうございます。
JOIN ~ ON を使うことばかり考え、条件2を入れるのに四苦八苦しておりました。
現在様々なテストデータを入れてみますがパーフェクトです。
初めて見る個所も多く大変勉強になります。
ありがとうございました。

2013/08/27 00:58:51

コメントはまだありません

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません