Left Anti-Join

[ddwcg]

hi,

I want to calculate the amount of consumption relative to the user added in the previous year, but the result of the following sql calculation is incorrect.

The "appendTable" is a table register from a appendStream


select a.years,a.shopId,a.userId, a.amount
from (select years,shopId,userId,sum(amount) amount from appendTable group by years,shopId,userId)a
where not exists (select  years,shopId,userId from appendTable b where cast(a.years as int) =cast(b.years as int)-1 and a.shopId=b.shopId and a.userId=b.userId)
;

thanks