sql - 从子查询到主查询选择最小日期值
问题描述
我正在尝试将 min Date 值作为主查询中的一列,其中主查询和子查询具有内部联接,并且 min Date 值取决于来自主查询联接的 3 列的分组
我在主查询 Select 语句中尝试了子查询,但正如我的研究所示,它们只执行一次。我尝试了各种连接方式,但无法让逻辑或语法正常工作。我认为 cte 的组合可能有效,但无法理解(对于老狗学习新技巧的这些高级查询仍然有点太新了)
DECLARE
@StartBallDate date = '09-05-2009',
@StartLatDate date = '09-05-2009',
@Male int = 1,
@Female int = 4
SELECT T.*, E.StylID, C.PtsFirstRec
FROM dbo.tblPtsPerCompHistory AS T
INNER JOIN
(
SELECT Min(Comp_Date) PtsFirstRec, Competition_Idx
FROM tblCompetitions
GROUP BY Comp_Date, Competition_Idx
) C ON C.Competition_Idx = T.PtsCompID
INNER JOIN tblEvtStructure AS E ON E.EvtStruct_Idx = T.PtsStructID
WHERE E.SectnID Not Between 9 And 10 And (T.PtsMale = @Male Or T.PtsFemale = @Female And E.StylID = 1 And PtsFirstRec >= @StartBallDate) Or (T.PtsMale = @Male Or T.PtsFemale = @Female And E.StylID = 2 And PtsFirstRec >= @StartLatDate)
到目前为止,这给了我每一行的 Comp_Date。我需要实现的,以便我可以稍后在我的程序中的其他地方使用数据是
T.PtsMale T.PtsFemale C.Comp_Date E.StylID PtsFirstRec
1 4 2009-05-05 1 2009-05-05
1 4 2009-05-05 2 2009-05-05
1 4 2010-03-16 1 2009-05-05
1 202 2015-03-25 1 2015-03-25
1 4 2015-03-25 2 2009-05-05
1 202 2016-07-17 1 2015-03-25
所以我有所有的行,其中 Ptsmale 或 PtsFemale 但最小日期值 PtsFirstRec 是配对的最早日期,也由 E.StylID 分组
解决方案
这是另一个窗口函数:
DECLARE
@StartBallDate date = '09-05-2009',
@StartLatDate date = '09-05-2009',
@Male int = 1,
@Female int = 4
SELECT T.*, E.StylID, MIN(C.PtsFirstRec) OVER (PARTITION BY T.PtsMale T.PtsFemale, E.StylID ORDER BY C.Comp_Date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) [NewValue]
FROM dbo.tblPtsPerCompHistory AS T
INNER JOIN
(
SELECT Min(Comp_Date) PtsFirstRec, Competition_Idx
FROM tblCompetitions
GROUP BY Comp_Date, Competition_Idx
) C ON C.Competition_Idx = T.PtsCompID
INNER JOIN tblEvtStructure AS E ON E.EvtStruct_Idx = T.PtsStructID
WHERE E.SectnID Not Between 9 And 10 And (T.PtsMale = @Male Or T.PtsFemale = @Female And E.StylID = 1 And PtsFirstRec >= @StartBallDate) Or (T.PtsMale = @Male Or T.PtsFemale = @Female And E.StylID = 2 And PtsFirstRec >= @StartLatDate)
如果您包含您的测试数据,我们将能够确保代码确实有效。但它应该是这样的。
推荐阅读
- android - 带有标签和自动 x 轴滚动的 Android SimpleGraph 问题
- javascript - ReactDOM 没有渲染
- mysql - 来自 MySQL 数据库阅读器的 Powershell Export-CSV 在导出过程中失败
- python - brew更新后损坏的virtualenv
- javascript - 页面不活动时停止 setTimeout 循环,页面活动时重新启动
- r - Proc Phreg Baseline 语句等效于 R
- ada - 在编译时生成函数
- windows - 时间戳证书已安装,安装它们是什么?
- sql - 如何将模式对象移动到不同的表空间
- c# - C#在内存数据库中共享索引?