当前位置: 数据库>sqlserver
SQL Server分组排序提取数据的代码举例
来源: 互联网 发布时间：2014-08-29
本文导语: 在sql Server中，分组排序之后，提取每组的第N行数据。 首先，以select 的方式来引出其实际操作步骤。 例子： 代码示例: select A.[DocNo] as [docno], A1.[Item_ItemCode] as [itemcode], A2.[LineNum] as [linenum], ...
在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
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
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
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
from #tempShouhuoFenqi ) x
where x.rowno=2 --取SQL Server分组排序后的第行
---- select * from #tempMaturity1
---- drop table #tempMaturity1
您可能感兴趣的文章:
本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术，将尽最大努力为读者提供更好的信息聚合和浏览方式。
本站(WWW.)站内文章除注明原创外，均为转载、整理或搜集自网络。欢迎任何形式的转载，转载请注明出处。
本站(WWW.)站内文章除注明原创外，均为转载、整理或搜集自网络。欢迎任何形式的转载，转载请注明出处。