我有一种情况，我需要删除共享相同字段值的连续记录（“无法通知客户”），但是我需要离开第一个和最后一个实例
样本数据
date type log
20/11/2014 09:05 System, Order Added
20/11/2014 09:18 Mark, Invoice Printed
20/11/2014 10:00 System, Failed to notify Customer
20/11/2014 10:05 System, Failed to notify Customer
20/11/2014 10:10 System, Failed to notify Customer
20/11/2014 10:15 System, Failed to notify Customer
20/11/2014 10:20 System, Failed to notify Customer
20/11/2014 12:05 System, Order Completed
结果输出
date type log
20/11/2014 09:05 System, Order Added
20/11/2014 09:18 Mark, Invoice Printed
20/11/2014 10:00 System, Failed to notify Customer
20/11/2014 10:20 System, Failed to notify Customer
20/11/2014 12:05 System, Order Completed
有某种公式化sql服务器查询以实现此目的的方法吗？对于我的一生，我无法理解如何处理这个问题
试试这个：
样本数据 ：
use tempdb
create table temp(
[date] datetime,
type varchar(100),
[log] varchar(100)
)
insert into temp values
('11/20/2014 09:05', 'System', 'Order Added'),
('11/20/2014 09:18', 'Mark', 'Invoice Printed'),
('11/20/2014 10:00', 'System', 'Failed to notify Customer'),
('11/20/2014 10:05', 'System', 'Failed to notify Customer'),
('11/20/2014 10:10', 'System', 'Failed to notify Customer'),
('11/20/2014 10:15', 'System', 'Failed to notify Customer'),
('11/20/2014 10:20', 'System', 'Failed to notify Customer'),
('11/20/2014 12:05', 'System', 'Order Completed');
解决方案使用ROW_NUMBER()：
with cte as(
select
*,
rn = row_number() over(partition by log order by [date]),
cc = count(*) over(partition by log)
from temp
where
log = 'Failed to notify Customer'
)
delete
from cte
where
rn > 1 and rn < cc
select * from temp
drop table temp
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句