T-SQL(1)

T-SQL:

     数据库：关系型数据库 MS-SQLSERVER

    学习摘要：

                     1. 两个资料表中PK是一对多，那么利用资料库规划的原则，将“一”资料表的主索引键复制到‘多’资料表成为Foreign Key。（因为主索引键具不重复性）

                    2. 在连接上面这种 一对多 资料表的时候，要用到join,

系统默认为内连接：inner join

如果需要还有：outer join. 和self join

Outer join 又分为：left outer join ,right outer join ,full outer join

 工作摘要：下午写的两个函数：

 说明：在MS2000自定义函数中不能直接使用GETDATE()公用函数，可以从外面传进来，也可以改变sp_serveroption

CREATE  FUNCTION dbo.getProjectPhaseByProjectNo(@projectNo AS int,@TODAY AS DATETIME) RETURNS  int  AS BEGIN
DECLARE @projectPhase AS int

SELECT @projectPhase =pt.projectPhase
  
FROM project_iteration pt
where pt.actualStartDate <= @TODAY and @TODAY <= pt.actualEndDate and pt.projectNo=@projectNo

return @projectPhase
END

 

CREATE FUNCTION dbo.getProjectStatusByProjectNo(@projectNo AS INT,@TODAY AS DATETIME) RETURNS varchar(12)
AS BEGIN
DECLARE @PROJECTSTATUS AS varchar(12)
IF(
SELECT pt.projectPhase  
FROM project_iteration pt
WHERE pt.projectNo=@projectNo and  @TODAY<(
select
min(pr.actualStartDate)
from project_iteration pr
)

)!=NULL
BEGIN
 SET @PROJECTSTATUS='未開始'
END
ELSE IF(
SELECT pt.projectPhase  
FROM project_iteration pt
WHERE pt.projectNo=@projectNo and  @TODAY>(
select
max(pr.actualEndDate)
from project_iteration pr
)
)!=NULL
BEGIN
 SET @PROJECTSTATUS='已結案'
END