SqlServer之apply

 

    SELECT 
            t1.ApplyOrderNo ,
            CreateBy ,
            CreateTime ,
            Matters ,
            TravelCode ,
            StartDate ,
            EndDate ,
            dbo.F_GetApplyOrderPsgStr(t1.ApplyOrderNo) AS TripPsgs ,
            t1.ApproveStatus,
            t1.Isdel,
            t1.UserCode,
            t1.TravelName,
            t3.UserName,
            t5.IsGenerate,
            t2.ApproveUserID,
            t4.ApproveStatus AS FinanceApprovalStatus,
            t2.IsFinanceAuditor,
            t6.PKID AS NoApprovalID,
            ISNULL(t4.IsNeedAirTicketFinanceApproval,0) AS IsNeedAirTicketFinanceApproval
    FROM    [YeeSkyGo].[dbo].[T_TripApplyOrder] t1
            --关联审批人表，用来获取财务审批人员ID
            LEFT JOIN [YeeSkyGo].[dbo].[T_TripApplyOrder_Auditors] t2 ON t2.ApplyOrderNo=t1.ApplyOrderNo AND t2.IsFinanceAuditor = 1
            --关联用户表，用来获取创建人姓名
            LEFT JOIN [YeeSkyGo].[dbo].[T_User_User] t3 ON t1.CreateBy=t3.LoginId AND t3.IsEnabled = 1 AND t3.ISDel = 0
            --关联出差申请单财务人员配置表，用来获取财务人员审批状态
            LEFT JOIN [YeeSkyGo].[dbo].[T_TripApplyOrder_FinanceApproval] t4 ON t1.ApplyOrderNo=t4.ApplyOrderNo
            --关联报销表，用来获取此出差单是否已生成报销单(已经和郑子宣确认，不满足条件的不再显示)
            LEFT JOIN [YeeSkyGo].[dbo].[T_ReimburseApply] t5 ON t5.ApplyOrderNo = t1.ApplyOrderNo AND t5.Isdel = 0   AND ISNULL(t5.IsDisabled,0)=0
            --关联关联审批人表，用来获取财务审批之前是否存在待审批的审批人，如果存在则能进行财务审批
            --OUTER APPLY (SELECT  TOP 1 *  FROM [YeeSkyGo].[dbo].[T_TripApplyOrder_Auditors] WHERE ApproveStatus=0 AND ApplyOrderNo=t1.ApplyOrderNo AND ApprovePriority<t2.ApprovePriority ) AS t6 
            cross APPLY (SELECT  TOP 1 *  FROM [YeeSkyGo].[dbo].[T_TripApplyOrder_Auditors] WHERE ApproveStatus=0 AND ApplyOrderNo=t1.ApplyOrderNo AND ApprovePriority<t2.ApprovePriority ) AS t6 
            --left join (SELECT  TOP 1 *  FROM [YeeSkyGo].[dbo].[T_TripApplyOrder_Auditors] WHERE ApproveStatus=0 AND ApplyOrderNo=t1.ApplyOrderNo AND ApprovePriority<t2.ApprovePriority ) AS t6 
            WHERE   ( t1.IsDisabled != 1 OR t1.IsDisabled IS NULL)  and (t1.ApplyOrderNo='CC1000002017091919030585' or t1.ApplyOrderNo='CC1000002017091919030597' or t1.ApplyOrderNo='CC1000002017091919030598')

apply 允许我们将前面结果集每一行的数据作为参数，传递到后面的表达式，后面的表达式可以是一个表值函数，或者select结果集。