SQL Server分组排序提取数据的代码举例

发布时间:2020-09-02编辑:脚本学堂
本文介绍下,在sql server中进行分组排序,并提取数据的一例代码。有需要的朋友,参考下吧。

在sql Server中,分组排序之后,提取每组的第N行数据。

首先,以select 的方式来引出其实际操作步骤。
例子:
 

复制代码 代码示例:
select
    A.[DocNo] as [docno],  
    A1.[Item_ItemCode] as [itemcode],  
    A2.[LineNum] as [linenum],  
    A2.[ARBillLine] as [arbillLine], 
    A2.[Maturity] as [maturity],  
    A2.[AROCMoney_TotalMoney] as [totalmoney

例子:
 

复制代码 代码示例:
select
    A.[DocNo] as [docno],  
    A1.[Item_ItemCode] as [itemcode],  
    A2.[LineNum] as [linenum],  
    A2.[ARBillLine] as [arbillLine], 
    A2.[Maturity] as [maturity],  
    A2.[AROCMoney_TotalMoney] as [totalmoney],  
    A2.[AROCMoneyBalance_TotalMoney] as [totalBalanceMoney]  
    into #tempShouhuoFenqi 
    from AR_ARBillHead as A  
    left join [AR_ARBillLine] as A1 on (A.[ID] = A1.[ARBillHead])  
    left join [AR_ARInstalment] as A2 on (A1.[ID] = A2.[ARBillLine]) 
    group by A.DocNo,A1.Item_ItemCode,A2.LineNum,A2.ARBillLine,A2.Maturity,A2.AROCMoney_TotalMoney,A2.AROCMoneyBalance_TotalMoney 
    -- select * from #tempShouhuoFenqi 
    -- drop table #tempShouhuoFenqi

合同起始日期:第期的日期
 

复制代码 代码示例:
select docno,arbillline,maturity1  
    into #tempMaturity1 from  
    ( 
    select docno,arbillline 
    ,maturity as maturity1, 
    row_number() over  
    (partition by docno,arbillline 

按docno,arbillline分组
 

复制代码 代码示例:
order by maturity asc) as rowno --按maturity排序
    from #tempShouhuoFenqi ) x
where x.rowno=2 --取SQL Server分组排序后的第行
    ---- select * from #tempMaturity1  
    ---- drop table #tempMaturity1