当前位置: 数据库>sqlserver
sql语句实例 取得孤立的讨论页
来源: 互联网 发布时间:2014-08-29
本文导语: 取得孤立讨论页的sql语句实例,代码: 代码示例: SELECT p1.page_namespace, ns_name, p1.page_title FROM page AS p1 JOIN toolserver.namespace ON p1.page_namespace = ns_id AND dbname = 'enwiki_p' WHERE p1.page_title NOT LIKE "%/%" AND p1.page_namespace NOT IN (0,2,3...
取得孤立讨论页的sql语句实例,代码:
代码示例:
SELECT
p1.page_namespace,
ns_name,
p1.page_title
FROM page AS p1
JOIN toolserver.namespace
ON p1.page_namespace = ns_id
AND dbname = 'enwiki_p'
WHERE p1.page_title NOT LIKE "%/%"
AND p1.page_namespace NOT IN (0,2,3,4,6,8,9,10,12,14,16,18,100,102,104)
AND CASE WHEN p1.page_namespace = 1
THEN NOT EXISTS (SELECT
1
FROM page AS p2
WHERE p2.page_namespace = 0
AND p1.page_title = p2.page_title)
ELSE 1 END
AND CASE WHEN p1.page_namespace = 5
THEN NOT EXISTS (SELECT
1
FROM page AS p2
WHERE p2.page_namespace = 4
AND p1.page_title = p2.page_title)
ELSE 1 END
AND CASE WHEN p1.page_namespace = 7
THEN NOT EXISTS (SELECT
1
FROM page AS p2
WHERE p2.page_namespace = 6
AND p1.page_title = p2.page_title)
AND NOT EXISTS (SELECT
1
FROM commonswiki_p.page AS p2
WHERE p2.page_namespace = 6
AND p1.page_title = p2.page_title)
ELSE 1 END
AND CASE WHEN p1.page_namespace = 11
THEN NOT EXISTS (SELECT
1
FROM page AS p2
WHERE p2.page_namespace = 10
AND p1.page_title = p2.page_title)
ELSE 1 END
AND CASE WHEN p1.page_namespace = 13
THEN NOT EXISTS (SELECT
1
FROM page AS p2
WHERE p2.page_namespace = 12
AND p1.page_title = p2.page_title)
ELSE 1 END
AND CASE WHEN p1.page_namespace = 15
THEN NOT EXISTS (SELECT
1
FROM page AS p2
WHERE p2.page_namespace = 14
AND p1.page_title = p2.page_title)
ELSE 1 END
AND CASE WHEN p1.page_namespace = 17
THEN NOT EXISTS (SELECT
1
FROM page AS p2
WHERE p2.page_namespace = 16
AND p1.page_title = p2.page_title)
ELSE 1 END
AND CASE WHEN p1.page_namespace = 101
THEN NOT EXISTS (SELECT
1
FROM page AS p2
WHERE p2.page_namespace = 100
AND p1.page_title = p2.page_title)
ELSE 1 END
AND p1.page_id NOT IN (SELECT
page_id
FROM page
JOIN templatelinks
ON page_id = tl_from
WHERE tl_title="G8-exempt"
AND tl_namespace = 10)
AND p1.page_id NOT IN (SELECT
page_id
FROM page
JOIN templatelinks
ON page_id = tl_from
WHERE tl_title="Go_away"
AND tl_namespace = 10)
AND p1.page_id NOT IN (SELECT
page_id
FROM page
JOIN templatelinks
ON page_id = tl_from
WHERE tl_title="Rtd"
AND tl_namespace = 10);
p1.page_namespace,
ns_name,
p1.page_title
FROM page AS p1
JOIN toolserver.namespace
ON p1.page_namespace = ns_id
AND dbname = 'enwiki_p'
WHERE p1.page_title NOT LIKE "%/%"
AND p1.page_namespace NOT IN (0,2,3,4,6,8,9,10,12,14,16,18,100,102,104)
AND CASE WHEN p1.page_namespace = 1
THEN NOT EXISTS (SELECT
1
FROM page AS p2
WHERE p2.page_namespace = 0
AND p1.page_title = p2.page_title)
ELSE 1 END
AND CASE WHEN p1.page_namespace = 5
THEN NOT EXISTS (SELECT
1
FROM page AS p2
WHERE p2.page_namespace = 4
AND p1.page_title = p2.page_title)
ELSE 1 END
AND CASE WHEN p1.page_namespace = 7
THEN NOT EXISTS (SELECT
1
FROM page AS p2
WHERE p2.page_namespace = 6
AND p1.page_title = p2.page_title)
AND NOT EXISTS (SELECT
1
FROM commonswiki_p.page AS p2
WHERE p2.page_namespace = 6
AND p1.page_title = p2.page_title)
ELSE 1 END
AND CASE WHEN p1.page_namespace = 11
THEN NOT EXISTS (SELECT
1
FROM page AS p2
WHERE p2.page_namespace = 10
AND p1.page_title = p2.page_title)
ELSE 1 END
AND CASE WHEN p1.page_namespace = 13
THEN NOT EXISTS (SELECT
1
FROM page AS p2
WHERE p2.page_namespace = 12
AND p1.page_title = p2.page_title)
ELSE 1 END
AND CASE WHEN p1.page_namespace = 15
THEN NOT EXISTS (SELECT
1
FROM page AS p2
WHERE p2.page_namespace = 14
AND p1.page_title = p2.page_title)
ELSE 1 END
AND CASE WHEN p1.page_namespace = 17
THEN NOT EXISTS (SELECT
1
FROM page AS p2
WHERE p2.page_namespace = 16
AND p1.page_title = p2.page_title)
ELSE 1 END
AND CASE WHEN p1.page_namespace = 101
THEN NOT EXISTS (SELECT
1
FROM page AS p2
WHERE p2.page_namespace = 100
AND p1.page_title = p2.page_title)
ELSE 1 END
AND p1.page_id NOT IN (SELECT
page_id
FROM page
JOIN templatelinks
ON page_id = tl_from
WHERE tl_title="G8-exempt"
AND tl_namespace = 10)
AND p1.page_id NOT IN (SELECT
page_id
FROM page
JOIN templatelinks
ON page_id = tl_from
WHERE tl_title="Go_away"
AND tl_namespace = 10)
AND p1.page_id NOT IN (SELECT
page_id
FROM page
JOIN templatelinks
ON page_id = tl_from
WHERE tl_title="Rtd"
AND tl_namespace = 10);
您可能感兴趣的文章:
SQL Server中Case语句的不同用法
SQL基础语句(数据库函数,时间函数,Union,Case,索引等)
sql server中的select case when
sql server中case when then else用法