如果我理解正确的话，你可以使用 date_diff() 以及 date_add() :

select t.*,
       (case when payment_type = 'yearly'
             then date_add(date_of_first_transaction,
                           interval (1 + date_diff(current_date, date_of_first_transaction, year)) year)
             when payment_type = 'monthly'
             then date_add(date_of_first_transaction,
                           interval (1 + date_diff(current_date, date_of_first_transaction, month)) month)
             when payment_type = 'weekly'
             then date_add(date_of_first_transaction,
                           interval (1 + date_diff(current_date, date_of_first_transaction, week)) week)
             when payment_type = 'daily'
             then date_add(date_of_first_transaction,
                           interval (1 + date_diff(current_date, date_of_first_transaction, day)) day)
        end)     
from t;

注意：一定要测试一下。这可能不是你想要的，因为 date_diff() 返回两个值之间的日期边界数。