if exists(select 1 from sysobjects where name='groupnumB')
drop table groupnumB
create TABLE groupnumB
(A INT, B INT)
INSERT groupnumB SELECT 1,11
UNION ALL SELECT 1,11
UNION ALL SELECT 1,10
UNION ALL SELECT 2,11
UNION ALL SELECT 2,20
UNION ALL SELECT 2,20
UNION ALL SELECT 2,11
UNION ALL SELECT 2,15
UNION ALL SELECT 3,23
UNION ALL SELECT 3,23
UNION ALL SELECT 3,15
UNION ALL SELECT 3,6
UNION ALL SELECT 3,6
UNION ALL SELECT 3,6
go--我的的觉方案
if exists (select 1 from sysobjects where name='v_groupnumB')
drop view v_groupnumB
go
create view v_groupnumB
as
(
select fnewID=row_number() over(order by B),* from groupnumB
) go
select * from v_groupnumB
delete from v_groupnumB where FnewID not in(select max(fnewID) from v_groupnumB group by A,B)
select * from v_groupnumB--我希望不使用创建视图、创建临时表的的方式
--请问能实现么?
--也就是一句SQL 搞定
--类似与ORACLE的
delete from v_groupnumB where rowid not in (select min(rowid) from v_groupnumB group by A, B)
drop table groupnumB
create TABLE groupnumB
(A INT, B INT)
INSERT groupnumB SELECT 1,11
UNION ALL SELECT 1,11
UNION ALL SELECT 1,10
UNION ALL SELECT 2,11
UNION ALL SELECT 2,20
UNION ALL SELECT 2,20
UNION ALL SELECT 2,11
UNION ALL SELECT 2,15
UNION ALL SELECT 3,23
UNION ALL SELECT 3,23
UNION ALL SELECT 3,15
UNION ALL SELECT 3,6
UNION ALL SELECT 3,6
UNION ALL SELECT 3,6
go--我的的觉方案
if exists (select 1 from sysobjects where name='v_groupnumB')
drop view v_groupnumB
go
create view v_groupnumB
as
(
select fnewID=row_number() over(order by B),* from groupnumB
) go
select * from v_groupnumB
delete from v_groupnumB where FnewID not in(select max(fnewID) from v_groupnumB group by A,B)
select * from v_groupnumB--我希望不使用创建视图、创建临时表的的方式
--请问能实现么?
--也就是一句SQL 搞定
--类似与ORACLE的
delete from v_groupnumB where rowid not in (select min(rowid) from v_groupnumB group by A, B)
from (select fnewID=row_number() over(partition by A order by B desc),* from groupnumB)t
where fnewID>1
select * from groupnumBdelete t
from (select fnewID=row_number() over(partition by A order by B desc),* from groupnumB)t
where fnewID>1select * from groupnumB(14 行受影响)
A B
----------- -----------
1 11
1 11
1 10
2 11
2 20
2 20
2 11
2 15
3 23
3 23
3 15
3 6
3 6
3 6(14 行受影响)(11 行受影响)A B
----------- -----------
1 11
2 20
3 23(3 行受影响)不对啊
delete t
from (select fnewID=row_number() over(partition by A,B order by A,B),* from groupnumB)t
where fnewID>1