时间戳

首页 > 解决方案 > 如何使用 Microsoft SQL Server 创建特定的编号规则？

sql - 如何使用 Microsoft SQL Server 创建特定的编号规则？

问题描述

我已经尝试了几个小时来实现以下目标，但没有成功（请参阅所需的结果）。

我试图从下面的代码开始，但后来被难住了。

select c.LOAN
,year(c.ASOF_DATE) as [PAYMENT_YEAR],DATEPART(QUARTER,c.ASOF_DATE) as [PAYMENT_QUARTER]
,sum(c.PRIN+c.INTEREST) as [PAYMENT]
,(row_number() over(partition by c.loan order by c.loan,year(c.ASOF_DATE),DATEPART(QUARTER,c.ASOF_DATE))% 5) - 1
from CASHFLOW c
join LOANTAB l on
    l.LOAN = c.LOAN
where 
c.ASOF_DATE > '2018-06-30'
and not(l.LOAN_KIND like 'sfp%')
GROUP BY c.LOAN,year(c.ASOF_DATE),DATEPART(QUARTER,c.ASOF_DATE)

期望的结果

贷款 | 付款 | 年
3750000 | 1000 | 0
3750000 | 1000 | 1.1
3750000 | 1000 | 1.2
3750000 | 1000 | 1.3
3750000 | 1000 | 1.4
3750000 | 10000 | 2.1

3766310 | 204009 | 0
3766310 | 204009 | 1.1
3766310 | 204009 | 1.2
3766310 | 204009 | 1.3
3766310 | 204009 | 1.4
3766310 | 204009 | 2.1
3766310 | 204009 | 2.2
3766310 | 204009 | 2.3
3766310 | 204009 | 2.4
3766310 | 204009 | 3.1
3766310 | 204009 | 3.2
3766310 | 204009 | 3.3
3766310 | 204009 | 3.4
3766310 | 204009 | 4.1
3766310 | 204009 | 4.2
3766310 | 204009 | 4.3
3766310 | 204009 | 4.4
3766310 | 204009 | 5.1
3766310 | 204009 | 5.2
3766310 | 204009 | 5.3
3766310 | 204009 | 5.4
3766310 | 67946.34 | 6.1

标签： sqlsequence

解决方案

我能够就我的问题获得帮助。结果如下：

if OBJECT_ID('tempdb..#base' ,'u') is not null drop table #base
select c.LOAN
,year(c.ASOF_DATE) as [PAYMENT_YEAR],DATEPART(QUARTER,c.ASOF_DATE) as [PAYMENT_QUARTER]
,sum(c.PRIN+c.INTEREST) as [PAYMENT]
,(row_number() over(partition by c.loan order by c.loan,year(c.ASOF_DATE),DATEPART(QUARTER,c.ASOF_DATE))) as [ROWONE]
into #base
from CASHFLOW c
join LOANMAST l on
    l.LOAN = c.LOAN
where 
c.ASOF_DATE > '2018-06-30'
and not(l.LOAN_KIND like 'sfp%')
GROUP BY c.LOAN,year(c.ASOF_DATE),DATEPART(QUARTER,c.ASOF_DATE)


declare @LOAN varchar(20),@PAYMENT decimal(16,2),@rowone varchar(50)
declare @i int, @j int, @k int, @ploan varchar(50)
declare @t table (i int, j int,LOAN varchar(50),PAYMENT decimal(16,2),ROWONE varchar(50))
declare c cursor for select LOAN,PAYMENT,ROWONE from #base 
    --where loan in ('3715042','3716254','3722741','3726310') 
    order by 1, rowone
open c
fetch c into @LOAN,@PAYMENT,@rowone
select @i=0,@j=0,@ploan=''
while @@FETCH_STATUS=0
begin
    if @loan<>@ploan begin select @ploan=@loan,@i=1,@j=1 end
    if @i=1 and @j=1
        insert into @t 
            select 0,0,@LOAN,@PAYMENT,@rowone
    insert into @t 
        select @i,@j,@LOAN,@PAYMENT,@rowone
    select @i=@i+1
    if @i>4 and @loan=@ploan begin select @j=@j+1,@i=1 end
    fetch c into @LOAN,@PAYMENT,@rowone
end
close c
deallocate c
select (case when convert(varchar,j)+'.'+convert(varchar,i)='0.0' then '0' else convert(varchar,j)+'.'+convert(varchar,i) end) CUSTOM,loan as [LOAN],payment as [PAYMENT] from @t order by 2,1

drop table #base

推荐阅读