Από τη Βικιπαίδεια, την ελεύθερη εγκυκλοπαίδεια
USE elwiki_p;
SELECT CONCAT('# [[',page_title,']]') as page_title
FROM page
JOIN externallinks
ON page_id = el_from
WHERE page_namespace = 0
AND (el_to LIKE 'http%://%.facebook.%/%'
OR el_to LIKE 'http%://facebook.%/%'
OR el_to LIKE 'http%://fb.%/%'
OR el_to LIKE 'http%://%.fb.%/%'
OR el_to LIKE 'http%://twitter.%/%'
OR el_to LIKE 'http%://%.twitter.%/%'
OR el_to LIKE 'http%://naszaklasa.%/%'
OR el_to LIKE 'http%://%.naszaklasa.%/%'
OR el_to LIKE 'http%://nk.pl/%'
OR el_to LIKE 'http%://%.nk.pl/%'
OR el_to LIKE 'http%://myspace.%/%'
OR el_to LIKE 'http%://%.myspace.%/%'
OR el_to LIKE 'http%://vk.%/%'
OR el_to LIKE 'http%://%.vk.%/%'
OR el_to LIKE 'http%://vkontak.%/%'
OR el_to LIKE 'http%://%.vkontak.%/%'
OR el_to LIKE 'http%://odnoklassniki.ru/%'
OR el_to LIKE 'http%://%.odnoklassniki.ru/%'
OR el_to LIKE 'http%://ok.ru/%'
OR el_to LIKE 'http%://%.ok.ru/%'
OR el_to LIKE 'http%://ok.ru/%'
OR el_to LIKE 'http%://%.instagram.com/%'
OR el_to LIKE 'http%://instagram.com/%'
OR el_to LIKE 'http%://%.pinterest.com/%'
OR el_to LIKE 'http%://pinterest.com/%'
OR el_to LIKE 'http%://%.linkedin.com/%'
OR el_to LIKE 'http%://linkedin.com/%'
OR el_to LIKE 'http%://%.soundcloud.com/%'
OR el_to LIKE 'http%://soundcloud.com/%'
OR el_to LIKE 'http%://%.soundcloud.com/%'
OR el_to LIKE 'http%://last.fm/%'
OR el_to LIKE 'http%://%.last.fm/%'
OR el_to LIKE 'http%://plus.google.%/%'
OR el_to LIKE 'http%://%.plus.google.%/%')
AND el_to NOT LIKE '%archive.%'
GROUP BY page_title
ORDER BY page_title;
SELECT CONCAT('# ',el_to) as el_to#, COUNT(*) AS counting
FROM page
JOIN externallinks
ON page_id = el_from
WHERE page_namespace = 0
AND (el_to LIKE 'http%://%.facebook.%/%'
OR el_to LIKE 'http%://facebook.%/%'
OR el_to LIKE 'http%://fb.%/%'
OR el_to LIKE 'http%://%.fb.%/%'
OR el_to LIKE 'http%://twitter.%/%'
OR el_to LIKE 'http%://%.twitter.%/%'
OR el_to LIKE 'http%://naszaklasa.%/%'
OR el_to LIKE 'http%://%.naszaklasa.%/%'
OR el_to LIKE 'http%://nk.pl/%'
OR el_to LIKE 'http%://%.nk.pl/%'
OR el_to LIKE 'http%://myspace.%/%'
OR el_to LIKE 'http%://%.myspace.%/%'
OR el_to LIKE 'http%://vk.%/%'
OR el_to LIKE 'http%://%.vk.%/%'
OR el_to LIKE 'http%://vkontak.%/%'
OR el_to LIKE 'http%://%.vkontak.%/%'
OR el_to LIKE 'http%://odnoklassniki.ru/%'
OR el_to LIKE 'http%://%.odnoklassniki.ru/%'
OR el_to LIKE 'http%://ok.ru/%'
OR el_to LIKE 'http%://%.ok.ru/%'
OR el_to LIKE 'http%://ok.ru/%'
OR el_to LIKE 'http%://%.instagram.com/%'
OR el_to LIKE 'http%://instagram.com/%'
OR el_to LIKE 'http%://%.pinterest.com/%'
OR el_to LIKE 'http%://pinterest.com/%'
OR el_to LIKE 'http%://%.linkedin.com/%'
OR el_to LIKE 'http%://linkedin.com/%'
OR el_to LIKE 'http%://%.soundcloud.com/%'
OR el_to LIKE 'http%://soundcloud.com/%'
OR el_to LIKE 'http%://%.soundcloud.com/%'
OR el_to LIKE 'http%://last.fm/%'
OR el_to LIKE 'http%://%.last.fm/%'
OR el_to LIKE 'http%://plus.google.%/%'
OR el_to LIKE 'http%://%.plus.google.%/%')
AND el_to NOT LIKE '%archive.%'
GROUP BY el_to
ORDER BY /*counting DESC,*/ el_to;
