Java,Jsp,模式及框架
Web技术
Web服务器
浏览器相关
SQL语言
数据库
开发环境
软件开发及管理
网站SEO
短信及邮件服务
网页设计
电脑、硬件及网络
协同管理平台问题
电子商务
前沿技术及趋势
  当前位置:首页 - 知识积累 - SQL语言
SQL 语言
时间:2008年10月05日 

    更新网站栏目的权限:

updatexloa_web_columnset xloa_web_column.users_edit=b.users_edit,

xloa_web_column.users_view=b.users_view from xloa_web_site b
where xloa_web_column.web_site_id=b.web_site_id

    参考:

    update b set col=a.col from a where a.id=b.id

    update b set b.col=a.col from a,b where a.id=b.id

    update b set col=a.col from b inner join a on a.id=b.id

    update b set b.col=a.col from b Left Join a on b.id = a.id
    查询迟到的人员信息:(不太好的方法)

    select U.user_name,D.date ,datename(weekday,D.date) from diary D,xloa_user U where D.user_id=U.user_id and

D.namespace='05info' and D.date >= '2008-8-1' and datename(weekday,D.date)<>'星期六' and

(convert(varchar(10),D.date,108) like '08%' or convert(varchar(10),D.date,108) like '09%')
order by U.user_id,D.date desc

    检查收件来源:

    select substring(email_from,charindex('@',email_from)+1,len(email_from)) as ff,count(email_from)

as cc from xloa_email
where email_to like '%chinamark.net' and date >'2009-4-27'

group by substring(email_from,charindex('@',email_from)+1,len(email_from))

order by cc desc

    评论转移 相关:

    select * from xloa_web_site where namespace='00'

INSERT INTO XLOA_WEB_COMMENT
(COMMENT_ID, COMMENT, NICK_NAME, [DATE], WEB_CONTENT_ID,ipaddr, NAMESPACE)
SELECT SUBSTRING(CAST(NEWID() AS varchar(255)), 25, 37) AS Expr1, a.Content,
substring(a.UserName,0,10), a.UpdateTime, b.WEB_CONTENT_ID,userip, 'chinamark.net' AS Expr2
FROM Tbl_Comment a INNER JOIN
XLOA_WEB_CONTENT b ON a.ArticleId = b.RAW_ID and b.WEB_SITE_ID = 'lchslmv2gtkm'

update xloa_web_comment set title=substring(comment,0,125) where title is null

    批量添加空间里允许的功能菜单。

update xloa_space set func_str= cast(func_str as varchar(5000))++',2001' where func_str not like '%,2001'

    替换email中email_to等字段的"<"为"<";">"为">":

UPDATE XLOA_EMAIL
SET EMAIL_FROM = REPLACE(EMAIL_FROM, '>', '>'),

EMAIL_TO = REPLACE(CAST(EMAIL_TO AS varchar(5000)), '>', '>')