开发者

MySQL: Select rows with more than one occurrence

开发者 https://www.devze.com 2023-01-26 01:19 出处:网络
Here\'s my query. It selects a list of ids from two tables across two databases. The query works fine.

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 != '' 

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

So for in开发者_Python百科stance, if this was the current query result

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

I only want to query to show this instead

 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
0

精彩评论

暂无评论...
验证码 换一张
取 消