基于指定数据表的SQL查询需求：单词统计相关问题

嘿，我来帮你搞定这几个基于给定表结构的SQL查询需求～ 每个需求我都写了对应的语句，还加了点说明方便你理解：

1. 统计所有书籍中仅出现一次的唯一单词总数 #

这里的核心是先算出每个单词在所有书籍里的总出现次数，再筛选出总次数恰好为1的单词，最后统计这些单词的数量：

-- 方式1：关联单词表确保统计的是有效单词
SELECT COUNT(w.id) AS unique_words_once_total
FROM m-words w
INNER JOIN (
    SELECT wordid
    FROM m-wordscounts
    GROUP BY wordid
    HAVING SUM(count) = 1
) wc ON w.id = wc.wordid;

-- 方式2：更简洁的统计（如果确认所有wordid都对应有效单词）
SELECT COUNT(*) AS unique_words_once_total
FROM (
    SELECT wordid
    FROM m-wordscounts
    GROUP BY wordid
    HAVING SUM(count) = 1
) AS temp;

2. 统计每本书中仅出现一次的唯一单词数量（按title字母顺序排序） #

这个需求针对单本书，我们要找出每本书里出现次数刚好为1的单词，统计其数量后关联书籍表获取书名并排序：

SELECT 
    b.title,
    COALESCE(COUNT(wc.wordid), 0) AS unique_words_once_per_book
FROM m-books b
LEFT JOIN (
    SELECT bookid, wordid
    FROM m-wordscounts
    WHERE count = 1
) wc ON b.id = wc.bookid
GROUP BY b.id, b.title
ORDER BY b.title ASC;

用LEFT JOIN加COALESCE是为了包含那些没有任何仅出现一次单词的书籍，此时数量会显示为0。如果只需要统计有这类单词的书籍，换成INNER JOIN即可。

3. 找出所有书籍中出现频率最高的前8个单词 #

这里的频率指单词在所有书籍中的总出现次数，按总次数降序排序后取前8个：

-- MySQL、PostgreSQL等支持LIMIT的数据库
SELECT 
    w.word,
    SUM(wc.count) AS total_frequency
FROM m-words w
INNER JOIN m-wordscounts wc ON w.id = wc.wordid
GROUP BY w.id, w.word
ORDER BY total_frequency DESC
LIMIT 8;

-- SQL Server版本（替换LIMIT为TOP）
SELECT TOP 8
    w.word,
    SUM(wc.count) AS total_frequency
FROM m-words w
INNER JOIN m-wordscounts wc ON w.id = wc.wordid
GROUP BY w.id, w.word
ORDER BY total_frequency DESC;

-- Oracle版本（使用FETCH语法）
SELECT 
    w.word,
    SUM(wc.count) AS total_frequency
FROM m-words w
INNER JOIN m-wordscounts wc ON w.id = wc.wordid
GROUP BY w.id, w.word
ORDER BY total_frequency DESC
FETCH FIRST 8 ROWS ONLY;

4. 计算每本书的单词计数占该书总唯一单词数的百分比 #

这里分两种常见场景，你可以根据实际需求选择：

场景1：占该书总唯一单词数的百分比 #

总唯一单词数指这本书里包含的不同单词的总数：

SELECT 
    b.title,
    w.word,
    wc.count AS word_count,
    book_stats.total_unique_words,
    ROUND((wc.count * 100.0 / book_stats.total_unique_words), 2) AS percentage
FROM m-books b
INNER JOIN m-wordscounts wc ON b.id = wc.bookid
INNER JOIN m-words w ON wc.wordid = w.id
INNER JOIN (
    SELECT bookid, COUNT(DISTINCT wordid) AS total_unique_words
    FROM m-wordscounts
    GROUP BY bookid
) book_stats ON b.id = book_stats.bookid
ORDER BY b.title, percentage DESC;

场景2：占该书总单词数的百分比 #

总单词数用m-count表的totalwords字段：

SELECT 
    b.title,
    w.word,
    wc.count AS word_count,
    mc.totalwords AS total_book_words,
    ROUND((wc.count * 100.0 / mc.totalwords), 2) AS percentage
FROM m-books b
INNER JOIN m-wordscounts wc ON b.id = wc.bookid
INNER JOIN m-words w ON wc.wordid = w.id
INNER JOIN m-count mc ON b.id = mc.bookid
ORDER BY b.title, percentage DESC;

内容的提问来源于stack exchange，提问作者Muhammad Umar