select now();

​

use wikidatawiki_p;

SELECT ips_item_id, COUNT(ips_item_id), (SELECT GROUP_CONCAT(distinct concat(wb1.ips_site_id, '-frkmarker-',wb1.ips_site_page) separator '|') as 'otherlinks'

                                         FROM wb_items_per_site wb1

                                         WHERE wb1.ips_site_id in ('enwiki','eswiki','ruwiki','frwiki') and wb1.ips_item_id=www.ips_item_id) as 'other links'

FROM wb_items_per_site www

WHERE ips_site_id LIKE '%wiki'

AND NOT ips_site_id='wikidatawiki'

AND NOT ips_site_id='commonswiki'

GROUP BY ips_item_id 

HAVING COUNT(ips_item_id) > 49

AND ips_item_id NOT IN

(SELECT ips_item_id

FROM wb_items_per_site

WHERE ips_site_id='dewiki')

ORDER BY COUNT(ips_item_id) DESC;