Question

令人奇怪的是,人们将如何有效地解决下列问题。

有一个简单的表格,追踪一组“申请”报告。报告“重报”和“提交”的轨道报告编写和提交之间的时间段应当是核准人国际发展署承诺提交单一报告的时间,如果其批准人国际发展署在相同时间段内为我想要指明的另一份报告显示情况。

例(表,“报告”):

"Created"                 "Submitted"                "ApproverID"
4/20/2012 5:01:32 AM          4/20/2012 5:30:32 AM          10
4/20/2012 5:08:32 AM          4/20/2012 5:45:32 AM          10
4/20/2012 5:01:32 AM          4/19/2012 5:38:16 PM          15

举例来说,我要谈谈选举和选举,并回到重叠的行文:

 "Created"                 "Submitted"                "ApproverID"     "Duplicate_ID"
4/20/2012 5:01:32 AM          4/20/2012 5:30:32 AM          10            1
4/20/2012 5:08:32 AM          4/20/2012 5:45:32 AM          10            1

......并可能添加一个具有独特价值的新的一栏“许可证”,以报告我刚才可以做的SlectT DISTINCT Duplicate_ID的具体重叠点。

我与BETWEEN在座,但没有成功。感谢任何帮助。

感谢

Answer 1

联合国

SELECT R1.Created, R1.Submitted, R1.ApproverID, count(*) 
FROM reports R1
INNER JOIN reports R2
  on R1.Created between R2.Created and R2.Submitted --ON
  OR R2.created between R2.Created and R2.submitted --OR
GROUP BY R1.Created, R1.Submitted, R1.ApproverID

Answer 2

这给你了一份相互矛盾的报告清单:

SELECT r1.approvalID, r2.approvalID
FROM reports r1
JOIN reports r2
  ON r2.approverID = r1.approverID
  AND r2.created < r1.created
  AND r2.submitted >= r1.created
  AND r2.submitted < r1.submitted

这假定你拥有一个主要的关键批准信息。

友情链接