过程:
--以删除以村为分组的数据
ALTER PROCEDURE [dbo].[deleteData]
AS
BEGIN
declare @villageID int
begin
declare TM cursor --定义游标
--以村ID为分组获取所有的村ID,循环取村ID进行操作
for (select villageID from SYS_FarmerHouse GROUP BY villageID) --查出需要的集合放到游标中
open TM --打开游标--
--读取身份证和添加时间两个字段
fetch next from TM into @villageID; --读取第一行数据
while @@FETCH_STATUS = 0
begin
delete SYS_FarmerHouse where id in (
--限定删除条件并返回需要删除的ID,以下例子中条件为删除每个分组中序号大于5的数据
select id from
(select ROW_NUMBER() over ( ORDER BY id) as row,* from SYS_FarmerHouse where villageid = @villageID ) as t
where row > 5)
--读取下一个村ID
fetch next from TM into @villageID --转到下一个游标
END
close TM --关闭游标
deallocate TM --释放游标
END
END