SET @ns = 1;

USE zhwiki_p;

SELECT

    CONCAT("https://zh.wikipedia.org/w/index.php?title=",

case

    when talkpage.page_namespace = 0 then ""

    when talkpage.page_namespace = 1 then "Talk:"

    when talkpage.page_namespace = 2 then "User:"

    when talkpage.page_namespace = 3 then "User talk:"

    when talkpage.page_namespace = 4 then "Wikpeida:"

    when talkpage.page_namespace = 5 then "Wikpeida talk:"

    when talkpage.page_namespace = 6 then "File:"

    when talkpage.page_namespace = 7 then "File talk:"

    when talkpage.page_namespace = 8 then "Mediawiki:"

    when talkpage.page_namespace = 9 then "Mediawiki talk:"

    when talkpage.page_namespace = 10 then "Template:"

    when talkpage.page_namespace = 11 then "Template talk:"

    when talkpage.page_namespace = 12 then "Help:"

    when talkpage.page_namespace = 13 then "Help talk:"

    when talkpage.page_namespace = 14 then "Category:"

    when talkpage.page_namespace = 15 then "Category talk:"

    when talkpage.page_namespace = 100 then "Portal:"

    when talkpage.page_namespace = 101 then "Portal talk:"

    when talkpage.page_namespace = 118 then "Draft:"

    when talkpage.page_namespace = 119 then "Draft talk:"

    when talkpage.page_namespace = 828 then "Module:"

    when talkpage.page_namespace = 829 then "Module talk:"

end, ":", talkpage.page_title, "&redirect=no ") AS "talk link",

    CONCAT(

case

    when talkpage.page_namespace = 0 then ""

    when talkpage.page_namespace = 1 then "Talk:"

    when talkpage.page_namespace = 2 then "User:"

    when talkpage.page_namespace = 3 then "User talk:"

    when talkpage.page_namespace = 4 then "Wikpeida:"

    when talkpage.page_namespace = 5 then "Wikpeida talk:"

    when talkpage.page_namespace = 6 then "File:"

    when talkpage.page_namespace = 7 then "File talk:"

    when talkpage.page_namespace = 8 then "Mediawiki:"

    when talkpage.page_namespace = 9 then "Mediawiki talk:"

    when talkpage.page_namespace = 10 then "Template:"

    when talkpage.page_namespace = 11 then "Template talk:"

    when talkpage.page_namespace = 12 then "Help:"

    when talkpage.page_namespace = 13 then "Help talk:"

    when talkpage.page_namespace = 14 then "Category:"

    when talkpage.page_namespace = 15 then "Category talk:"

    when talkpage.page_namespace = 100 then "Portal:"

    when talkpage.page_namespace = 101 then "Portal talk:"

    when talkpage.page_namespace = 118 then "Draft:"

    when talkpage.page_namespace = 119 then "Draft talk:"

    when talkpage.page_namespace = 828 then "Module:"

    when talkpage.page_namespace = 829 then "Module talk:"

end, ":", talkpage.page_title) AS "talk",

talkpage.page_namespace AS "talk ns",

mainpage.page_title AS "main",

mainpage.page_namespace AS "main ns"

FROM page AS talkpage

LEFT JOIN page AS mainpage ON talkpage.page_title = mainpage.page_title AND talkpage.page_namespace = mainpage.page_namespace+1

WHERE talkpage.page_namespace = (@ns)

    AND talkpage.page_title NOT LIKE "%/%"

    AND mainpage.page_namespace IS NULL

#ORDER BY mainpage.page_title DESC

LIMIT 10