需求:
样品表关联查询审核记录表,角色A的最后审核日期,早于角色B的最后审核日期的样品记录。
问题:
审核表中,不一定存在角色A或者角色B的审核记录,所以查询出来的日期可能为null,进行日期比较的时候会有意想不到的问题。
SQL如下:
SELECT [t0].[ID], [t0].[TimeStamp], [t0].[ApplicationNumber], [t0].[ApplicantID], [t0].[Type], [t0].[LegID], [t0].[TopicID], [t0].[BearVoyageTaskID], [t0].[VoyageReportID], [t0].[VoyageReportDetailID], [t0].[NoOceanTopicID], [t0].[ActivityID], [t0].[ActivityStartTime], [t0].[ActivityEndTime], [t0].[UsingNecessity], [t0].[RepatriaionNecessity], [t0].[CommitResultTime], [t0].[CommitResultForm], [t0].[CommitResultAmount], [t0].[ApplyTime], [t0].[CreateTime], [t0].[ApplicationStateID], [t0].[Remark]
FROM [dbo].[SampleApplication] AS [t0]
LEFT OUTER JOIN [dbo].[VoyageTask] AS [t1] ON [t1].[ID] = [t0].[BearVoyageTaskID]
LEFT OUTER JOIN [dbo].[ApplicationState] AS [t2] ON [t2].[ID] = [t0].[ApplicationStateID]
WHERE ([t2].[StateCode] IN ('VODCSubmit','VSAudit', 'VSSubmit', 'LSAudit', 'LSSubmit','AdminAccept')) AND (((
SELECT [t4].[AuditTime] --- 重点
FROM (
SELECT TOP (1) [t3].[AuditTime]
FROM [dbo].[ApplicationAuditing] AS [t3]
WHERE ([t3].[SampleApplicationID] = [t0].[ID]) AND ([t3].[AuditType] = 1) AND ([t3].[State] = 1)
ORDER BY [t3].[AuditTime] DESC
) AS [t4]
)) < ((
SELECT [t6].[AuditTime]
FROM (
SELECT TOP (1) [t5].[AuditTime]
FROM [dbo].[ApplicationAuditing] AS [t5]
WHERE ([t5].[SampleApplicationID] = [t0].[ID]) AND ([t5].[AuditType] = 0) AND ([t5].[State] = 1)
ORDER BY [t5].[AuditTime] DESC
) AS [t6]
))) AND (([t1].[VoyageID]) IN (2,3)) AND ([t0].[Type] = 1)
把两个日期的比较拿出来看:
SELECT [t4].[AuditTime]
FROM (
SELECT TOP (1) [t3].[AuditTime]
FROM [dbo].[ApplicationAuditing] AS [t3]
WHERE ([t3].[SampleApplicationID] = 4) AND ([t3].[AuditType] = 1) AND ([t3].[State] = 1)
ORDER BY [t3].[AuditTime] DESC
) AS [t4]
<
SELECT [t6].[AuditTime]
FROM (
SELECT TOP (1) [t5].[AuditTime]
FROM [dbo].[ApplicationAuditing] AS [t5]
WHERE ([t5].[SampleApplicationID] = 4) AND ([t5].[AuditType] = 0) AND ([t5].[State] = 1)
ORDER BY [t5].[AuditTime] DESC
) AS [t6]
会报 '<' 附近有语法错误。
转载于:https://blog.51cto.com/wangyuelucky/1427988