当前位置: 数据库>sqlserver
sql语句实例 查询相关联的类别与空分类信息
来源: 互联网 发布时间:2014-08-29
本文导语: 1,查询相关联的类别信息 代码示例: SELECT cattmp.cl_to, cattmp.cl_count, user_name, log_timestamp, log_comment FROM logging JOIN `user` ON log_user = user_id JOIN (SELECT cl_to, COUNT(cl_to) AS cl_count FROM categorylinks LEFT JOIN page O...
1,查询相关联的类别信息
代码示例:
SELECT
cattmp.cl_to,
cattmp.cl_count,
user_name,
log_timestamp,
log_comment
FROM logging
JOIN `user` ON log_user = user_id
JOIN
(SELECT
cl_to,
COUNT(cl_to) AS cl_count
FROM categorylinks
LEFT JOIN page ON cl_to = page_title
AND page_namespace = 14
WHERE page_title IS NULL
GROUP BY cl_to) AS cattmp
ON log_title = cattmp.cl_to
WHERE log_namespace = 14
AND log_type = "delete"
AND log_timestamp = (SELECT
MAX(log_timestamp)
FROM logging AS LAST
WHERE log_namespace = 14
AND cattmp.cl_to = LAST.log_title);
cattmp.cl_to,
cattmp.cl_count,
user_name,
log_timestamp,
log_comment
FROM logging
JOIN `user` ON log_user = user_id
JOIN
(SELECT
cl_to,
COUNT(cl_to) AS cl_count
FROM categorylinks
LEFT JOIN page ON cl_to = page_title
AND page_namespace = 14
WHERE page_title IS NULL
GROUP BY cl_to) AS cattmp
ON log_title = cattmp.cl_to
WHERE log_namespace = 14
AND log_type = "delete"
AND log_timestamp = (SELECT
MAX(log_timestamp)
FROM logging AS LAST
WHERE log_namespace = 14
AND cattmp.cl_to = LAST.log_title);
2,查询为空的分类信息
代码示例:
SELECT
page_title,
page_len
FROM categorylinks
RIGHT JOIN page ON cl_to = page_title
WHERE page_namespace = 14
AND page_is_redirect = 0
AND cl_to IS NULL
AND NOT EXISTS (SELECT
1
FROM categorylinks
WHERE cl_from = page_id
AND cl_to = 'Wikipedia_category_redirects')
AND NOT EXISTS (SELECT
1
FROM categorylinks
WHERE cl_from = page_id
AND cl_to = 'Disambiguation_categories')
AND NOT EXISTS (SELECT
1
FROM templatelinks
WHERE tl_from = page_id
AND tl_namespace = 10
AND tl_title = 'Empty_category');
page_title,
page_len
FROM categorylinks
RIGHT JOIN page ON cl_to = page_title
WHERE page_namespace = 14
AND page_is_redirect = 0
AND cl_to IS NULL
AND NOT EXISTS (SELECT
1
FROM categorylinks
WHERE cl_from = page_id
AND cl_to = 'Wikipedia_category_redirects')
AND NOT EXISTS (SELECT
1
FROM categorylinks
WHERE cl_from = page_id
AND cl_to = 'Disambiguation_categories')
AND NOT EXISTS (SELECT
1
FROM templatelinks
WHERE tl_from = page_id
AND tl_namespace = 10
AND tl_title = 'Empty_category');
以上代码,演示了right join与exists的用法,大家参考下。
您可能感兴趣的文章:
本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术,将尽最大努力为读者提供更好的信息聚合和浏览方式。
本站(WWW.)站内文章除注明原创外,均为转载、整理或搜集自网络。欢迎任何形式的转载,转载请注明出处。
本站(WWW.)站内文章除注明原创外,均为转载、整理或搜集自网络。欢迎任何形式的转载,转载请注明出处。