SQL：如何按自定义年份分组

For simplicities sake, I'll make up a similar example to what I have:

为了简单起见,我将构成一个与我所拥有的相似的例子:

Let's say a db has a table of orders with an OrderDate field and a Company field. Then there's a table of Companies and each record has a YearEndingDate (which signifies that the year ends on that date each year, e.g. 6/6).

假设数据库有一个订单表,其中包含OrderDate字段和Company字段。然后是一个公司表,每个记录都有一个YearEndingDate(表示年份在每年的那个日期结束,例如6/6)。

I need to add up all of the orders for each year.

我需要将每年的所有订单加起来。

I assume it would have to be something like this but I can't quite figure it out:

我认为它必须是这样的,但我无法弄清楚:

SELECT SUM(orderValue),
CASE WHEN orderDate <= YearEndingDate THEN DatePart(year, orderDate)
CASE WHEN orderDate > YearEndingDate THEN DatePart(year, orderDate) + 1
END as Year
FROM Orders
INNER JOIN Company ON Company.companyID = Order.companyID
GROUP By Company, Year

Any ideas?

1 个解决方案

#1

Not sure what RDMS you are using but this should do the trick. The datepart and dateadd stuff are tsql specific but I'd assume you'd have access to similar functions on whatever platform you are using. The case in the where determines which year value to use.

不确定你使用的RDMS,但这应该可以解决问题。 datepart和dateadd的东西是特定于tsql的,但我假设你可以在你正在使用的任何平台*问类似的函数。 where中的情况决定了使用哪个年份值。

Answer:

select c.companyid
      ,yearendingdate + '/' + convert(varchar, datepart(yy, dateadd(yy,1,o.orderdate))) as yearending
      ,sum(ordervalue) as numberoforders
  from @orders o
       join @companies c
         on o.companyid = c.companyid
 where orderdate between case when (cast(yearendingdate + '/' + convert(varchar, datepart(yy, o.orderdate)) as datetime) >= o.orderdate)
                         then yearendingdate + '/' + convert(varchar, datepart(yy, dateadd(yy,-1,o.orderdate)))
                         else yearendingdate + '/' + convert(varchar, datepart(yy, o.orderdate))
                          end
                     and 
                         case when (cast(yearendingdate + '/' + convert(varchar, datepart(yy, o.orderdate)) as datetime) >= o.orderdate)
                         then yearendingdate + '/' + convert(varchar, datepart(yy, o.orderdate))
                         else yearendingdate + '/' + convert(varchar, datepart(yy, dateadd(yy,1,o.orderdate)))
                          end
 group by c.companyid, o.orderdate, yearendingdate

Code to figure out problem:

找出问题的代码:

declare @orders table (OrderDate datetime
                      ,CompanyID varchar(20)
                      ,OrderValue int)

insert into @orders
values (getdate(),'MS',2)

insert into @orders
values (DateAdd(year, -1, getdate()),'MS',3)

insert into @orders
values (DateAdd(year, -1, getdate()),'MS',1)

insert into @orders
values (DateAdd(year, 1, getdate()),'MS',4)

insert into @orders
values (DateAdd(year, 1, getdate()),'Blizzard',2)

insert into @orders
values (getdate(),'MS',11)

declare @companies table (CompanyID varchar(20)
                         ,YearEndingDate varchar(20))

insert into @companies
values ('MS', '05/6')

insert into @companies
values ('Blizzard', '07/01')

select c.companyid
      ,o.orderdate
      ,yearendingdate + '/' + convert(varchar, datepart(yy, o.orderdate)) as sameyear
      ,yearendingdate + '/' + convert(varchar, datepart(yy, dateadd(yy,1,o.orderdate))) as plusyear
      ,yearendingdate + '/' + convert(varchar, datepart(yy, dateadd(yy,-1,o.orderdate))) as minusyear
  from @orders o
       join @companies c
         on o.companyid = c.companyid

select c.companyid
      ,yearendingdate + '/' + convert(varchar, datepart(yy, dateadd(yy,1,o.orderdate))) as yearending
      ,sum(ordervalue) as numberoforders
  from @orders o
       join @companies c
         on o.companyid = c.companyid
 where orderdate between case when (cast(yearendingdate + '/' + convert(varchar, datepart(yy, o.orderdate)) as datetime) >= o.orderdate)
                         then yearendingdate + '/' + convert(varchar, datepart(yy, dateadd(yy,-1,o.orderdate)))
                         else yearendingdate + '/' + convert(varchar, datepart(yy, o.orderdate))
                          end
                     and 
                         case when (cast(yearendingdate + '/' + convert(varchar, datepart(yy, o.orderdate)) as datetime) >= o.orderdate)
                         then yearendingdate + '/' + convert(varchar, datepart(yy, o.orderdate))
                         else yearendingdate + '/' + convert(varchar, datepart(yy, dateadd(yy,1,o.orderdate)))
                          end
 group by c.companyid, o.orderdate, yearendingdate