SQL逻辑问题&交叉应用查询
给定开始日期和结束日期，我需要这两个日期之间的实例计数。因此，给出以下内容：
表：
Col 1 Start_Date End_Date
1 01/01/2010 02/01/2010
2 01/01/2010 04/01/2010
3 03/01/2010 04/01/2010
4 03/01/2010 04/01/2010
如果我在第 1 个 (01/01) 和第 2 个 (02/01) 之间查找，我预计计数为 2。如果我在查找第 3 个到第 4 个，我预计计数为 2 3。如果我查看整个日期范围，那么我预计计数为 4。有意义吗？
注意：日期已转换为午夜，无需为此添加代码。此外，整个问题中的日期均采用 dd/MM/yyyy 格式。
目前我有类似以下内容：
SELECT COUNT(*), Group_Field
FROM MY_Table
WHERE Start_Date < DATEADD(DAY, 1, @StartDate) AND End_Date > @EndDate
GROUP BY Group_Field
我确实在某些时候认为这是正确的，但我现在不相信...
我以前确实有：
WITH Dates AS (
SELECT [Date] = @StartDate
UNION ALL SELECT [Date] = DATEADD(DAY, 1, [Date])
FROM Dates WHERE [Date] < @EndDate
)
SELECT COUNT(*), Group_Field -- In this case it is [Date]
FROM MY_Table
CROSS APPLY Dates
WHERE Start_Date < DATEADD(DAY, 1, @StartDate) AND End_Date > [Date]
GROUP BY Group_Field
但我不确定在这种情况下我是否正确使用了 CROSS APPLY ...
问题：
1）我是否在第二个示例中使用了 Cross Apply（以及与此相关的 CTE）？
2）如果是这样，哪种逻辑是正确的？ （我认为这是第二个）
/讨论:)
Given a start date and an end date, I need a count of instances between those two dates. So given the following:
Table:
Col 1 Start_Date End_Date
1 01/01/2010 02/01/2010
2 01/01/2010 04/01/2010
3 03/01/2010 04/01/2010
4 03/01/2010 04/01/2010
If I was looking between the 1st (01/01) and the 2nd (02/01) I would expect a count of 2. If I was looking for the 3rd to the 4th I would expect a count of 3. If I was looking across the whole date range then I would expect a count of 4. Make sense?
NOTE: The dates are already converted to midnight, no code needs to be added for this. Also, dates are in dd/MM/yyyy format throughout this question.
Currently I have something similar to the following:
SELECT COUNT(*), Group_Field
FROM MY_Table
WHERE Start_Date < DATEADD(DAY, 1, @StartDate) AND End_Date > @EndDate
GROUP BY Group_Field
I did at some point think that this was right, but i'm not convinced now...
I did previously have:
WITH Dates AS (
SELECT [Date] = @StartDate
UNION ALL SELECT [Date] = DATEADD(DAY, 1, [Date])
FROM Dates WHERE [Date] < @EndDate
)
SELECT COUNT(*), Group_Field -- In this case it is [Date]
FROM MY_Table
CROSS APPLY Dates
WHERE Start_Date < DATEADD(DAY, 1, @StartDate) AND End_Date > [Date]
GROUP BY Group_Field
But I am not sure that I am using CROSS APPLY properly in this case...
The questions:
1) Am I using Cross Apply right in the 2nd example (and the CTE for that matter)?
2) If so, which logic is right? (I think it's the 2nd)
/Discuss :)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果应该包含在内，请使用 <= 和 >=。
我相信这两种逻辑都与工作有关。
If it is supposed to be inclusive, use <= and >=.
I believe either logic with work.
最终解决方案是：
The solution ended up being: