質問編集履歴
3
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -1,3 +1,3 @@
|
|
|
1
|
-
MySQLで、片方のテーブルにしかないデータをランダムで10件抽出したいと思います。xamppでphpMyAdminを使用しています。
|
|
1
|
+
MySQLで、片方のテーブルにしかないデータをランダムで10件抽出したいと思います。xamppでphpMyAdminを使用しています。
|
|
2
|
-
|
|
2
|
+
|
|
3
|
-
解決しました。
|
|
3
|
+
自己解決しました。
|
2
解決のため、不要な文章削除
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -1,112 +1,3 @@
|
|
|
1
1
|
MySQLで、片方のテーブルにしかないデータをランダムで10件抽出したいと思います。xamppでphpMyAdminを使用しています。
|
|
2
2
|
|
|
3
|
-
|
|
4
|
-
wordsテーブルの主キーで結合可能、ただしいろいろなユーザーの単語のクイズの成績が存在する。下記詳述。
|
|
5
|
-
|
|
6
|
-
クイズテーブルには、それぞれのユーザーが以前クイズで回答した単語の成績が追加されています。
|
|
7
|
-
wordsテーブルの主キーとusersテーブル（ユーザー情報保管テーブル）の主キーを外部キーにして作成しています。
|
|
8
|
-
|
|
9
|
-
単語テーブル
|
|
10
|
-
```SQL
|
|
11
|
-
CREATE TABLE `words` (
|
|
12
|
-
`id` int(6) NOT NULL,
|
|
13
|
-
`word` char(50) CHARACTER SET utf8 NOT NULL COMMENT '単語',
|
|
14
|
-
`meaning` char(50) CHARACTER SET utf8 NOT NULL COMMENT '意味',
|
|
15
|
-
`example` varchar(200) CHARACTER SET utf8 DEFAULT NULL COMMENT '例文',
|
|
16
|
-
`add_datetime` datetime DEFAULT NULL,
|
|
17
|
-
`delete_datetime` datetime DEFAULT NULL,
|
|
18
|
-
`user_id` int(11) NOT NULL,
|
|
19
|
-
`list_id` int(11) NOT NULL COMMENT '単語帳外部キー'
|
|
20
|
-
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
21
|
-
```
|
|
22
|
-
```SQL
|
|
23
|
-
INSERT INTO `words` (`id`, `word`, `meaning`, `example`, `add_datetime`, `delete_datetime`, `user_id`, `list_id`) VALUES
|
|
24
|
-
(11, 'Poop', 'うんこ\r\n', 'I want to eat poop.\r\n私はうんこ食べたいです。', NULL, '2021-08-31 22:12:33', 1, 1),
|
|
25
|
-
(41, 'parentheses', '括弧()', NULL, '2021-08-18 22:12:46', NULL, 4, 2),
|
|
26
|
-
(46, 'dumb', '〈主に米話〉頭の悪い、ばかな、あほな、\r\n間抜けな、常識のない', 'We're making a dumb Youtube video.', '2021-08-16 23:29:23', NULL, 4, 2),
|
|
27
|
-
(48, 'ignorant', '無知な', 'My grandmother is always igonorant.', NULL, NULL, 4, 2),
|
|
28
|
-
(57, 'I couldn't help but', 'どうしても目が行ってしまう', 'I couldn't help but notice your necklace.\r\nそのネックレスに\r\nどうしても目が行ってしまって。', '2022-01-03 18:02:21', NULL, 1, 1),
|
|
29
|
-
(59, 'I'll walk you through', '1つずつ全体を段階的に説明します', 'Don't worry. I'll walk you through again later.\r\n（心配いらないわ。後でもう一度全体を1つずつ段階的に説明するわね。） ', NULL, NULL, 1, 1),
|
|
30
|
-
(64, 'judgmental', 'すぐに決めつけて批判するような\r\nよく知らないで自分の価値基準だけですぐに批判するような', 'He's judgmental.', '2022-01-03 21:15:13', NULL, 1, 1),
|
|
31
|
-
(74, 'acquaintance', '知人', 'Mr. Collins is an acquaintance rather than a friend.', '2021-08-22 00:03:17', NULL, 1, 1),
|
|
32
|
-
(75, 'Put you down', '悪い方向に無理やり持っていて、批判する的な？\r\nけなす', 'She always puts me down.', '2021-08-22 00:04:00', NULL, 1, 1),
|
|
33
|
-
(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),
|
|
34
|
-
(77, 'crunch time', '締め切り前の踏ん張りどころ', 'I’m in a time crunch with my projects.（＝複数のプロジェクトで時間に追われている）', '2021-08-22 00:04:59', NULL, 1, 1),
|
|
35
|
-
(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),
|
|
36
|
-
(84, 'sample', 'サンプル', 'this is sample', '2021-09-02 01:40:46', '2021-09-02 09:38:02', 1, 3),
|
|
37
|
-
(85, 'ballsy', '〈米俗〉威勢のいい、心臓の強い、度胸のある、勇敢な、根性のある \r\n\r\n', 'Let's see how ballsy they are.\r\n彼らがどれだけ度胸があるか見てみよう。\r\nMy dad says you're ballsy.\r\n父が、あなたは根性があるって言っていたわ。', '2021-12-11 22:55:33', NULL, 1, 1),
|
|
38
|
-
(86, 'assertive', '〔意見・要求・感情などの表現が〕自信に満ちた態度の、積極的な、はっきり［積極的に］自己主張する', '"I am the type who can't say 'No.'"\r\n"You should be more assertive."\r\n\r\n「私はノーと言えない性格なんです」\r\n「あなたはもう少し積極的に自己主張するべきです」', '2021-09-02 12:10:38', NULL, 1, 1),
|
|
39
|
-
(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),
|
|
40
|
-
(88, 'Sum up', '要約する', 'Can you sum up what you said ?', '2021-09-02 12:07:19', NULL, 1, 1),
|
|
41
|
-
(89, 'sample', 'サンプル', NULL, '2021-12-30 21:57:36', '2021-12-30 21:58:47', 4, 2);
|
|
42
|
-
|
|
43
|
-
```
|
|
44
|
-
クイズテーブル
|
|
45
|
-
```SQL
|
|
46
|
-
CREATE TABLE `quiz` (
|
|
47
|
-
`id` int(11) NOT NULL COMMENT '主キー',
|
|
48
|
-
`correctCount` int(11) NOT NULL COMMENT '単語正解した回数',
|
|
49
|
-
`falseCount` int(11) NOT NULL COMMENT '単語間違えた数',
|
|
50
|
-
`lastCorrectedTime` datetime DEFAULT NULL COMMENT '最後に正解した時間',
|
|
51
|
-
`lastFalseTime` datetime DEFAULT NULL COMMENT '最後に間違えた時間',
|
|
52
|
-
`word_id` int(11) NOT NULL COMMENT '単語外部キー',
|
|
53
|
-
`user_id` int(11) NOT NULL COMMENT 'ユーザー外部キー'
|
|
54
|
-
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
55
|
-
```
|
|
56
|
-
|
|
57
|
-
```SQL
|
|
58
|
-
INSERT INTO `quiz` (`id`, `correctCount`, `falseCount`, `lastCorrectedTime`, `lastFalseTime`, `word_id`, `user_id`) VALUES
|
|
59
|
-
(61, 1, 0, '2022-01-08 13:27:32', NULL, 75, 1),
|
|
60
|
-
(62, 1, 0, '2022-01-08 13:27:32', NULL, 78, 1),
|
|
61
|
-
(63, 1, 0, '2022-01-08 13:27:32', NULL, 76, 1),
|
|
62
|
-
(64, 0, 1, NULL, '2022-01-08 13:27:32', 64, 1),
|
|
63
|
-
(72, 2, 0, '2022-01-09 11:34:30', NULL, 77, 4),
|
|
64
|
-
(73, 3, 0, '2022-01-09 11:34:30', NULL, 85, 4),
|
|
65
|
-
(74, 2, 1, '2022-01-08 17:14:56', '2022-01-08 13:30:00', 75, 4),
|
|
66
|
-
(78, 2, 0, '2022-01-09 11:34:30', NULL, 64, 4),
|
|
67
|
-
(79, 1, 0, '2022-01-08 17:14:56', NULL, 78, 4),
|
|
68
|
-
(80, 1, 0, '2022-01-09 11:34:30', NULL, 76, 4),
|
|
69
|
-
(81, 1, 0, '2022-01-09 11:34:30', NULL, 87, 4),
|
|
70
|
-
(82, 1, 0, '2022-01-09 11:34:30', NULL, 74, 4),
|
|
71
|
-
```
|
|
72
|
-
|
|
73
|
-
逆に言うと、今までまだ一度もユーザーがクイズで回答していない単語について、その単語の単語IDとそのユーザーのユーザーIDを外部キーとして併せ持つレコードはクイズテーブルには存在しません。それを利用して今回の要望を実現したいと考えております。
|
|
74
|
-
（単語自体は、他のユーザーが回答している場合、クイズテーブルに追加されていますが、ユーザーIDの外部キがそのユーザーとは異なる。）
|
|
75
|
-
|
|
76
|
-
つまり、クイズテーブルには存在せず、単語テーブルにのみ存在する各ユーザーのクイズ未出題の単語を抽出したいです。
|
|
77
|
-
|
|
78
|
-
一つの単語には複数のユーザーのクイズの成績を保管しているため、単純にNOT EXISTSの条件をwhere quiz.word_id = words.idとするとすべてのユーザーがクイズで回答したことない単語のみが抽出されますが、それは望んでいません。それぞれのユーザー別に抽出したいです。
|
|
79
|
-
|
|
80
|
-
いろいろ調べて、NOT EXISTSを使用して、WHEREの条件でユーザーIDを指定しているのですが上手くいきません。
|
|
81
|
-
エラーとかはないのですが、何も抽出されません。
|
|
82
|
-
|
|
83
|
-
NOT EXISTSを書く前に、それぞれのSELECT文を分解して実行しましたが、両方きちんとデータが抽出されていました。
|
|
84
|
-
そして、クイズテーブルに存在せず単語テーブルにのみ存在するデータがあることも確認済みなので、何もデータが抽出されないのは正しい挙動ではありません。
|
|
85
|
-
|
|
86
|
-
どのようにすれば、上記の要件を満たすSQL文になるでしょうか。
|
|
87
|
-
|
|
88
|
-
ご教授お願い致します。
|
|
89
|
-
|
|
90
|
-
現在書いているSQL文
|
|
91
|
-
```SQL
|
|
92
|
-
SELECT w.id, w.word, w.meaning
|
|
93
|
-
FROM words w
|
|
94
|
-
WHERE w.delete_datetime IS NULL AND w.list_id = 1
|
|
95
|
-
AND NOT EXISTS (
|
|
96
|
-
SELECT * FROM quiz q
|
|
97
|
-
LEFT JOIN words w ON w.id = q.word_id
|
|
98
|
-
WHERE w.list_id = 1 AND q.user_id = 1
|
|
99
|
-
AND w.delete_datetime IS NULL
|
|
100
|
-
)
|
|
101
|
-
ORDER BY rand()
|
|
102
|
-
LIMIT 10
|
|
103
|
-
```
|
|
104
|
-
希望するSELECT結果（ユーザーによってこのNOT IN句に指定しなければいけない単語IDが違うため、動的に取得できる上記を書いている）
|
|
105
|
-
```SQL
|
|
106
|
-
SELECT w.id, w.word, w.meaning
|
|
107
|
-
FROM words w
|
|
108
|
-
WHERE w.delete_datetime IS NULL AND w.list_id = 1 AND
|
|
109
|
-
w.id NOT IN("75", "76", "78")
|
|
110
|
-
ORDER BY rand()
|
|
111
|
-
LIMIT 10
|
|
112
|
-
```
|
|
3
|
+
解決しました。
|
1
SQLで使っているテーブルのCREATE TABLE文や何件かINSERT文や希望するSELECT結果の追記
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -6,6 +6,70 @@
|
|
|
6
6
|
クイズテーブルには、それぞれのユーザーが以前クイズで回答した単語の成績が追加されています。
|
|
7
7
|
wordsテーブルの主キーとusersテーブル（ユーザー情報保管テーブル）の主キーを外部キーにして作成しています。
|
|
8
8
|
|
|
9
|
+
単語テーブル
|
|
10
|
+
```SQL
|
|
11
|
+
CREATE TABLE `words` (
|
|
12
|
+
`id` int(6) NOT NULL,
|
|
13
|
+
`word` char(50) CHARACTER SET utf8 NOT NULL COMMENT '単語',
|
|
14
|
+
`meaning` char(50) CHARACTER SET utf8 NOT NULL COMMENT '意味',
|
|
15
|
+
`example` varchar(200) CHARACTER SET utf8 DEFAULT NULL COMMENT '例文',
|
|
16
|
+
`add_datetime` datetime DEFAULT NULL,
|
|
17
|
+
`delete_datetime` datetime DEFAULT NULL,
|
|
18
|
+
`user_id` int(11) NOT NULL,
|
|
19
|
+
`list_id` int(11) NOT NULL COMMENT '単語帳外部キー'
|
|
20
|
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
21
|
+
```
|
|
22
|
+
```SQL
|
|
23
|
+
INSERT INTO `words` (`id`, `word`, `meaning`, `example`, `add_datetime`, `delete_datetime`, `user_id`, `list_id`) VALUES
|
|
24
|
+
(11, 'Poop', 'うんこ\r\n', 'I want to eat poop.\r\n私はうんこ食べたいです。', NULL, '2021-08-31 22:12:33', 1, 1),
|
|
25
|
+
(41, 'parentheses', '括弧()', NULL, '2021-08-18 22:12:46', NULL, 4, 2),
|
|
26
|
+
(46, 'dumb', '〈主に米話〉頭の悪い、ばかな、あほな、\r\n間抜けな、常識のない', 'We're making a dumb Youtube video.', '2021-08-16 23:29:23', NULL, 4, 2),
|
|
27
|
+
(48, 'ignorant', '無知な', 'My grandmother is always igonorant.', NULL, NULL, 4, 2),
|
|
28
|
+
(57, 'I couldn't help but', 'どうしても目が行ってしまう', 'I couldn't help but notice your necklace.\r\nそのネックレスに\r\nどうしても目が行ってしまって。', '2022-01-03 18:02:21', NULL, 1, 1),
|
|
29
|
+
(59, 'I'll walk you through', '1つずつ全体を段階的に説明します', 'Don't worry. I'll walk you through again later.\r\n（心配いらないわ。後でもう一度全体を1つずつ段階的に説明するわね。） ', NULL, NULL, 1, 1),
|
|
30
|
+
(64, 'judgmental', 'すぐに決めつけて批判するような\r\nよく知らないで自分の価値基準だけですぐに批判するような', 'He's judgmental.', '2022-01-03 21:15:13', NULL, 1, 1),
|
|
31
|
+
(74, 'acquaintance', '知人', 'Mr. Collins is an acquaintance rather than a friend.', '2021-08-22 00:03:17', NULL, 1, 1),
|
|
32
|
+
(75, 'Put you down', '悪い方向に無理やり持っていて、批判する的な？\r\nけなす', 'She always puts me down.', '2021-08-22 00:04:00', NULL, 1, 1),
|
|
33
|
+
(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),
|
|
34
|
+
(77, 'crunch time', '締め切り前の踏ん張りどころ', 'I’m in a time crunch with my projects.（＝複数のプロジェクトで時間に追われている）', '2021-08-22 00:04:59', NULL, 1, 1),
|
|
35
|
+
(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),
|
|
36
|
+
(84, 'sample', 'サンプル', 'this is sample', '2021-09-02 01:40:46', '2021-09-02 09:38:02', 1, 3),
|
|
37
|
+
(85, 'ballsy', '〈米俗〉威勢のいい、心臓の強い、度胸のある、勇敢な、根性のある \r\n\r\n', 'Let's see how ballsy they are.\r\n彼らがどれだけ度胸があるか見てみよう。\r\nMy dad says you're ballsy.\r\n父が、あなたは根性があるって言っていたわ。', '2021-12-11 22:55:33', NULL, 1, 1),
|
|
38
|
+
(86, 'assertive', '〔意見・要求・感情などの表現が〕自信に満ちた態度の、積極的な、はっきり［積極的に］自己主張する', '"I am the type who can't say 'No.'"\r\n"You should be more assertive."\r\n\r\n「私はノーと言えない性格なんです」\r\n「あなたはもう少し積極的に自己主張するべきです」', '2021-09-02 12:10:38', NULL, 1, 1),
|
|
39
|
+
(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),
|
|
40
|
+
(88, 'Sum up', '要約する', 'Can you sum up what you said ?', '2021-09-02 12:07:19', NULL, 1, 1),
|
|
41
|
+
(89, 'sample', 'サンプル', NULL, '2021-12-30 21:57:36', '2021-12-30 21:58:47', 4, 2);
|
|
42
|
+
|
|
43
|
+
```
|
|
44
|
+
クイズテーブル
|
|
45
|
+
```SQL
|
|
46
|
+
CREATE TABLE `quiz` (
|
|
47
|
+
`id` int(11) NOT NULL COMMENT '主キー',
|
|
48
|
+
`correctCount` int(11) NOT NULL COMMENT '単語正解した回数',
|
|
49
|
+
`falseCount` int(11) NOT NULL COMMENT '単語間違えた数',
|
|
50
|
+
`lastCorrectedTime` datetime DEFAULT NULL COMMENT '最後に正解した時間',
|
|
51
|
+
`lastFalseTime` datetime DEFAULT NULL COMMENT '最後に間違えた時間',
|
|
52
|
+
`word_id` int(11) NOT NULL COMMENT '単語外部キー',
|
|
53
|
+
`user_id` int(11) NOT NULL COMMENT 'ユーザー外部キー'
|
|
54
|
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
55
|
+
```
|
|
56
|
+
|
|
57
|
+
```SQL
|
|
58
|
+
INSERT INTO `quiz` (`id`, `correctCount`, `falseCount`, `lastCorrectedTime`, `lastFalseTime`, `word_id`, `user_id`) VALUES
|
|
59
|
+
(61, 1, 0, '2022-01-08 13:27:32', NULL, 75, 1),
|
|
60
|
+
(62, 1, 0, '2022-01-08 13:27:32', NULL, 78, 1),
|
|
61
|
+
(63, 1, 0, '2022-01-08 13:27:32', NULL, 76, 1),
|
|
62
|
+
(64, 0, 1, NULL, '2022-01-08 13:27:32', 64, 1),
|
|
63
|
+
(72, 2, 0, '2022-01-09 11:34:30', NULL, 77, 4),
|
|
64
|
+
(73, 3, 0, '2022-01-09 11:34:30', NULL, 85, 4),
|
|
65
|
+
(74, 2, 1, '2022-01-08 17:14:56', '2022-01-08 13:30:00', 75, 4),
|
|
66
|
+
(78, 2, 0, '2022-01-09 11:34:30', NULL, 64, 4),
|
|
67
|
+
(79, 1, 0, '2022-01-08 17:14:56', NULL, 78, 4),
|
|
68
|
+
(80, 1, 0, '2022-01-09 11:34:30', NULL, 76, 4),
|
|
69
|
+
(81, 1, 0, '2022-01-09 11:34:30', NULL, 87, 4),
|
|
70
|
+
(82, 1, 0, '2022-01-09 11:34:30', NULL, 74, 4),
|
|
71
|
+
```
|
|
72
|
+
|
|
9
73
|
逆に言うと、今までまだ一度もユーザーがクイズで回答していない単語について、その単語の単語IDとそのユーザーのユーザーIDを外部キーとして併せ持つレコードはクイズテーブルには存在しません。それを利用して今回の要望を実現したいと考えております。
|
|
10
74
|
（単語自体は、他のユーザーが回答している場合、クイズテーブルに追加されていますが、ユーザーIDの外部キがそのユーザーとは異なる。）
|
|
11
75
|
|
|
@@ -23,14 +87,26 @@
|
|
|
23
87
|
|
|
24
88
|
ご教授お願い致します。
|
|
25
89
|
|
|
90
|
+
現在書いているSQL文
|
|
26
91
|
```SQL
|
|
27
|
-
SELECT
|
|
92
|
+
SELECT w.id, w.word, w.meaning
|
|
28
93
|
FROM words w
|
|
94
|
+
WHERE w.delete_datetime IS NULL AND w.list_id = 1
|
|
29
|
-
|
|
95
|
+
AND NOT EXISTS (
|
|
30
96
|
SELECT * FROM quiz q
|
|
31
97
|
LEFT JOIN words w ON w.id = q.word_id
|
|
32
|
-
WHERE q.user_id =
|
|
98
|
+
WHERE w.list_id = 1 AND q.user_id = 1
|
|
99
|
+
AND w.delete_datetime IS NULL
|
|
33
100
|
)
|
|
34
101
|
ORDER BY rand()
|
|
35
102
|
LIMIT 10
|
|
103
|
+
```
|
|
104
|
+
希望するSELECT結果（ユーザーによってこのNOT IN句に指定しなければいけない単語IDが違うため、動的に取得できる上記を書いている）
|
|
105
|
+
```SQL
|
|
106
|
+
SELECT w.id, w.word, w.meaning
|
|
107
|
+
FROM words w
|
|
108
|
+
WHERE w.delete_datetime IS NULL AND w.list_id = 1 AND
|
|
109
|
+
w.id NOT IN("75", "76", "78")
|
|
110
|
+
ORDER BY rand()
|
|
111
|
+
LIMIT 10
|
|
36
112
|
```
|