SQL - 連兩週千張大戶增幅超過1%

select a.StockNo ,a.DATE '本週' , a.Rate '本週持股比' , b.Rate '上週持股比',
round((cast(a.Rate as float) - cast(b.Rate as float)),2) '增幅%'
from
(select *,id0=row_number()over(order by StockNo , Date) from Stockholder where Item = '1000001' )a,  --本周
(select *,id0=row_number()over(order by StockNo , Date) from Stockholder where Item = '1000001' )b,  --前一周
(select *,id0=row_number()over(order by StockNo , Date) from Stockholder where Item = '1000001' )c  --前兩周
where 1=1
and a.id0=b.id0+1
and b.id0=c.id0+1
and a.StockNo = b.StockNo
and a.StockNo = c.StockNo
and a.Date = '20150821'
and round((cast(a.Rate as float) - cast(b.Rate as float)),2) > 1
and round((cast(b.Rate as float) - cast(c.Rate as float)),2) > 1