sql - 案例声明中的案例

Question

我必须从名为 section 的表中获取教师批准日期。如果该表没有日期(空)，那么我必须从提供表中获取日期，即使该表中没有我要查找的内容，也可以从 Term 表中获取。

如果这三个都为空，那么我需要以与 instructorapprovaldate 相同的方式获取 absoluteExpireDate。

如果 absoluteExpireDate 在所有三个表中也为 null，那么我需要以类似的方式获取 WaitList 日期。

我如何创建一个 case 语句来处理它？<​​/p>

到目前为止，这是我所拥有的:

SELECT  @dInstructApprDate = case when a.InstructorApprovalDate is null  
        then
             select @dInstructApprDate = instructorapprovaldate 
             from SSS_OfferingAcademicPeriods   
             where SSS_OfferingRegPeriods.SSS_OfferingsID = @lSSS_OfferingsID

我不确定为什么它不喜欢我在“THEN”中使用 select 语句

如有任何帮助，我们将不胜感激。

这是到目前为止我得到的函数:

 CREATE FUNCTION [dbo].[SSS_GetInstructorApprovalDate](@lSSS_SectionsID INT)      
 RETURNS VARCHAR(20)    
 AS      

          BEGIN      

  DECLARE   
 @dInstructApprDate DATETIME,  
 @dAddDropDate  DATETIME,  
 @lTemp    INT,  
 @lSSS_OfferingsID INT,  
 @lSSS_TermsID  INT  

 SET @lTemp = 0  

 SELECT   
 @lTemp = 1  
 WHERE   
 EXISTS (SELECT 1 FROM SSS_SectionAcademicPeriods WITH (NOLOCK) WHERE SSS_SectionsID = @lSSS_SectionsID)  

--Fetch from section level, if present - Begin  
 IF @lTemp = 1  
BEGIN  

  SELECT  @dInstructApprDate = case when a.InstructorApprovalDate is null  
  then   
   (select instructorapprovaldate from SSS_OfferingAcademicPeriods where SSS_OfferingRegPeriods.SSS_OfferingsID = @lSSS_OfferingsID)

  else   
 InstructorApprovalDate  
end   
  FROM  
   SSS_SectionAcademicPeriods a WITH (NOLOCK)  
  where   
   SSS_SectionsID = @lSSS_SectionsID

Answer 1

鉴于您正在检查 null，您可以使用 Coalesce:

select coalesce
(
      (select 1 a where 1=2) --returns null
    , (select 2 a where 2=3) --returns null
    , (select 3 a where 4=4) --returns result
    , 100 --default
) x

对我来说，这比 case 语句更清晰/更容易阅读，而且我怀疑它的性能也一样。

根据您的问题中包含的代码和描述，对您来说，这看起来类似于以下内容:

CREATE FUNCTION [dbo].[SSS_GetInstructorApprovalDate](@lSSS_SectionsID INT)      
RETURNS VARCHAR(20)    
AS      
BEGIN      

    DECLARE @dInstructApprDate  DATETIME 
    ,       @dAddDropDate       DATETIME
    ,       @lSSS_OfferingsID   INT
    ,       @lSSS_TermsID       INT  
    --,     @lTemp              INT = 0

    --I suspect you don't want this bit; but uncomment if it's required (i.e. if you only want a value when there's a matching record in the secion table, but the record's approval date's null
    --SELECT top 1 @lTemp = 1 
    --FROM SSS_SectionAcademicPeriods WITH (NOLOCK) 
    --WHERE SSS_SectionsID = @lSSS_SectionsID

    --Fetch from section level, if present - Begin  
    --IF @lTemp = 1  
    --BEGIN  

        SELECT @dInstructApprDate = coalesce
        (
            (
                SELECT InstructorApprovalDate       
                FROM SSS_SectionAcademicPeriods with(nolock)
                where SSS_SectionsID = @lSSS_SectionsID
            )
            ,
            (
                select InstructorApprovalDate 
                from SSS_OfferingAcademicPeriods 
                where SSS_OfferingsID = @lSSS_OfferingsID
            )
            ,
            (
                select InstructorApprovalDate 
                from SSS_TermsAcademicPeriods 
                where SSS_OfferingsID = @lSSS_TermsID
            )

            ,
            (
                SELECT AbsoluteExpireDate       
                FROM SSS_SectionAcademicPeriods with(nolock)
                where SSS_SectionsID = @lSSS_SectionsID
            )
            ,
            (
                select AbsoluteExpireDate        
                from SSS_OfferingAcademicPeriods 
                where SSS_OfferingsID = @lSSS_OfferingsID
            )
            ,
            (
                select AbsoluteExpireDate        
                from SSS_TermsAcademicPeriods 
                where SSS_OfferingsID = @lSSS_TermsID
            )
            ,
            (
                SELECT WaitListDate     
                FROM SSS_SectionAcademicPeriods with(nolock)
                where SSS_SectionsID = @lSSS_SectionsID
            )
            ,
            (
                select WaitListDate      
                from SSS_OfferingAcademicPeriods 
                where SSS_OfferingsID = @lSSS_OfferingsID
            )
            ,
            (
                select WaitListDate      
                from SSS_TermsAcademicPeriods 
                where SSS_OfferingsID = @lSSS_TermsID
            )

        )
    --END
    return cast(@dInstructApprDate as varchar(20)) --probably 

END

注意:根据每个查询花费的时间，您可能希望采用略有不同的方法。这是一个替代方案/让我知道它如何适合:

CREATE FUNCTION [dbo].[SSS_GetInstructorApprovalDate](@lSSS_SectionsID INT)      
RETURNS VARCHAR(20)    
AS      
BEGIN      

    DECLARE @dInstructApprDate  DATETIME 
    ,       @dInstructApprDate2 DATETIME 
    ,       @dInstructApprDate3 DATETIME 
    ,       @dAddDropDate       DATETIME
    ,       @lSSS_OfferingsID   INT
    ,       @lSSS_TermsID       INT  
    --,     @lTemp              INT = 0

    --I suspect you don't want this bit; but uncomment if it's required (i.e. if you only want a value when there's a matching record in the secion table, but the record's approval date's null
    --SELECT top 1 @lTemp = 1 
    --FROM SSS_SectionAcademicPeriods WITH (NOLOCK) 
    --WHERE SSS_SectionsID = @lSSS_SectionsID

    --Fetch from section level, if present - Begin  
    --IF @lTemp = 1  
    --BEGIN  


        SELECT  @dInstructApprDate  = InstructorApprovalDate        
        ,       @dInstructApprDate2 = AbsoluteExpireDate
        ,       @dInstructApprDate3 = WaitListDate
        FROM SSS_SectionAcademicPeriods with(nolock)
        where SSS_SectionsID = @lSSS_SectionsID

        if @dInstructApprDate is null 
            select  @dInstructApprDate  = InstructorApprovalDate        
            ,       @dInstructApprDate2 = isnull(@dInstructApprDate2, AbsoluteExpireDate)
            ,       @dInstructApprDate3 = isnull(@dInstructApprDate3, WaitListDate)
            from SSS_OfferingAcademicPeriods 
            where SSS_OfferingsID = @lSSS_OfferingsID

        if @dInstructApprDate is null 
            select  @dInstructApprDate  = InstructorApprovalDate    
            ,       @dInstructApprDate2 = isnull(@dInstructApprDate2, AbsoluteExpireDate)
            ,       @dInstructApprDate3 = isnull(@dInstructApprDate3, WaitListDate
            from SSS_TermsAcademicPeriods 
            where SSS_OfferingsID = @lSSS_TermsID


        set @dInstructApprDate = coalesce(@dInstructApprDate, @dInstructApprDate2, @dInstructApprDate3)

    --END
    return cast(@dInstructApprDate as varchar(20)) --probably 

END