-- SET @ime_kat_unos = 'Planinarstvo po godinama';              -- promijeniti naziv kategorije po želji

​

​

​

-- SET @ime_kat = REPLACE(@ime_kat_unos, ' ', '_');

use hrwiki_p;

​

​

SELECT DISTINCT cat_title, page.page_len, count(distinct(cl.cl_from)) as cnt -- , count( distinct(cl.cl_from)) 

                                                        -- staviti "--(razmak)" ispred ", count..." za popis umjesto broja

from category as c

inner join categorylinks as cl on c.cat_title = cl.cl_to

inner join page on cl.cl_from = page.page_id

inner join revision_userindex as r_ui on r_ui.rev_page = page.page_id

inner join actor_revision as a_rev on r_ui.rev_actor = a_rev.actor_id

-- where c.cat_title = @ime_kat

where cat_title like '%_po_godinama'

 and page.page_namespace in (10,14)

--  and page.page_is_new = 0

  and page.page_is_redirect = 0

  -- and actor_name != "Kubura"

  -- and actor_name NOT LIKE "%Bot" 

group by cat_title, page_len

having cnt>0

order by  cnt desc,cat_title, page_len desc

limit 800;

​

​

/*

​

SELECT DISTINCT c.cat_id, c.cat_pages, page.page_title, page.page_len  -- , count( distinct(cl.cl_from)) 

                                                        -- staviti "--(razmak)" ispred ", count..." za popis umjesto broja

from category as c

inner join categorylinks as cl on c.cat_title = cl.cl_to

inner join page on cl.cl_from = page.page_id

inner join revision_userindex as r_ui on r_ui.rev_page = page.page_id

inner join actor_revision as a_rev on r_ui.rev_actor = a_rev.actor_id

where c.cat_title = @ime_kat

and page.page_namespace = 0

--  and page.page_is_new = 0

  and page.page_is_redirect = 0

  -- and actor_name != "Kubura"

  and actor_name NOT LIKE "%Bot" 

order by page_len asc

limit 300;

*/

​

​

​

​

-- stranice u nekoj _po_godinama kategoriji točno određene veličine (iz gornjeg upita)

SELECT DISTINCT cl.cl_from, c.cat_id, c.cat_pages, page.page_title, page.page_len  -- , count( distinct(cl.cl_from)) 

                                                        -- staviti "--(razmak)" ispred ", count..." za popis umjesto broja

from category as c

inner join categorylinks as cl on c.cat_title = cl.cl_to

inner join page on cl.cl_from = page.page_id

inner join revision_userindex as r_ui on r_ui.rev_page = page.page_id

inner join actor_revision as a_rev on r_ui.rev_actor = a_rev.actor_id

-- where c.cat_title = @ime_kat

where cat_title = 'Medicina_po_godinama' and page_len=1795

and page.page_namespace = 10

--  and page.page_is_new = 0

   and page.page_is_redirect = 0

  -- and actor_name != "Kubura"

  -- and actor_name NOT LIKE "%Bot" 

order by page_title, page_len asc

limit 600;

​