使用SQL查询统计单词出现次数

Question

我有两张桌子。

Table1：

ID   SENTENCE
1    The shoes are good shoes.
2    There is a tree.
3    This is nice, nice, nice!

Table2：

ID   WORD
1    The
1    shoes
1    are
1    good
1    shoes
2    There
2    is
2    a
2    tree
3    This
3    is
3    nice
3    nice
3    nice

我需要计算Table1中每个句子中每个单词的出现次数。如果某个单词出现多次(>1)，则对其进行计数，否则跳过该单词。最后，生成的表格应该如下所示：

ID   SENTENCE                   CNT
1    The shoes are good shoes.  2
2    There is a tree.
3    This is nice, nice, nice!  3

Answer 1

您可以使用count() over()

select distinct t1.id,
  t1.sentence,
  coalesce(t2.cnt, 0) cnt
from table1 t1
left join 
(
  select t1.id, 
    t1.sentence,
    t2.word,
    count(t2.word) over(partition by t1.id, t2.word) cnt
  from table1 t1
  left join table2 t2
    on t1.id = t2.id
) t2
  on t1.id = t2.id
  and t2.cnt > 1
order by t1.id

参见SQL Fiddle with Demo。

或者，您可以只使用count()

select t1.id,
  t1.sentence,
  coalesce(t2.cnt, 0) cnt
from table1 t1
left join 
(
  select t1.id, 
    t1.sentence,
    t2.word,
    count(t2.word) cnt
  from table1 t1
  left join table2 t2
    on t1.id = t2.id
  group by t1.id, t1.sentence, t2.word
  having count(t2.word) > 1
) t2
  on t1.id = t2.id
order by t1.id 

请参阅SQL Fiddle with Demo

Answer 2

SQL DEMO

select t1.id, t1.sentence, 
coalesce(t2.cnt,0) as counts
from table1 t1
left join
(select id, word, count(id) cnt
from table2
group by id, word
having count(id) > 1)t2
on t1.id = t2.id
order by t1.id
;

| ID |                  SENTENCE | COUNTS |
-------------------------------------------
|  1 | The shoes are good shoes. |      2 |
|  2 |          There is a tree. |      0 |
|  3 | This is nice, nice, nice! |      3 |

Answer 3

SELECT table1.id, table1.sentence, COUNT(word) as cnt FROM table2 JOIN table1 ON table1.id = table2.id GROUP BY table2.word HAVING COUNT(word) > 1

我的答案是mysql，我现在正在验证它在sql中也可以工作