SQL中的每年累计计数

Question

我有一个“活动”表，一个“客户”表和一个“customer_activity”表。在“活动”表中，每个活动都有自己的ID和开始，结束年份： 因此，每一行都代表一个活动，第一列是开始年份，第二列是结束年份（也就是说，以下是名称为X1的特定客户的活动）。

Start End Act_ID 
    2007 2008 1 
    2007 2008 2 
    2009 2009 3 
    2008 2008 4 
    2008 2011 5 
    2007 2008 6 
    2007 2008 7 
    2006 2007 8 
    2006 2006 9 
    2011 2013 10 
    2011 2013 11 

表“客户”有客户ID和客户名称。最后， 表“customer_activity”具有客户ID和活动ID。 我想通过客户名称查询（说与客户名称X1），并得到一个表是这样的：

 Year New Total 
     2006 2  2 
     2007 4  5 
     2008 1  4 
     2009 1  2 
     2010 0  1 
     2011 2  3 
     2012 0  2 
     2013 0  2 

我使用MariaDB的10.1.10。 任何人都可以帮助我吗？

Answer 1

已经更换了你的相关的列名，你可以试试这个：

select a.start, count(*) as total, count(c.id) as new 
from (select distinct(start) from activity) a 
     left join activity b 
     on a.start between b.start and b.end 
     left join activity c 
     on b.id=c.id and a.start=c.start 
where b.id in (select customer_activity.id 
      from customer left join customer_activity 
      on customer.id = customer_activity.customer_id 
      where customer.name = "X1") 
group by a.start 

Answer 2

如果你有多年的列表，你可以使用join和聚集：

select y.yyyy, 
     sum(case when y.yyyy = ca.start then 1 else 0 end) as news, 
     count(ca.customer_id) as total 
from (select 2007 as yyyy union all select 2008 union all . . . 
     select 2013 
    ) y left join 
    customer_activity ca 
    on y.yyyy between ca.start and ca.end left join 
    customer c 
    on ca.customer_id = c.customer_id and c.name = 'X1' 
group by y.yyyy 
order by y.yyyy; 

最好的办法产生的年这样的列表取决于数据库。