存储过程中的年计算 [英] Years calculations inside stored procedure

问题描述

与此相关的我先前的问题已在此处得到解答： LINK

My previous question, which is connected to this one, was already answered here: LINK

此查询对我来说非常有效，但是我需要做一些更改。
不必将每个月都显示为债务，而只需要显示一年即可。

This query was working perfectly for me, however I need to do some changes. Instead of showing every month as debt I would need to show only a year as debt.

例如：
我需要检查是否一年中有任何付款，如果没有，那么我需要在表格中显示该年。
在上一个输出中，我有类似以下内容：

For example: I would need to check if there were any payments in the year, if there are none then I need to show that year in my table. In my previous output I had something like this:

Member's code  |  Debt ( Months )        
555-12         |     2-2013        
555-12         |     4-2013       

现在我需要这个：

Member's code  |  Debt ( Years )
555-12         |     2012

这就是我尝试过的方法：小提琴
在此示例中，您可以看到我有一个不应该存在的2013年。

This is what I've tried : Fiddle In this example you can see that I have a year 2013 which shouldn't be there.

推荐答案

以下查询返回自第一个成员资格以来没有匹配的年份：

The following query returns the years since the first membership with no matches:

select m.*, (year(Member_since) + n.n) as YearNotPaid
from members m join
     numbers n
     on year(Member_since) + n.n <= year(now()) left outer join
     membership ms
     on ms.code = m.code and
         year(Member_since) + n.n = year(ms.paid)
where ms.id is null;

您可以包含 AND m.Code ='555-12'，如果您只想要一位成员。

You can include AND m.Code = '555-12' if you just want it for one member.

这篇关于存储过程中的年计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！