如何编写查询来清理给定问题的表

Question

我有一些数据库，我需要从一个表中清理一些数据，我想相当自动地执行此操作。

首先是模式：这个数据库中有几个表，但我只需要专门清理单个表。

表名CollectedProducts：

[Id] [int] IDENTITY(1,1) NOT NULL,
[Date] [date] NOT NULL,
[Time] [time](7) NOT NULL,
[BouquetId] [int] NOT NULL,
[EmployeeId] [int] NOT NULL,
[ProductionEnd] [bit] NOT NULL

我对最后一列感兴趣，ProductionEnd，它指出，如果特定员工在给定的一天内完成了生产。

此列的逻辑约束是，在给定日期的生产结束时，必须至少有一行将此列设置为 1。因此，它基本上是员工在给定日期的最后一行。

白天还可以将其他行设置为 1，表示员工的休息时间更长。

它后面或前面不得有另一行，其值设置为 1。

现在给定这些约束，我想选择所有具有无效ProductionEnd值的行，这样这不是每个(天、员工(对的最后一行，我还想保留所有ProductionEnd设置为 1 的行，这些行不是最后一行，但后面也没有/前面是另一行，根据我上面描述的约束，此列设置为 1。

如何编写这样的查询？

Answer 1

您可以使用窗口函数执行此操作。基本上，对于每天，您都需要具有ProductionEnd = 1行，并且其他行以较晚的时间和ProductionEnd = 1存在，并且没有行以较晚的时间和ProductionEnd = 0存在。

你可以这样说：

select *
from (
select
cp.*,
sum(ProductionEnd) 
over(partition by EmployeeId, Date order by Time desc) nbProdEnd,
sum(case when ProductionEnd = 1 then 0 else 1 end)
over(partition by EmployeeId, Date order by Time desc) nbNonProdEnd
from CollectedProducts cp
) t
where nbProdEnd > 1 and nbNonProdEnd = 0

Answer 2

如果您只想要最后一行不是"1"的日期/员工对，那么您可以使用first_value(). 以下内容获取员工日期的所有行：

select cp.*
from (select cp.*,
first_value(productionEnd) over (partition by EmployeeId, Date order by Time desc) as last_productionEnd
from CollectedProducts cp
) cp
where last_productionEnd <> 1;

如果你只想要最后一个，你可以使用：

select cp.*
from (select cp.*,
row_number() over (partition by EmployeeId, Date order by Time desc) as seqnum
from CollectedProducts cp
) cp
where seqnum = 1 and last_productionEnd <> 1;