SQL:GROUP BY之后的SUM [英] SQL: SUM after GROUP BY

问题描述

样品表

CustomerId | VoucherId | CategoryId | StartDate | EndDate
-------------------------------------------------------------
        10 |         1 |          1 | 2013-09-01| 2013-09-30
        10 |         1 |          2 | 2013-09-01| 2013-09-30
        11 |         2 |          1 | 2013-09-01| 2013-11-30
        11 |         2 |          2 | 2013-09-01| 2013-11-30
        11 |         2 |          3 | 2013-09-01| 2013-11-30
        10 |         3 |          1 | 2013-10-01| 2013-12-31
        10 |         3 |          2 | 2013-10-01| 2013-12-31
        11 |         4 |          1 | 2013-12-01| 2014-04-30

在上面的示例记录中，我想找出客户凭证涵盖的总月数

In above sample record, I want to find out total No. of months customer's vouchers cover

我需要以下形式的输出

CustomerId | Months
--------------------
        10 | 4
        11 | 8

要注意的是，凭证可以有多行用于不同的CategoryId ...

The catch is that a voucher can have multiple rows for different CategoryIds...

我计算出凭证所涵盖的月份为DATEDIFF(MM，StartDate，EndDate)+1 ...

I calculated months covered by a voucher as DATEDIFF(MM, StartDate, EndDate) + 1...

当我应用SUM(DATEDIFF(MM，StartDate，EndDate))GROUP BY VoucherId，StartDate，EndDate时 我给出错误的结果，因为VoucherId有多行....

When i apply SUM(DATEDIFF(MM, StartDate, EndDate)) GROUP BY VoucherId, StartDate, EndDate I give wrong result because of multiple rows for a VoucherId....

我得到这样的东西...

I get something like this...

CustomerId | Months
--------------------
        10 | 8
        11 | 14

在这种情况下，CategoryId没用

CategoryId is useless in this scenario

谢谢

推荐答案

sql小提琴演示

此SQL Fiddle解决了您的问题.您需要生成一个Calendar表，以便您可以将日期添加到其中.然后，您可以为每个客户计算不同的MonthYears.

This SQL Fiddle addresses your concerns. You need to generate a Calendar table so that you have something to join your dates to. Then you can do a count of distinct MonthYears for each Customer.

create table test(
  CustomerId int,
  StartDate date,
  EndDate date
  )

insert into test
values 
  (10, '9/1/2013', '9/30/2013'),
  (10, '9/1/2013', '9/30/2013'),
  (11, '9/1/2013', '11/30/2013'),
  (11, '9/1/2013', '11/30/2013'),
  (11, '9/1/2013', '11/30/2013'),
  (10, '10/1/2013', '12/31/2013'),
  (10, '10/1/2013', '12/31/2013'),
  (11, '12/1/2013', '4/30/2014')

create table calendar(
  MY varchar(10),
  StartDate date,
  EndDate date
  )

insert into calendar
values 
  ('9/2013', '9/1/2013', '9/30/2013'),
  ('10/2013', '10/1/2013', '10/31/2013'),
  ('11/2013', '11/1/2013', '11/30/2013'),
  ('12/2013', '12/1/2013', '12/31/2013'),
  ('1/2014', '1/1/2014', '1/31/2014'),
  ('2/2014', '2/1/2014', '2/28/2014'),
  ('3/2014', '3/1/2014', '3/31/2014'),
  ('4/2014', '4/1/2014', '4/30/2014')

select
  t.CustomerId, 
  count(distinct c.MY)
from
  test t
  inner join calendar c
    on t.StartDate <= c.EndDate
      and t.EndDate >= c.StartDate
group by
  t.CustomerId

这篇关于SQL:GROUP BY之后的SUM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！