代码之家 › 专栏 › 技术社区 › grabbag

3个表的层次结构中的计数和分组依据

sql mysql

grabbag · 技术社区 · 7 年前

我有3个表的层次结构。

我需要一个查询,它返回顶部表中的所有条目,并统计该层次结构底部的条目。

例子:

table paragraph has columns : id_paragraph
table sentence has columns  : id_sentence, id_paragraph
table word has columns      : id_word, id_sentence, font, color

输出示例:

id_paragraph, sentence_count, word_count, word_count_no_font, word_count_font_present, word_count_no_color, word_count_color_present
1, 10, 100, 20, 80, 25, 75
2, 20, 200, 40, 160, 50, 150

id_段落=1,有10个句子,这些句子中的单词和=100,这个段落中的这些单词的总和,其中字体为空的这个段落中的这些单词的总和=20,这个段落中的这些句子中的单词的总和如果这个段落中的颜色是空的,这些单词的总和=25,本段中这些句子中颜色不为空的单词之和=75。

我看到了“left join”和“groupby”如何给我一个2 deep层次结构的解决方案。我试着扭 SQL JOIN 3 TABLES WITH COUNT AND GROUP BY CLAUSE

0 回复 | 直到 7 年前

Gordon Linoff 7 年前

就用吧 count(distinct)

select s.id_paragraph,
       count(distinct s.id_sentence) as num_sentences,
       count(*) as num_words,
       count(w.font) as num_words_with_font,
       sum(w.font is null) as num_words_without_font,
       count(w.color) as num_words_with_color,
       sum(w.color is null) as num_words_without_color
from sentence s join
     word w
     on w.id_sentence = s.id_word
group by s.id_paragraph;

Vladimir.V.Bvn 7 年前

SELECT p.id_paragraph,
(SELECT COUNT(s.id_sentence) FROM sentence s WHERE p.id_paragraph = s.id_paragraph) sentence_count,
(SELECT COUNT(w.id_word) FROM sentence s, word w WHERE s.id_sentence = w.id_sentence AND p.id_paragraph = s.id_paragraph) word_count,
(SELECT COUNT(IFNULL(w.font, 1)) FROM sentence s, word w WHERE s.id_sentence = w.id_sentence AND p.id_paragraph = s.id_paragraph AND w.font IS NULL) word_count_no_font,
(SELECT COUNT(w.font) FROM sentence s, word w WHERE s.id_sentence = w.id_sentence AND p.id_paragraph = s.id_paragraph AND w.font IS NOT NULL) word_count_font_present
...
FROM paragraph p
;

推荐文章

Bard.Mus · 迁移后的数据库字符集环境

1 年前

tinkerr · 如何在2025年在MacOS上安装MySQL 5.7

1 年前