在一列中的条目不存在于另一列的所有条目中(Entry in one column not existng in all the entries of another column)

我使用下面的查询 -

Select B.Backup_datetime, E.date from Table B cross apply (select max(E.Backup_datetime) date from Table E where E.Backup_datetime<B.Backup_datetime) E

创建以下结果 -

Backup_datetime date 08 07 07 05 05 04 04 03

现在，我希望看到Backup_datetime整个date集中不存在。在这种情况下， Backup_datetime '08'不在date集中。所以，我想只看到Backup_datetime = '08' 。如何附加现有代码才能完成此操作？

I am using below query -

Select B.Backup_datetime, E.date from Table B cross apply (select max(E.Backup_datetime) date from Table E where E.Backup_datetime<B.Backup_datetime) E

Creates below result -

Backup_datetime date 08 07 07 05 05 04 04 03

Now, I want to see the row with Backup_datetime which does not exist in the entire set of date. In this case, '08' from Backup_datetime is not present in the date set. So, I want to see only the row with Backup_datetime='08'. How can I append the existing code to get this done?

最满意答案

这应该做到这一点。我使用了一个cte，所以我可以引用你的原始查询而不必多次写它。

;with cte as ( Select B.Backup_datetime, E.date from Table B cross apply (select max(E.Backup_datetime) date from Table E where E.Backup_datetime<B.Backup_datetime) E ) SELECT * FROM cte where backup_datetime NOT IN (select date from cte)

或者如果你不想使用cte

Select B.Backup_datetime , E.date from Table B cross apply (select max(E.Backup_datetime) date from Table E where E.Backup_datetime<B.Backup_datetime) where backupdate not in (Select E.date from Table B cross apply (select max(E.Backup_datetime) date from Table E where E.Backup_datetime<B.Backup_datetime))

This should do it. I used a cte so I could reference your original query without having to write it more than once.

;with cte as ( Select B.Backup_datetime, E.date from Table B cross apply (select max(E.Backup_datetime) date from Table E where E.Backup_datetime<B.Backup_datetime) E ) SELECT * FROM cte where backup_datetime NOT IN (select date from cte)

Or if you don't want to use a cte

Select B.Backup_datetime , E.date from Table B cross apply (select max(E.Backup_datetime) date from Table E where E.Backup_datetime<B.Backup_datetime) where backupdate not in (Select E.date from Table B cross apply (select max(E.Backup_datetime) date from Table E where E.Backup_datetime<B.Backup_datetime))

更多推荐