--供参考declare @mm char(11), @cfid char(11)
declare id_cursor cursor
for select id from Enterprise
OPEN spid_cursor
FETCH NEXT FROM id_cursor into @cfid
WHILE @@FETCH_STATUS = 0
BEGIN
set @mm = @cfid FETCH NEXT FROM id_cursor into @cfid
if @mm = @cfid
DELETE FROM Enterprise WHERE CURRENT OF id_cursor
END
CLOSE id_cursor
DEALLOCATE id_cursor
declare id_cursor cursor
for select id from Enterprise
OPEN spid_cursor
FETCH NEXT FROM id_cursor into @cfid
WHILE @@FETCH_STATUS = 0
BEGIN
set @mm = @cfid FETCH NEXT FROM id_cursor into @cfid
if @mm = @cfid
DELETE FROM Enterprise WHERE CURRENT OF id_cursor
END
CLOSE id_cursor
DEALLOCATE id_cursor
--建立测试环境
Create table Enterprise
(id Int,
corpname Nvarchar(50)
)
GO
--插入数据
Insert Enterprise Values(77902,N'慈溪市洁士物业管理有限公司')
Insert Enterprise Values(104533,N'慈溪市洁士物业管理有限公司')
Insert Enterprise Values(86345,N'慈溪市大通房地产开发有限公司')
Insert Enterprise Values(106005,N'慈溪市大通房地产开发有限公司')
Insert Enterprise Values(78155,N'宁波东大空调设备有限公司')
Insert Enterprise Values(103870,N'宁波东大空调设备有限公司')
Insert Enterprise Values(82471,N'慈溪市晨光印业有限公司')
Insert Enterprise Values(103747,N'慈溪市晨光印业有限公司')
Insert Enterprise Values(89929,N'慈溪市横河资产经营公司')
Insert Enterprise Values(102942,N'慈溪市横河资产经营公司')
GO
--测试
Select * from Enterprise
Delete from Enterprise Where id Not In (Select MIn(id) As id from Enterprise Group By corpname)
Select * from Enterprise
--删除测试环境
Drop table Enterprise
--结果
/*
77902 慈溪市洁士物业管理有限公司
86345 慈溪市大通房地产开发有限公司
78155 宁波东大空调设备有限公司
82471 慈溪市晨光印业有限公司
89929 慈溪市横河资产经营公司
*/
declare mc_cursor cursor
for select corpname from Enterprise
OPEN mc_cursor
FETCH NEXT FROM mc_cursor into @cfmc
WHILE @@FETCH_STATUS = 0
BEGIN
set @mm = @cfmc FETCH NEXT FROM mc_cursor into @cfmc
if @mm = @cfmc
DELETE FROM Enterprise WHERE CURRENT OF mc_cursor
END
CLOSE mc_cursor
DEALLOCATE mc_cursor
where ID not in(select min(id) from Enterprise group by corpname)
Email:[email protected]
iocq:38363454