表1為欠款的月份
表2為欠款金額
結果要求按欠款金額計算是哪個月欠款的，取最近12個月的，不是最近12月的歸納為歷史欠款。
如欠款為負數不需要算
這個真有點難
uj5u.com熱心網友回復：
--建表
create table #T
(
id int,
czdate date,
amount int
)
insert into #T values(1,'2019-06-01',7000)
insert into #T values(1,'2019-07-02',8000)
insert into #T values(1,'2019-08-11',6000)
insert into #T values(2,'2019-06-05',6000)
insert into #T values(2,'2019-05-06',5000)
insert into #T values(3,'2019-05-06',5000)
insert into #T values(3,'2019-06-07',1000)
--建表
create table #T2
(
id int,
amount int
)
insert into #T2 values(1,26000),(2,15000),(3,-5000)
--查詢
select id,amount,[01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12],欠款 from
(
select c.id,c.amount,Right(a.ym,2) as ym,case when c.amount<=0 then null else b.amount end as val,case when c.amount>b.ssum then c.amount-b.ssum else null end as 欠款 from
(
select number,CONVERT(varchar(6),DATEADD(month,-1*number,GETDATE()),112) as ym from master.dbo.spt_values where type='P' and number between 0 and 5
union
select number,CONVERT(varchar(6),DATEADD(month,number,GETDATE()),112) as ym from master.dbo.spt_values where type='P' and number between 0 and 6
) A
left join
(
select *,CONVERT(varchar(6),czdate,112) as ym,SUM(amount) over(partition by id) as ssum from #T
) b on a.ym=b.ym
right join #T2 c on b.id=c.id
) D
pivot
(
sum(val)
for ym in ([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12])
)P
uj5u.com熱心網友回復：
--建表
create table #T
(
id int,
czdate date,
amount int
)
insert into #T values(1,'2019-06-01',7000)
insert into #T values(1,'2019-07-02',8000)
insert into #T values(1,'2019-08-11',6000)
insert into #T values(2,'2019-06-05',6000)
insert into #T values(2,'2019-05-06',5000)
insert into #T values(3,'2019-05-06',5000)
insert into #T values(3,'2019-06-07',1000)
--建表
create table #T2
(
id int,
amount int
)
insert into #T2 values(1,26000),(2,15000),(3,-5000)
--取最近12個月(上取5個,下取6個,1個當月)
select number,CONVERT(varchar(6),DATEADD(month,-1*number,GETDATE()),112) as ym into #tmp from master.dbo.spt_values where type='P' and number between 0 and 5
union
select number,CONVERT(varchar(6),DATEADD(month,number,GETDATE()),112) as ym from master.dbo.spt_values where type='P' and number between 0 and 6
--準備動態行列轉換
declare @sql varchar(max)=''
declare @filed varchar(max)=''
declare @pivot varchar(max)=''
select
@filed=@filed+','+QUOTENAME(RIGHT(ym,2))+' as ' +QUOTENAME(RIGHT(ym,2) +'月'),
@pivot=@pivot+','+QUOTENAME(RIGHT(ym,2))
from #tmp order by ym
--查詢
set @sql='
select id,amount'+@filed+',欠款 from
(
select c.id,c.amount,Right(a.ym,2) as ym,case when c.amount<=0 then null else b.amount end as val,case when c.amount>b.ssum then c.amount-b.ssum else null end as 欠款 from
#tmp A
left join
(
select *,CONVERT(varchar(6),czdate,112) as ym,SUM(amount) over(partition by id) as ssum from #T
) b on a.ym=b.ym
right join #T2 c on b.id=c.id
) D
pivot
(
sum(val)
for ym in ('+stuff(@pivot,1,1,'')+')
)P'
exec (@sql)
drop table #tmp
drop table #T
drop table #T2
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/149587.html
標籤：疑難問題
上一篇：mysql每天晚上2點同步指定表資料到另一服務器資料庫中
下一篇：Abaqus問題求解