jawiktから名詞を抽出するサンプル

SQL

use jawiktionary_p;

/*
SELECT GROUP_CONCAT(cl_to)
FROM categorylinks cl, page p
WHERE cl.cl_from = p.page_id
AND p.page_id = 12345
GROUP by cl.cl_from
;
*/

SELECT p.page_title
FROM categorylinks cl, page p
WHERE TRUE
AND p.page_namespace = 0
AND p.page_is_redirect = 0
AND cl.cl_from = p.page_id
AND (
	SELECT GROUP_CONCAT(cl_to)
	FROM categorylinks clk, page pg
	WHERE clk.cl_from = pg.page_id
	AND pg.page_id = p.page_id
	GROUP by clk.cl_from
) NOT LIKE "%造語成分%"
AND (
	SELECT GROUP_CONCAT(cl_to)
	FROM categorylinks clk, page pg
	WHERE clk.cl_from = pg.page_id
	AND pg.page_id = p.page_id
	GROUP by clk.cl_from
) NOT LIKE "%オノマトペ%"
AND cl.cl_to = "日本語_名詞"
ORDER BY page_random
LIMIT 1000;
SQL

Checking query status...
