您可以使用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
See SQL 摆弄演示 http://www.sqlfiddle.com/#!4/9a949/16.
或者你可以只使用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
See SQL 摆弄演示 http://www.sqlfiddle.com/#!4/9a949/22