SQL Server中用APPLY作成max(m, n)而非m*n的表连接效果

众所周知，JOIN只能造成m*n的表连接效果。但有的时候，我需要的仅仅是将两个表列举在一起，即造成max(m,n)的效果。

举个例子：有两张表要连接在一起，表A的结构如下图所示：
表B的结构如下图所示：
现在，我们想要将两张表连接成如下图所示的效果：
也就是，我们只想先按照合同号，找出两张表中同一个合同的数据，然后将两张表的列合并起来。表A列在左边，表B列在右边。这就是max(m,n)的效果，m是表A的行数，n是表B的行数。
如果我们用JOIN连接的话，就会是如下图的效果：

我首先想，如果只有一个合同的数据，那我怎么能并列两张表，我想到了用行号，SQL Server中提供了内置函数row_number()为查询结果提供行号，这样我们就可以在CONTRACT_ID相同的情况下，用行号进行连接了。SQL代码如下：

ALTER FUNCTION dbo.getOneContractDelayInterestReport(@contract_id int)RETURNS TABLEASRETURN(select ABC.*, D.RECEIVE_DATE as 实收逾期利息日, D.ActualPayDelayInterest as 实收逾期利息额from (select row_number() over (order by isnull(Q.CONTRACT_ID, A.CONTRACT_ID), isnull(Q.ISSUE_NO, A.ISSUE_NO),isnull(Q.SETTLEMENT_END_DATE, A.PLAN_DATE)) as RowNo,        isnull(Q.CONTRACT_ID, A.CONTRACT_ID) as CONTRACT_ID,        isnull(Q.ISSUE_NO, A.ISSUE_NO) as ISSUE_NO,         A.PLAN_DATE as 应收款日,         A.PLAN_AMOUNT as 应收款额,        Q.RECEIVE_DATE as 实收款日,        Q.RECEIVE_AMOUNT as 实收款额,        Q.SETTLEMENT_START_DATE as 计息开始日,        Q.SETTLEMENT_END_DATE as 计息结束日,        Q.COMPUTED_AMOUNT as 计算基准额,        Q.SettleDays as 计息天数,        Q.OVERDUE_AMOUNT as 逾期利息,        Q.ADJUST_AMOUNT as 减免逾期利息    from (select * from dbo.SETTLEMENT_PLAN_SCCMF where CONTRACT_ID = @contract_id) A    full join (select C.CONTRACT_ID, C.ISSUE_NO,        B.RECEIVE_DATE, B.RECEIVE_AMOUNT,        C.SETTLEMENT_START_DATE, C.SETTLEMENT_END_DATE,        C.COMPUTED_AMOUNT, DATEDIFF(day, C.SETTLEMENT_START_DATE, C.SETTLEMENT_END_DATE) as SettleDays,        C.OVERDUE_AMOUNT, C.ADJUST_AMOUNT    from (select * from dbo.SETTLEMENT_ACTUAL_SCCMF where CONTRACT_ID = @contract_id) B    right join (select * from dbo.SETTLEMENT_PLAN_SCCMF_OVERDUE where CONTRACT_ID = @contract_id) C    on B.CONTRACT_ID = C.CONTRACT_ID    and B.ISSUE_NO = C.ISSUE_NO    and  B.RECEIVE_DATE = C.SETTLEMENT_END_DATE) Q    on A.CONTRACT_ID = Q.CONTRACT_ID    and A.ISSUE_NO = Q.ISSUE_NO    and A.PLAN_DATE = Q.SETTLEMENT_START_DATE) ABCfull join(select row_number() over (order by CONTRACT_ID, RECEIVE_DATE) as RowNo, CONTRACT_ID, RECEIVE_DATE, sum(RECEIVE_AMOUNT) as ActualPayDelayInterest    from dbo.SETTLEMENT_ACTUAL_SCCMF_OVERDUE    where CONTRACT_ID = @contract_id     group by CONTRACT_ID, RECEIVE_DATE) Don ABC.RowNo = D.RowNo);GO

在此，我将contract_id作为参数写了一个函数，这可满足一个合同情况下的连接，从倒数第三行的join条件可以看到我用行号作为连接条件。此一个合同的情况下的连接效果如下图所示，假设我们连接1006合同：

接着就是APPLY的用武之地了。先看微软上APPLY的一个例子：
这里有一个函数fn_getsubtree()，它以Departments表的deptmgrid（部门经理ID）为参数，返回该部门的所有成员。例如HR部门的部门经理的ID是2，即Andrew，接着找Andrew的下属们，找到了Steven和Michael，他们的经理都是Andrew。功能说完。

这里可以明晰看到Apply的作用，它把左边表（Departments表）的每一行依次作为输入，放到右边的函数中处理，假设函数返回m行，那么就输出m行，左边的那一行输入则重复输出m次。。。如此，如果左表有n行输入，则最终可产生n*m行输出。这正是我们所需的功能！当合并两表时，一个合同我们可以用行号连接，那么多个合同时，我们只需要将所有的合同号作为APPLY左侧的输入，然后就能得到我们所需的所有的连接后的合同的输出。

代码如下（调用在上面写的函数）：

ALTER PROCEDURE [dbo].[SP_CLM1701_OVERDUE_INTEREST_REPORT@AGENT_ID nvarchar(6),@DELIVERY_DATE_LEFT nvarchar(10),@DELIVERY_DATE_RIGHT nvarchar(10),@CONTRACT_NO nvarchar(50),@CLOSING_DATE nvarchar(10)ASdeclare @sql nvarchar(max)declare @whereForContract nvarchar(max)set @whereForContract = ' where CONTRACT_TYPE = ''FS'' and CONTRACT_STATUS = ''3'''if(@CONTRACT_NO <> '')beginset @whereForContract += ' and CONTRACT_NO = '''+@CONTRACT_NO+''''endif(@AGENT_ID <> '')beginset @whereForContract += ' and AGENT_ID = '''+@AGENT_ID+''''endif(@DELIVERY_DATE_LEFT <>'')beginset @whereForContract += ' and DELIVERY_DATE >= '''+@DELIVERY_DATE_LEFT+''''endif(@DELIVERY_DATE_RIGHT <>'')beginset @whereForContract += ' and DELIVERY_DATE <= '''+@DELIVERY_DATE_RIGHT+''''endset @sql = 'select A.CONTRACT_NO, A.MACHINE_TYPE, A.MACHINE_NO, A.END_USER_ID, A.CUSTOMER_NAME, A.AGENT_ID, A.COMPANY_NAME, A.DELIVERY_DATE, A.TOTAL_AMOUNT, B.*from(select C.*, MST_CUSTOMER.CUSTOMER_NAME, MST_COMPANY.COMPANY_NAMEfrom (select * from dbo.MACHINE_CONTRACT_SCCMF ' +@whereForContract+ ') C, dbo.MST_CUSTOMER, dbo.MST_COMPANYwhere C.END_USER_ID = MST_CUSTOMER.CUSTOMER_IDand C.AGENT_ID = MST_COMPANY.COMPANY_ID) Across apply getOneContractDelayInterestReport(A.CONTRACT_ID,'''+@CLOSING_DATE+''') AS B'exec (@sql)

以上存储过程所用的函数与上面所写参数略有不同，因为最终还多了一个参数作为条件，不过不影响解释。以上@sql最末可以看到我们将A作为Apply的输入。

最终调用该存储过程即可：

DECLARE @return_value intEXEC    @return_value = [dbo].[SP_CLM1701_OVERDUE_INTEREST_REPORT]        @AGENT_ID = NULL,        @DELIVERY_DATE_LEFT = N'2016/05/30',        @DELIVERY_DATE_RIGHT = N'2016/06/01',        @CONTRACT_NO = NULL,        @CLOSING_DATE = N'2016/06/05'SELECT  'Return Value' = @return_valueGO

最终效果已贴在上面。

---