sql—如何在多个表/列中查找不同的日期

m1m5dgzv  于 2021-08-09  发布在  Java
关注(0)|答案(1)|浏览(636)

我有一个项目规划应用程序。应用程序的一部分显示了项目的时间表。有三个表保存时间轴信息;阶段计划,目标日期,实际日期。
我想能够列出所有的日期,改变了项目的时间表。
所有三个表都有“created\u datetime”和“deleted\u datetime”(如果用户删除一个阶段或编辑一个目标日期,那么“is\u deleted”设置为true,并且该阶段/旧日期从视图中隐藏)。
所以我想把这两个日期的所有不同的例子放在三个表中。
执行6条select语句并将结果合并在一起是最好的/唯一的方法吗?

SELECT DISTINCT CAST(Created_DateTime AS Date)  
FROM stg.Stage_Plan 

UNION 

SELECT DISTINCT CAST(Deleted_DateTime AS Date) 
FROM stg.Stage_Plan 

UNION 

SELECT DISTINCT CAST(AD.Created_DateTime AS Date) 
FROM stg.Stage_Plan SP 
     JOIN stg.Stage_Plan_Actual_Date AD ON SP.Stage_Plan_ID = AD.Stage_Plan_ID

UNION 

SELECT DISTINCT CAST(AD.Deleted_DateTime AS Date) 
FROM stg.Stage_Plan SP 
     JOIN stg.Stage_Plan_Actual_Date AD ON SP.Stage_Plan_ID = AD.Stage_Plan_ID 

UNION 

SELECT DISTINCT CAST(TD.Deleted_DateTime AS Date) 
FROM stg.Stage_Plan SP 
     JOIN stg.Stage_Plan_Target_Date TD ON SP.Stage_Plan_ID = TD.Stage_Plan_ID

UNION 

SELECT DISTINCT CAST(TD.Created_DateTime AS Date) 
FROM stg.Stage_Plan SP 
     JOIN stg.Stage_Plan_Target_Date TD ON SP.Stage_Plan_ID = TD.Stage_Plan_ID

这看起来很难看(我知道区别是不相关的,因为我正在做一个联盟)
想要的输出是对项目时间表进行更改的所有日期的列表,例如:

2020-05-29
2020-06-01
wz1wpwve

wz1wpwve1#

你可以用 cross apply . 我认为以下查询符合您的要求:

select distinct x.dt 
from stg.Stage_Plan sp
left join stg.stage_plan_actual_date ad on sp.stage_plan_id = ad.stage_plan_id
left join stg.stage_plan_target_date td on sp.stage_plan_id = td.stage_plan_id
cross apply (values
    (sp.Created_DateTime), (sp.Deleted_DateTime), 
    (ad.Created_DateTime), (ad.Deleted_DateTime), 
    (td.Created_DateTime), (td.Deleted_DateTime) 
) x(dt)
where sp.stage_plan_id = ?

假设您需要给定项目的数据,如果需要,还应该有 where 子句-我添加了。

相关问题