KissU常用的SQL脚本

☻下面的函数是将多行记录合并为一行记录以分号隔开的函数

CREATE FUNCTION FN_catString(@id uniqueidentifier)

RETURNS NVARCHAR(1000)

BEGIN

DECLARE @result VARCHAR(40)

SET @result=''

select @result=@result+';'+ISNULL(u.Name,'') //IsNull函数式判断是否为null,如果为null就用空值替换。

FROM INVEST_ProjectLead as pl

left outer join SEC_User as u

on u.Id=pl.ProjectLeaderID

where pl.DEPT_ROJECT_ID=@id

return(stuff(@result,1,1,'')) //stuff(@result,1,1,''）函数的意思是返回结果result ，但将结果的从第1位开始的1长度用‘’替换。

END

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

☻ 条件判断下的处理

SELECT #Level#=LEVEL_NO,#ProjectID#=PARENT_ID FROM INVEST_DEPTProjectInfo WHERE PROJECT_ID=#PARENT_ID#

IF #Level#=1

BEGIN

SELECT PROJECT_ID,APPLY_COMPANY_ID,APPLY_COMPANY_NAME FROM INVEST_DEPTProjectInfo

WHERE PARENT_ID=#PARENT_ID#

OR PROJECT_ID=#PARENT_ID#

END

ELSE

BEGIN

SELECT PROJECT_ID,APPLY_COMPANY_ID,APPLY_COMPANY_NAME FROM INVEST_DEPTProjectInfo

WHERE PARENT_ID=#ProjectID#

OR PROJECT_ID=#ProjectID#

END

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

☻KissU框架下的更新语句

<![CDATA[

update dbo.INVEST_DEPTProjectInfo

set DEPT_ROJECT_ID = #DEPT_ROJECT_ID#

{?,Status = #Status#}

{?,PROFESSION = #PROFESSION#}

{?,InvestAdmin = #InvestAdmin#}

{?,ISPROJECTVIEW = #ISPROJECTVIEW#}

{?,PROJECT_DEPT = #PROJECT_DEPT#}

,UPDATETIME=getdate()

,UPDATORID=#Env:User.Id#

where DEPT_ROJECT_ID = #DEPT_ROJECT_ID#

]]>

</command>

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

☻KissU框架下的判断是否存在存在就更新，不存在就插入语句

<![CDATA[

IF NOT EXISTS(

SELECT TOP 1 1 FROM INVEST_ProjectLead ipl ----注意：这里如果条件匹配上了就查出了1否则查出为空

WHERE ipl.DEPT_ROJECT_ID = #DEPT_ROJECT_ID# AND ipl.ProjectLeaderID = #ProjectLeaderID#

)

BEGIN

INSERT INTO dbo.INVEST_ProjectLead

(

ProjectLeadID,

DEPT_ROJECT_ID,

ProjectLeaderID,

CREATETIME,

CREATORID

)

SELECT NEWID(),

#DEPT_ROJECT_ID#,

#ProjectLeaderID#,

GETDATE(),

#Env:User.Id#

END

]]>

</command>

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

☻查询列中含查询语句

select T.TID as TID,U.UserID as UserID,U.Name as UserName,

Tr.FullDeptName as DeptName,U.MobilePhone as MobilePhone,U.Email as Email,

ApplyTime,

case when IsChecked = 1 then '是'

else '否'

end as IsPass,

case when Tr.UserType = 1 then '移动学员'

else '合作单位学员'

end as UserType,

case T.IsPassFirstCheck when 0 then '未处理' when 1 then '审核通过' when 2 then '审核未通过'

else '' end as IsPassFirstCheck,

(select U.Name FROM UserInfo U WHERE U.UserID = T.FirstCheckUserID) AS FirstCheckUser,

T.FirstCheckTime

from UserInfo U

join TrainApply T on U.UserID = T.UserID

join Trainees Tr on U.UserID = Tr.UserID

left Join Company P on U.DeptID = P.CompID

left join DeptInfo D on U.DeptID = D.DeptID

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

☻ 较为复杂的SQL查询（内含有子查询、case 表达式、和函数）

SELECT

di.DEPT_ROJECT_ID

, di.PRO_CODE

, di.PRO_NAME

, cp.ContractProjectID

, cp.PROJECT_ID

, cp.ContractInfoID

, cp.CT_NO

, cp.PROJECT_NAME

, cp.PROJECT_MONEY

, (select di.Text from dbo.SYS_DictionaryItems as di where di.id=cp.PROJECTCONTRACTCLASS) as PROJECTCONTRACTCLASS

, (CASE WHEN (cp.PROJECTCONTRACTCLASS IS NULL OR cp.PROJECTCONTRACTCLASS='') THEN '否' ELSE '是' END ) AS IsPCTCLASS

, cp.IS_COUNT_FRAME

, cp.STATUS

, cp.CreateTime

, cp.UPDATETIME

, cp.UPDATORID

, cp.CREATORID

, cp.COUNT_FRAMEACOUNT

, ci.CT_NAME

, ci.APPROVE_BEGIN_DATE

, ci.APPROVE_END_DATE

, ci.IS_FRAME_CT_NAME

, dbo.FN_catString(di.DEPT_ROJECT_ID) AS UName

FROM dbo.INVEST_DEPTProjectInfo AS di

INNER JOIN

dbo.CMS_ContractProject AS cp

ON di.PROJECT_ID=cp.PROJECT_ID

INNER JOIN

dbo.CMS_ContractInfo AS ci

ON cp. ContractInfoID=ci.ContractInfoID

WHERE 1=1

{? AND di.PRO_NAME Like '%'+#PRO_NAME#+'%' }

{? AND di.PRO_CODE Like '%'+#PRO_CODE#+'%' }

{? AND UName Like '%'+#UName#+'%'}

{? AND ci.CT_NO Like '%'+#CT_NO#+'%'}

{? AND ci.CT_NAME Like '%'+#CT_NAME#+'%'}

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

☻ 清除数据库日志文件的脚本语句

DBCC SQLPERF('logspace')

--1.清空日志

DUMP TRANSACTION IIMS WITH NO_LOG

--2.截断事务日志：

BACKUP LOG IIMS WITH NO_LOG

--3.收缩数据文件

DBCC SHRINKDATABASE(IIMS)

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

☻压缩数据库

dbcc shrinkdatabase(dbname)

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

☻日期的转换

<![CDATA[

SELECT

ActivityID

, ActivityTitle

, ActivityContent

, Participants

, CONVERT(varchar(100),ActivityBeginTime,23) AS ActivityBeginTime

, CONVERT(varchar(100),ActivityEndTime,23) AS ActivityEndTime

, CONVERT(varchar(100),SignUpBeginTime,23) AS SignUpBeginTime

, CONVERT(varchar(100),SignUpEndTime,23) AS SignUpEndTime

, Caps

, [Status] --0为未发布 1已发布

FROM dbo.Forum_Activity

WHERE 1=1

AND ActivityID = #ActivityID#

]]>

</command>

//删除试卷、题、及选项类的业务脚本

<![CDATA[

DELETE

FROM dbo.Forum_TopicItems

WHERE TopicID IN

(

SELECT TopicID

FROM dbo.Forum_SurveyTopic

WHERE SurveyID = #SurveyID#

)

DELETE

FROM dbo.Forum_SurveyTopic

WHERE SurveyID = #SurveyID#

DELETE FROM dbo.Forum_Survey

WHERE SurveyID = #SurveyID#

]]>

</command>

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

//统计的sql

<![CDATA[

SELECT

(SELECT PlateName FROM Forum_Plate WHERE PlateID=#PlateID#) AS PlateName

,fp.ThemeID

,ft.ThemeName

,ft.ThemeImg

,ft.Description

,COUNT(fp.PostsID) AS PostsNum

,SUM(CASE WHEN DATEDIFF(dd,fp.PublishTime,GETDATE()) = 0 THEN 1 ELSE 0 END) AS TodayPostsNum

,(SELECT TOP 1 Title FROM Forum_Posts AS fp1 WHERE fp1.ThemeID = fp.ThemeID ORDER BY PublishTime DESC) AS lastPostsTitle

,(SELECT TOP 1 PostsID FROM Forum_Posts AS fp1 WHERE fp1.ThemeID = fp.ThemeID ORDER BY PublishTime DESC) AS PostsID

,(SELECT TOP 1 PublishUserName FROM Forum_Posts AS fp1 WHERE fp1.ThemeID = fp.ThemeID ORDER BY PublishTime DESC) AS lastTitlePublishUserName

FROM Forum_Theme AS ft

INNER JOIN Forum_Posts AS fp

ON ft.ThemeID = fp.ThemeID

WHERE PlateID=#PlateID#

GROUP BY ft.ThemeName,ft.ThemeImg,ft.Description,fp.ThemeID

]]>

</command>

//插入包含当前用户信息及部门信息

<![CDATA[

INSERT INTO dbo.Forum_Posts

( PostsID ,

ThemeID ,

Title ,

Content ,

PublishUserID ,

PublishUserName ,

PublishDeptID ,

PublishDeptName ,

PublishTime

)

VALUES ( #PostsID# , -- PostsID - nvarchar(50)

#ThemeID# , -- ThemeID - nvarchar(50)

#Title# , -- Title - nvarchar(500)

#Content# , -- Content - nvarchar(max)

#Env:User.Id#, -- PublishUserID - nvarchar(50)

(SELECT Name FROM dbo.SEC_User WHERE Id = #Env:User.Id# ) , -- PublishUserName - nvarchar(50)

#Env:User.DeptId# , -- PublishDeptID - nvarchar(50)

(select top 1 Name FROM dbo.SEC_Organization WHERE Id =#Env:User.DeptId# ) , -- PublishDeptName - nvarchar(50)

GETDATE() -- PublishTime - datetime

)

]]>

</command>

posted @ 2013-11-26 22:39 weifb 阅读(228) 评论(0) 收藏举报

刷新页面返回顶部

weifb

KissU常用的SQL脚本

公告