質問編集履歴

2022/01/16 09:26

投稿

ingeniero

スコア10

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -1,5 +1,3 @@
 MySQLで、片方のテーブルにしかないデータをランダムで10件抽出したいと思います。xamppでphpMyAdminを使用しています。
-解決しました。
+自己解決しました。

MySQL SQL データベース

解決のため、不要な文章削除

2022/01/11 12:38

投稿

ingeniero

スコア10

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -2,222 +2,4 @@
-wordsテーブルには単語の情報、quizテーブルにはそれぞれの単語のクイズの正解不正解の情報があります。
-wordsテーブルの主キーで結合可能、ただしいろいろなユーザーの単語のクイズの成績が存在する。下記詳述。
-クイズテーブルには、それぞれのユーザーが以前クイズで回答した単語の成績が追加されています。
-wordsテーブルの主キーとusersテーブル（ユーザー情報保管テーブル）の主キーを外部キーにして作成しています。
-単語テーブル
-```SQL
-CREATE TABLE `words` (
-  `id` int(6) NOT NULL,
-  `word` char(50) CHARACTER SET utf8 NOT NULL COMMENT '単語',
-  `meaning` char(50) CHARACTER SET utf8 NOT NULL COMMENT '意味',
-  `example` varchar(200) CHARACTER SET utf8 DEFAULT NULL COMMENT '例文',
-  `add_datetime` datetime DEFAULT NULL,
-  `delete_datetime` datetime DEFAULT NULL,
-  `user_id` int(11) NOT NULL,
-  `list_id` int(11) NOT NULL COMMENT '単語帳外部キー'
-) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-```
-```SQL
-INSERT INTO `words` (`id`, `word`, `meaning`, `example`, `add_datetime`, `delete_datetime`, `user_id`, `list_id`) VALUES
-(11, 'Poop', 'うんこ\r\n', 'I want to eat poop.\r\n私はうんこ食べたいです。', NULL, '2021-08-31 22:12:33', 1, 1),
-(41, 'parentheses', '括弧()', NULL, '2021-08-18 22:12:46', NULL, 4, 2),
-(46, 'dumb', '〈主に米話〉頭の悪い、ばかな、あほな、\r\n間抜けな、常識のない', 'We&#039;re making a dumb Youtube video.', '2021-08-16 23:29:23', NULL, 4, 2),
-(48, 'ignorant', '無知な', 'My grandmother is always igonorant.', NULL, NULL, 4, 2),
-(57, 'I couldn&#039;t help but', 'どうしても目が行ってしまう', 'I couldn&#039;t help but notice your necklace.\r\nそのネックレスに\r\nどうしても目が行ってしまって。', '2022-01-03 18:02:21', NULL, 1, 1),
-(59, 'I&#039;ll walk you through', '1つずつ全体を段階的に説明します', 'Don&#039;t worry. I&#039;ll walk you through again later.\r\n（心配いらないわ。後でもう一度全体を1つずつ段階的に説明するわね。）　', NULL, NULL, 1, 1),
-(64, 'judgmental', 'すぐに決めつけて批判するような\r\nよく知らないで自分の価値基準だけですぐに批判するような', 'He&#039;s judgmental.', '2022-01-03 21:15:13', NULL, 1, 1),
-(74, 'acquaintance', '知人', 'Mr. Collins is an acquaintance rather than a friend.', '2021-08-22 00:03:17', NULL, 1, 1),
-(75, 'Put you down', '悪い方向に無理やり持っていて、批判する的な？\r\nけなす', 'She always puts me down.', '2021-08-22 00:04:00', NULL, 1, 1),
-(76, 'Flirt', 'イチャイチャする', 'If you like him, try flirting with him. He might get the message.\r\n（彼の事が好きだったら、からかったり褒めたりしてみて。彼はあなたの気持ちが分かるかもしれない。）', '2021-08-22 00:04:30', NULL, 1, 1),
-(77, 'crunch time', '締め切り前の踏ん張りどころ', 'I’m in a time crunch with my projects.（＝複数のプロジェクトで時間に追われている）', '2021-08-22 00:04:59', NULL, 1, 1),
-(78, 'Pinkeye', '結膜炎', 'My brother farted on my face yesterday\r\nso I think I have a pinkeye now', '2021-08-22 13:08:31', NULL, 1, 1),
-(84, 'sample', 'サンプル', 'this is sample', '2021-09-02 01:40:46', '2021-09-02 09:38:02', 1, 3),
-(85, 'ballsy', '〈米俗〉威勢のいい、心臓の強い、度胸のある、勇敢な、根性のある　\r\n\r\n', 'Let&#039;s see how ballsy they are.\r\n彼らがどれだけ度胸があるか見てみよう。\r\nMy dad says you&#039;re ballsy.\r\n父が、あなたは根性があるって言っていたわ。', '2021-12-11 22:55:33', NULL, 1, 1),
-(86, 'assertive', '〔意見・要求・感情などの表現が〕自信に満ちた態度の、積極的な、はっきり［積極的に］自己主張する', '&quot;I am the type who can&#039;t say &#039;No.&#039;&quot;\r\n&quot;You should be more assertive.&quot;\r\n\r\n「私はノーと言えない性格なんです」\r\n「あなたはもう少し積極的に自己主張するべきです」', '2021-09-02 12:10:38', NULL, 1, 1),
-(87, 'give off', '〔光・気体・熱・雰囲気などを〕出す、発する', 'She is giving off an atmosphere of not wanting to be here.\r\n彼女はここにいたくないというそぶりを見せている。', '2021-09-02 12:10:16', NULL, 1, 1),
-(88, 'Sum up', '要約する', 'Can you sum up what you said ?', '2021-09-02 12:07:19', NULL, 1, 1),
-(89, 'sample', 'サンプル', NULL, '2021-12-30 21:57:36', '2021-12-30 21:58:47', 4, 2);
-```
-クイズテーブル
-```SQL
-CREATE TABLE `quiz` (
-  `id` int(11) NOT NULL COMMENT '主キー',
-  `correctCount` int(11) NOT NULL COMMENT '単語正解した回数',
-  `falseCount` int(11) NOT NULL COMMENT '単語間違えた数',
-  `lastCorrectedTime` datetime DEFAULT NULL COMMENT '最後に正解した時間',
-  `lastFalseTime` datetime DEFAULT NULL COMMENT '最後に間違えた時間',
-  `word_id` int(11) NOT NULL COMMENT '単語外部キー',
-  `user_id` int(11) NOT NULL COMMENT 'ユーザー外部キー'
-) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-```
-```SQL
-INSERT INTO `quiz` (`id`, `correctCount`, `falseCount`, `lastCorrectedTime`, `lastFalseTime`, `word_id`, `user_id`) VALUES
-(61, 1, 0, '2022-01-08 13:27:32', NULL, 75, 1),
-(62, 1, 0, '2022-01-08 13:27:32', NULL, 78, 1),
-(63, 1, 0, '2022-01-08 13:27:32', NULL, 76, 1),
-(64, 0, 1, NULL, '2022-01-08 13:27:32', 64, 1),
-(72, 2, 0, '2022-01-09 11:34:30', NULL, 77, 4),
-(73, 3, 0, '2022-01-09 11:34:30', NULL, 85, 4),
-(74, 2, 1, '2022-01-08 17:14:56', '2022-01-08 13:30:00', 75, 4),
-(78, 2, 0, '2022-01-09 11:34:30', NULL, 64, 4),
-(79, 1, 0, '2022-01-08 17:14:56', NULL, 78, 4),
-(80, 1, 0, '2022-01-09 11:34:30', NULL, 76, 4),
-(81, 1, 0, '2022-01-09 11:34:30', NULL, 87, 4),
-(82, 1, 0, '2022-01-09 11:34:30', NULL, 74, 4),
-```
-逆に言うと、今までまだ一度もユーザーがクイズで回答していない単語について、その単語の単語IDとそのユーザーのユーザーIDを外部キーとして併せ持つレコードはクイズテーブルには存在しません。それを利用して今回の要望を実現したいと考えております。
-（単語自体は、他のユーザーが回答している場合、クイズテーブルに追加されていますが、ユーザーIDの外部キがそのユーザーとは異なる。）
-つまり、クイズテーブルには存在せず、単語テーブルにのみ存在する各ユーザーのクイズ未出題の単語を抽出したいです。
-一つの単語には複数のユーザーのクイズの成績を保管しているため、単純にNOT EXISTSの条件をwhere quiz.word_id = words.idとするとすべてのユーザーがクイズで回答したことない単語のみが抽出されますが、それは望んでいません。それぞれのユーザー別に抽出したいです。
-いろいろ調べて、NOT EXISTSを使用して、WHEREの条件でユーザーIDを指定しているのですが上手くいきません。
-エラーとかはないのですが、何も抽出されません。
-NOT EXISTSを書く前に、それぞれのSELECT文を分解して実行しましたが、両方きちんとデータが抽出されていました。
-そして、クイズテーブルに存在せず単語テーブルにのみ存在するデータがあることも確認済みなので、何もデータが抽出されないのは正しい挙動ではありません。
-どのようにすれば、上記の要件を満たすSQL文になるでしょうか。
-ご教授お願い致します。
-現在書いているSQL文
-```SQL
-            SELECT w.id, w.word, w.meaning
-            FROM words w
-            WHERE w.delete_datetime IS NULL AND w.list_id = 1
-            AND NOT EXISTS (
-                SELECT * FROM quiz q
-                LEFT JOIN words w ON w.id = q.word_id
-                WHERE w.list_id = 1 AND q.user_id = 1
-                AND w.delete_datetime IS NULL
-            )
-            ORDER BY rand()
-            LIMIT 10
-```
-希望するSELECT結果（ユーザーによってこのNOT IN句に指定しなければいけない単語IDが違うため、動的に取得できる上記を書いている）
-```SQL
-SELECT w.id, w.word, w.meaning
-            FROM words w
-            WHERE w.delete_datetime IS NULL AND w.list_id = 1 AND
-            w.id NOT IN("75", "76", "78")
-            ORDER BY rand()
-            LIMIT 10
-```
+解決しました。

SQLで使っているテーブルのCREATE TABLE文や何件かINSERT文や希望するSELECT結果の追記

2022/01/11 12:38

投稿

ingeniero

スコア10

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -14,6 +14,134 @@
+単語テーブル
+```SQL
+CREATE TABLE `words` (
+  `id` int(6) NOT NULL,
+  `word` char(50) CHARACTER SET utf8 NOT NULL COMMENT '単語',
+  `meaning` char(50) CHARACTER SET utf8 NOT NULL COMMENT '意味',
+  `example` varchar(200) CHARACTER SET utf8 DEFAULT NULL COMMENT '例文',
+  `add_datetime` datetime DEFAULT NULL,
+  `delete_datetime` datetime DEFAULT NULL,
+  `user_id` int(11) NOT NULL,
+  `list_id` int(11) NOT NULL COMMENT '単語帳外部キー'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+```
+```SQL
+INSERT INTO `words` (`id`, `word`, `meaning`, `example`, `add_datetime`, `delete_datetime`, `user_id`, `list_id`) VALUES
+(11, 'Poop', 'うんこ\r\n', 'I want to eat poop.\r\n私はうんこ食べたいです。', NULL, '2021-08-31 22:12:33', 1, 1),
+(41, 'parentheses', '括弧()', NULL, '2021-08-18 22:12:46', NULL, 4, 2),
+(46, 'dumb', '〈主に米話〉頭の悪い、ばかな、あほな、\r\n間抜けな、常識のない', 'We&#039;re making a dumb Youtube video.', '2021-08-16 23:29:23', NULL, 4, 2),
+(48, 'ignorant', '無知な', 'My grandmother is always igonorant.', NULL, NULL, 4, 2),
+(57, 'I couldn&#039;t help but', 'どうしても目が行ってしまう', 'I couldn&#039;t help but notice your necklace.\r\nそのネックレスに\r\nどうしても目が行ってしまって。', '2022-01-03 18:02:21', NULL, 1, 1),
+(59, 'I&#039;ll walk you through', '1つずつ全体を段階的に説明します', 'Don&#039;t worry. I&#039;ll walk you through again later.\r\n（心配いらないわ。後でもう一度全体を1つずつ段階的に説明するわね。）　', NULL, NULL, 1, 1),
+(64, 'judgmental', 'すぐに決めつけて批判するような\r\nよく知らないで自分の価値基準だけですぐに批判するような', 'He&#039;s judgmental.', '2022-01-03 21:15:13', NULL, 1, 1),
+(74, 'acquaintance', '知人', 'Mr. Collins is an acquaintance rather than a friend.', '2021-08-22 00:03:17', NULL, 1, 1),
+(75, 'Put you down', '悪い方向に無理やり持っていて、批判する的な？\r\nけなす', 'She always puts me down.', '2021-08-22 00:04:00', NULL, 1, 1),
+(76, 'Flirt', 'イチャイチャする', 'If you like him, try flirting with him. He might get the message.\r\n（彼の事が好きだったら、からかったり褒めたりしてみて。彼はあなたの気持ちが分かるかもしれない。）', '2021-08-22 00:04:30', NULL, 1, 1),
+(77, 'crunch time', '締め切り前の踏ん張りどころ', 'I’m in a time crunch with my projects.（＝複数のプロジェクトで時間に追われている）', '2021-08-22 00:04:59', NULL, 1, 1),
+(78, 'Pinkeye', '結膜炎', 'My brother farted on my face yesterday\r\nso I think I have a pinkeye now', '2021-08-22 13:08:31', NULL, 1, 1),
+(84, 'sample', 'サンプル', 'this is sample', '2021-09-02 01:40:46', '2021-09-02 09:38:02', 1, 3),
+(85, 'ballsy', '〈米俗〉威勢のいい、心臓の強い、度胸のある、勇敢な、根性のある　\r\n\r\n', 'Let&#039;s see how ballsy they are.\r\n彼らがどれだけ度胸があるか見てみよう。\r\nMy dad says you&#039;re ballsy.\r\n父が、あなたは根性があるって言っていたわ。', '2021-12-11 22:55:33', NULL, 1, 1),
+(86, 'assertive', '〔意見・要求・感情などの表現が〕自信に満ちた態度の、積極的な、はっきり［積極的に］自己主張する', '&quot;I am the type who can&#039;t say &#039;No.&#039;&quot;\r\n&quot;You should be more assertive.&quot;\r\n\r\n「私はノーと言えない性格なんです」\r\n「あなたはもう少し積極的に自己主張するべきです」', '2021-09-02 12:10:38', NULL, 1, 1),
+(87, 'give off', '〔光・気体・熱・雰囲気などを〕出す、発する', 'She is giving off an atmosphere of not wanting to be here.\r\n彼女はここにいたくないというそぶりを見せている。', '2021-09-02 12:10:16', NULL, 1, 1),
+(88, 'Sum up', '要約する', 'Can you sum up what you said ?', '2021-09-02 12:07:19', NULL, 1, 1),
+(89, 'sample', 'サンプル', NULL, '2021-12-30 21:57:36', '2021-12-30 21:58:47', 4, 2);
+```
+クイズテーブル
+```SQL
+CREATE TABLE `quiz` (
+  `id` int(11) NOT NULL COMMENT '主キー',
+  `correctCount` int(11) NOT NULL COMMENT '単語正解した回数',
+  `falseCount` int(11) NOT NULL COMMENT '単語間違えた数',
+  `lastCorrectedTime` datetime DEFAULT NULL COMMENT '最後に正解した時間',
+  `lastFalseTime` datetime DEFAULT NULL COMMENT '最後に間違えた時間',
+  `word_id` int(11) NOT NULL COMMENT '単語外部キー',
+  `user_id` int(11) NOT NULL COMMENT 'ユーザー外部キー'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+```
+```SQL
+INSERT INTO `quiz` (`id`, `correctCount`, `falseCount`, `lastCorrectedTime`, `lastFalseTime`, `word_id`, `user_id`) VALUES
+(61, 1, 0, '2022-01-08 13:27:32', NULL, 75, 1),
+(62, 1, 0, '2022-01-08 13:27:32', NULL, 78, 1),
+(63, 1, 0, '2022-01-08 13:27:32', NULL, 76, 1),
+(64, 0, 1, NULL, '2022-01-08 13:27:32', 64, 1),
+(72, 2, 0, '2022-01-09 11:34:30', NULL, 77, 4),
+(73, 3, 0, '2022-01-09 11:34:30', NULL, 85, 4),
+(74, 2, 1, '2022-01-08 17:14:56', '2022-01-08 13:30:00', 75, 4),
+(78, 2, 0, '2022-01-09 11:34:30', NULL, 64, 4),
+(79, 1, 0, '2022-01-08 17:14:56', NULL, 78, 4),
+(80, 1, 0, '2022-01-09 11:34:30', NULL, 76, 4),
+(81, 1, 0, '2022-01-09 11:34:30', NULL, 87, 4),
+(82, 1, 0, '2022-01-09 11:34:30', NULL, 74, 4),
+```
 逆に言うと、今までまだ一度もユーザーがクイズで回答していない単語について、その単語の単語IDとそのユーザーのユーザーIDを外部キーとして併せ持つレコードはクイズテーブルには存在しません。それを利用して今回の要望を実現したいと考えております。
 （単語自体は、他のユーザーが回答している場合、クイズテーブルに追加されていますが、ユーザーIDの外部キがそのユーザーとは異なる。）
@@ -48,19 +176,25 @@
+現在書いているSQL文
-```SQL
+```SQL
-            SELECT *
+            SELECT w.id, w.word, w.meaning
             FROM words w
+            WHERE w.delete_datetime IS NULL AND w.list_id = 1
-            WHERE NOT EXISTS (
+            AND NOT EXISTS (
                 SELECT * FROM quiz q
                 LEFT JOIN words w ON w.id = q.word_id
-                WHERE q.user_id = 4
+                WHERE w.list_id = 1 AND q.user_id = 1
+                AND w.delete_datetime IS NULL
             )
@@ -69,3 +203,21 @@
             LIMIT 10
 ```
+希望するSELECT結果（ユーザーによってこのNOT IN句に指定しなければいけない単語IDが違うため、動的に取得できる上記を書いている）
+```SQL
+SELECT w.id, w.word, w.meaning
+            FROM words w
+            WHERE w.delete_datetime IS NULL AND w.list_id = 1 AND
+            w.id NOT IN("75", "76", "78")
+            ORDER BY rand()
+            LIMIT 10
+```