MySQL:选择出现多次的行 [英] MySQL: Select rows with more than one occurrence

问题描述

这是我的查询.它从两个数据库中的两个表中选择一个ID列表.该查询工作正常.

Here's my query. It selects a list of ids from two tables across two databases. The query works fine.

select en.id, fp.blogid
from french.blog_pics fp, french.blog_news fn, english.blog_news en 
where fp.blogid = fn.id 
and en.title_fr = fn.title 
and fp.title != '' 

我只想显示en.id出现多次的行

I only want to display rows where a en.id occurs more than once

例如，如果这是当前查询结果

So for instance, if this was the current query result

en.id fp.blogid
---------------
  10     12
  12     8
  17     9
  12     8

我只想查询显示此内容

 en.id fp.blogid occurrences
 -----------------------------
  12     8           2

推荐答案

select en.id, fp.blogid, count(*) as occurrences
from french.blog_pics fp, french.blog_news fn, english.blog_news en 
where fp.blogid = fn.id 
and en.title_fr = fn.title 
and fp.title != ''
group by en.id
having count(*) > 1

这篇关于MySQL:选择出现多次的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！