租金表ContractTable,有7个字段,分别是:ContractNumber(合同号,主键,nvarchar)、City(所在城市,nvarchar),Road(所在路段,nvarchar),Address(门牌号,nvarchar),LeaseDateFrom(合同起始日,date),LeaseDateTo(合同终止日,date),TypeOfPayment(缴款方式,nvarchar)
-
City、Road、Address是联合使用的,用来确定具体的地址。
-
现在把同一个City+Road+Address的联合值作为一个门店的完整地址,因为每隔一段时间这个门店会重新签合同,TypeOfPayment(缴款方式)也可能变化。
-
(上一期的合同应该可以根据LeastDateTo(合同终止日)的先后来推断),下面是我求同一个门店最新合同的上一期的TypeOfPayment的代码,问题是“如何在此基础上把a表中的TypeOfPayment更新成b表中相对应的TypeOfPayment呢?”(也就是让最新合同的TypeOfPayment更新成上一期合同的TypeOfPayment)。第一次看见带With的更新,我还不会写,所以请给出具体代码!谢谢
;with f as
(
select px=row_number()over(partition by city,road,address order by leasedateto desc),* from contracttable
)
select
b.City+b.Road +b.Address as 地址 ,a.typeofpayment ,b.TypeOfPayment from
f a
left join
f b on a.City+a.Road +a.Address=b.City+b.Road +b.Address and a.px=b.px-1
where a.ContractNumber like '%new%' and a.ContractNumber like '%20122%'
-
City、Road、Address是联合使用的,用来确定具体的地址。
-
现在把同一个City+Road+Address的联合值作为一个门店的完整地址,因为每隔一段时间这个门店会重新签合同,TypeOfPayment(缴款方式)也可能变化。
-
(上一期的合同应该可以根据LeastDateTo(合同终止日)的先后来推断),下面是我求同一个门店最新合同的上一期的TypeOfPayment的代码,问题是“如何在此基础上把a表中的TypeOfPayment更新成b表中相对应的TypeOfPayment呢?”(也就是让最新合同的TypeOfPayment更新成上一期合同的TypeOfPayment)。第一次看见带With的更新,我还不会写,所以请给出具体代码!谢谢
;with f as
(
select px=row_number()over(partition by city,road,address order by leasedateto desc),* from contracttable
)
select
b.City+b.Road +b.Address as 地址 ,a.typeofpayment ,b.TypeOfPayment from
f a
left join
f b on a.City+a.Road +a.Address=b.City+b.Road +b.Address and a.px=b.px-1
where a.ContractNumber like '%new%' and a.ContractNumber like '%20122%'
(
select px=row_number()over(partition by city,road,address order by leasedateto desc),* from contracttable
)
update a set a.typeofpayment =b.TypeOfPayment
from
f a
left join
f b on a.City+a.Road +a.Address=b.City+b.Road +b.Address and a.px=b.px-1
where a.ContractNumber like '%new%' and a.ContractNumber like '%20122%'
isnull((select top 1 TypeOfPayment from ContractTable where City = m.City and Road = m.Road and Address = m.Address and LeaseDateTo<m.LeaseDateTo order by LeaseDateTo desc),t.TypeOfPayment)
from (select City,Road,Address,max(LeaseDateTo) LeaseDateTo from ContractTable group by City,Road,Address) m , ContractTable t
where t.City = m.City and t.Road = m.Road and t.Address = m.Address and t.LeaseDateTo=m.LeaseDateTo
isnull((select top 1 TypeOfPayment from ContractTable where City = m.City and Road = m.Road and Address = m.Address and LeaseDateTo<m.LeaseDateTo and ContractNumber like '%new%' and ContractNumber like '%20122%' order by LeaseDateTo desc),t.TypeOfPayment)
from (select City,Road,Address,max(LeaseDateTo) LeaseDateTo from ContractTable where ContractNumber like '%new%' and ContractNumber like '%20122%' group by City,Road,Address) m , ContractTable t
where t.City = m.City and t.Road = m.Road and t.Address = m.Address and t.LeaseDateTo=m.LeaseDateTo and t.ContractNumber like '%new%' and t.ContractNumber like '%20122%'