Left Anti-Join

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view

Left Anti-Join


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)