一个SQL问题，可能不是最困难的。
我正在从ID =-> easy的一系列相关表联接中进行查看。现在有一个表与其他所有表没有关键关系。 （批处理日期）
`ALTER VIEW [ECSUB].[FCT_Ext_Collection]
AS
SELECT sh.id AS idSubmissionHistory, dh.id, dd.id AS Description, sch.id AS idScoringHistory, sh.CreationDate, sh.UpdateDate, bd.id AS BatchDateID
FROM ECSUB.SubmissionHistory AS sh INNER JOIN EC.DocumentHistory AS dh ON sh.id = dh.idSubmissionHistory
LEFT OUTER JOIN ECSM.ScoringHistory AS sch ON sh.idScoringHistory = sch.id
LEFT OUTER JOIN EC.DocumentDescriptions AS dd ON dd.id = dh.Description
LEFT OUTER JOIN ECSUB.AddressBilling AS ab ON sh.id = ab.id
LEFT OUTER JOIN ECSUB.AddressPremise AS ap ON sh.id = ap.id
CROSS JOIN EC.BatchDates AS bd --ON sh.documentdate between .......
GO`
好吧，我的主表“ documentHistory”包含一个文档日期，我必须定义该日期属于哪个批次。
每个批次都有一个ID和开始日期。一批总是一个月长。
BatchDates表中的数据将使它更容易理解：
id month startdate
1 2010-12-01 00:00:00.000 2010-12-01 00:00:00.000
1 2011-01-01 00:00:00.000 2010-12-01 00:00:00.000
1 2011-02-01 00:00:00.000 2010-12-01 00:00:00.000
2 2011-03-01 00:00:00.000 2011-03-01 00:00:00.000
2 2011-04-01 00:00:00.000 2011-03-01 00:00:00.000
2 2011-05-01 00:00:00.000 2011-03-01 00:00:00.000
3 2011-06-01 00:00:00.000 2011-06-01 00:00:00.000
3 2011-07-01 00:00:00.000 2011-06-01 00:00:00.000
3 2011-08-01 00:00:00.000 2011-06-01 00:00:00.000
4 2011-09-01 00:00:00.000 2011-09-01 00:00:00.000
4 2011-10-01 00:00:00.000 2011-09-01 00:00:00.000
4 2011-11-01 00:00:00.000 2011-09-01 00:00:00.000
5 2011-12-01 00:00:00.000 2011-12-01 00:00:00.000
5 2012-01-01 00:00:00.000 2011-12-01 00:00:00.000
5 2012-02-01 00:00:00.000 2011-12-01 00:00:00.000
6 2012-03-01 00:00:00.000 2012-03-01 00:00:00.000
6 2012-04-01 00:00:00.000 2012-03-01 00:00:00.000
6 2012-05-01 00:00:00.000 2012-03-01 00:00:00.000
7 2012-06-01 00:00:00.000 2012-06-01 00:00:00.000
7 2012-07-01 00:00:00.000 2012-06-01 00:00:00.000
7 2012-08-01 00:00:00.000 2012-06-01 00:00:00.000
8 2012-09-01 00:00:00.000 2012-09-01 00:00:00.000
8 2012-10-01 00:00:00.000 2012-09-01 00:00:00.000
8 2012-11-01 00:00:00.000 2012-09-01 00:00:00.000
9 2012-12-01 00:00:00.000 2012-12-01 00:00:00.000
9 2013-01-01 00:00:00.000 2012-12-01 00:00:00.000
9 2013-02-01 00:00:00.000 2012-12-01 00:00:00.000
10 2013-03-01 00:00:00.000 2013-03-01 00:00:00.000
10 2013-04-01 00:00:00.000 2013-03-01 00:00:00.000
10 2013-05-01 00:00:00.000 2013-03-01 00:00:00.000
etc...........
因此，我需要根据documentdate获取batchID，因此我们使用startdate列的currentMonth。
因此：... JOIN BatchDates startDate.month中documentDate所在的位置（此处不存在）
我什至不知道我是否需要加入，交叉加入，联合等等。
提前致谢！
大号
最佳答案
join BatchDates
on datepart(yyyy,[document date]) = datepart(yyyy,[startDate])
and datepart(mm,[document date]) = datepart(mm,[startDate])
关于mysql - 按t1.date(在t2.startdate和t2.enddate之间)联接不相关的表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/9522657/