SQL查询日期之间显示为属于每一日期，具体日期+数据！(SQL QUERY showing Betw

这是一个表是如何呈现

SELECT RequestsID, Country, Activity,
[People needed (each day)], [Start date], [End date]
FROM dbo.Requests

会有很多的要求，我想总结一下，每天（！）的“人们需要”，还不如开始和结束日期之间。

此外，我想组由国家，有其历史我想获得的数据之间设置的可能性。

有些日子，可能是空的就需要人（0），但日期应反正呈现。 请注意，可以指出相同的日期，并在同一个国家多个请求 - 但活动是那么不同。

查询应该是这样的（当然，it's不是SQL，你可以看到，只是想显示逻辑）

From Requests,
show Country and SUM 'People needed'
where (column not in Requests table-Date) is a Date (will be
a number of dates, I want to set the scope by a Start and End date)
and Requests.Country is @Country 
(and the Date(s) above of course is between the Requests Start date and End date...)
And from (a non existing table...?) show Date
Group by Country

我希望看到这样的事情：

Date            Country         People needed

06/01/2010      Nigeria          34 // this might be from three different Requests, all pointing out Nigeria. People needed might be (30+1+3 = 34)
06/02/2010      Nigeria          10
06/03/2010      Nigeria           0
06/04/2010      Nigeria           1
06/05/2010      Nigeria         134

06/01/2010      China             2
06/02/2010      China             0
06/03/2010      China            14
06/04/2010      China            23
06/05/2010      China            33

06/01/2010      Chile             3
06/02/2010      Chile             4
06/03/2010      Chile             0
06/04/2010      Chile             0
06/05/2010      Chile            19

你会怎么做呢？

注：我希望看到某种类型的示例代码，即可开始:-)

Answer 1:

通常情况下，我建议具有包含日期​​的顺序列表的静态日历表。 但是，使用凯德空肠Roux的聪明之处产生一个日历表的方法，你会是这样的：

;With Calendar As
    (
     Select Cast(Floor(Cast(@StartDate As float)) As datetime) As [Date]
     Union All
     Select DateAdd(d, 1, [Date])
     From Calendar
     Where DateAdd(d, 1, [Date]) < @EndDate
    )
Select C.[Date], R.Country, Sum(R.PeopleNeeded)
From Calendar As C
    Left Join Requests As R
        On C.[Date] Between R.[Start Date] And R.[End Date]
            And ( @Country Is Null Or R.Country = @Country )
Group By C.[Date], R.Country    
Option (MAXRECURSION 0); 

现在，如果你想在全国筛选的情况下，使得返回的仅几天是那些对于给定的国家，有数据，那么只需在需要更改左连接到内部连接。

加成

从意见，有人要求以显示所有国家是否有一个请求。 要做到这一点，你需要跨越加入到国家表：

With Calendar As
    (
     Select Cast(Floor(Cast(@StartDate As float)) As datetime) As [Date]
     Union All
     Select DateAdd(d, 1, [Date])
     From Calendar
     Where DateAdd(d, 1, [Date]) < @EndDate
    )
Select C.[Date], C2.Country, Sum(R.PeopleNeeded)
From Calendar As C
    Cross Join Countries As C2
    Left Join Requests As R
        On C.[Date] Between R.[Start Date] And R.[End Date]
            And R.CountryId = C2.CountryId
Group By C.[Date], C2.Country    
Option (MAXRECURSION 0); 

Answer 2:

通常我会用所有日期的计数或数据透视表，然后根据该日期范围之间加入。

类似的技术在这里讨论 。

Answer 3:

这样的事情？

select d.Date, c.Country, sum(People) as PeopleNeeded 
from Dates d left join Requests r on d.Date between r.Start and r.End
group by d.Date, c.Country

其中，日期包含日期的适当范围，如凯德鲁的回答

文章来源: SQL QUERY showing Between Dates as specific dates + Data belonging to each date!