时间戳

首页 > 解决方案 > SQL Server：避免子查询

sql - SQL Server：避免子查询

问题描述

我想在下面的查询中避免子查询：

SELECT ID,[Start_Date],[End_Date],Assignment_Id 
FROM (
    SELECT ID, MIN([Start_Date]) AS [Start_Date], MAX([End_Date]) AS [End_Date]
    FROM EmployeeTable
    WHERE ID IN (123456) AND ([status] ='A' OR ([status] ='C'))
    GROUP BY ID, Assignment_Id 
    ) T
WHERE [End_date] >= CONVERT(VARCHAR(10),GETDATE(),120)

以上请指教。

提前致谢。

标签： sqlsql-serversubquery

解决方案

您可以在进行聚合后立即将外部where子句转换为having子句，您不需要使用OR您可以直接使用IN子句表达它：

 SELECT ID, MIN([Start_Date]) AS [Start_Date], MAX([End_Date]) AS [End_Date]
 FROM EmployeeTable
 WHERE ID = 123456 AND [status] IN ('A','C')
 GROUP BY ID, Assignment_Id
 HAVING MAX([End_Date]) >= CAST(GETDATE() AS DATE);

推荐阅读