这是表格的示例：
基于该表，我需要创建一个查询，将这样的结果作为结果，
对于每个ProductID，我将有1行，列数为N，在破损前，返工数将计数，如果产品破损10次，则将看到10列。
huaping1207 回答：根据下一行的值计算行数
您可以使用窗口函数来统计每一行的中断次数。然后只需使用条件聚合：
select productid,creationdate,sum(case when statustype = 'rework' and num_breaks = 0
then 1 else 0
end) as cnt_before_first_break,sum(case when statustype = 'rework' and num_breaks = 1
then 1 else 0
end) as cnt_before_second_break
from (select t.*,sum(case when stationtype = 'break' then 1 else 0 end) over
(partition by productid,creationdate
order by status_timestamp
) as num_breaks
from t
) t
group by productid,creationdate;
由于您只需要2级计数，因此可以考虑使用此查询。
with cte as (
select '201207' as ProductId,'2019-10-01' as CreationDate,'Rework' as StationDesc,'2019-09-01' as status_timestamp
union all
select '201207','2019-10-01','Rework','2019-09-02'
union all
select '201207','2019-09-03'
union all
select '201207','Break','2019-09-04'
union all
select '201207','2019-09-05'
union all
select '201207','2019-09-06'
union all
select '201207','2019-09-07'
union all
select '201207','2019-09-08'
union all
select '201207','2019-09-09'
)
select
ProductId,CreationDate,sum(case when status_timestamp < d1
and StationDesc = 'Rework' then 1 else 0 end) as FirstBreak,sum(case when status_timestamp between d1 and d2
and StationDesc = 'Rework' then 1 else 0 end) as SecondBreak
from cte
cross join
(select t1.status_timestamp as d1,t2.status_timestamp as d2 from
(select row_number() over (partition by ProductId,CreationDate order by status_timestamp) as rn,status_timestamp from cte where StationDesc = 'Break') as t1
left join
(select row_number() over (partition by ProductId,status_timestamp from cte where StationDesc = 'Break') as t2 on t2.rn = t1.rn + 1
where t1.rn < 2) as t3
group by ProductId,CreationDate
输出：
