mysql のクエリいろいろ

(2025-07-16)

mysql に英単語などを記録してアプリを作成しています。

使い勝手を良くするためにデータベースに対する命令（クエリ）を作成するのですが、面倒なのでいつも chatGPT に依頼しています。

でも、いつも質問するのも面倒なので、代表的なものをいくつか記録しておきたいと思います。

group 化してカウントする

英単語データベースに grade を付けていますが、その grade ごとに集計します。

SELECT grade, COUNT(*) AS count
FROM wordlists
GROUP BY grade
ORDER BY count DESC;

2 つのテーブルを結合して group 化してカウント

現在、単語テストのテーブルには単語そのものを記録していません。 vocabulary_id として記録しています。

なので、2 つのテーブルを結合してカウントします。

SELECT
    wl.word,wl.grade,
    COUNT(*) AS incorrect_count
FROM
    word_tests wt
JOIN
    wordlists wl ON wt.vocabulary_id = wl.id
WHERE
    wt.evaluation != '○'
GROUP BY
    wl.word
ORDER BY
    incorrect_count DESC;

B1 でも全然憶えられない単語がいっぱいあります。数字は間違った回数です。

正解するまでトライすればいいんですが、何らかの引っ掛かりがないと簡単には憶えられません。

文字列の「照合順序（collation）」が異なる場合の update

UPDATE tokens
JOIN STEP.wordlists
  ON tokens.lemma COLLATE utf8mb4_unicode_520_ci = wordlists.word COLLATE utf8mb4_unicode_520_ci
SET tokens.grade = wordlists.grade;

英単語テストの誤答

英単語のボキャブラリーを増やすために laravel でアプリを作っています。

正解を出せなかった問題を集計します。

ある grade で不正解になった単語を、テスト回数の少ない方から抽出します。

SELECT
    wl.word,
    wt.evaluation,
    counts.test_count
FROM wordlists wl
JOIN word_tests wt
    ON wl.id = wt.vocabulary_id
JOIN (
    SELECT vocabulary_id, MAX(id) AS latest_test_id
    FROM word_tests
    GROUP BY vocabulary_id
) latest
    ON wt.id = latest.latest_test_id
JOIN (
    SELECT vocabulary_id, COUNT(*) AS test_count
    FROM word_tests
    GROUP BY vocabulary_id
) counts
    ON wl.id = counts.vocabulary_id
WHERE wl.grade = 'B2'
  AND wt.evaluation != '○'
ORDER BY counts.test_count ASC;

JOIN の部分がわかりにくいですが、サブクエリの結果を latest という名前の仮想テーブルとして扱います。

JOIN (
    SELECT vocabulary_id, MAX(id) AS latest_test_id
    FROM word_tests
    GROUP BY vocabulary_id
) latest

word_tests から各単語ごとの最新テストIDを計算するサブクエリです。

JOIN (
    SELECT vocabulary_id, COUNT(*) AS test_count
    FROM word_tests
    GROUP BY vocabulary_id
) counts

各単語のテスト回数を集計した仮想テーブルを作っています。