比如表TABLE:
A B C D E
1 2 3 4 5
1 2 3
2 2 5 2 1
3 4 6 2 9
2 2 5 3
7 6 7 8
----------------------------
删除指定字段A,B,C中有重复的且整条记录相对不全的,即保存相对字段信息完整的内容
如:
A B C D E
1 2 3 4 5
2 2 5 2 1
3 4 6 2 9
7 6 7 8
A B C D E
1 2 3 4 5
1 2 3
2 2 5 2 1
3 4 6 2 9
2 2 5 3
7 6 7 8
----------------------------
删除指定字段A,B,C中有重复的且整条记录相对不全的,即保存相对字段信息完整的内容
如:
A B C D E
1 2 3 4 5
2 2 5 2 1
3 4 6 2 9
7 6 7 8
解决方案 »
- 这个插入存储过程怎么写才正确
- 查询第31行到40行之间的数据
- sql2005程序访问的问题
- 如何打开SQL Server Performance Monitor监视
- SQL查询(1,2,3)如何显示成3条记录?
- 求助!win7系统32位安装SQL SERVER2008时出错——注册类型库出错
- (数据库高手请进)存储过程中写递归函数
- 帮忙看看这个触发器为什么不执行?
- Sybase如何设置自动备份数据库、日志?
- 请问怎样可以在两台机子上(都有SQL Server)备份各自建立的数据库??
- 进行还原SQL SERVER 数据库时,总是提示“数据库正在使用所以未能获得对数据库的排它访问权, restore database 操作异常终止”
- 关于“聚合”
*
from
tb t
where
D=(select max(D) from tb where a=t.a and b.=t.b and c=t.c)
where
(exists(select 1 from tb where a=t.a and b=t.b and c=t.c and d is not null) and t.d is null ) or
(exists(select 1 from tb where a=t.a and b=t.b and c=t.c and e is not null) and t.e is null ) or
(exists(select 1 from tb where a=t.a and b=t.b and c=t.c and d is not null and e is not null) and t.d is not null and t.e is null) or
(exists(select 1 from tb where a=t.a and b=t.b and c=t.c and d is not null and e is not null) and t.d is null and t.e is not null)
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([A] [int],[B] [int],[C] [int],[D] [int],[E] [int])
INSERT INTO [tb]
SELECT '1','2','3','4','5' UNION ALL
SELECT '1','2','3',NULL,NULL UNION ALL
SELECT '2','2','5','2','1' UNION ALL
SELECT '3','4','6','2','9' UNION ALL
SELECT '2','2','5','3',NULL UNION ALL
SELECT '7','6','7','8',NULL--SELECT * FROM [tb]-->SQL查询如下:
--删除数据:
;WITH t AS
(
SELECT rn=ROW_NUMBER()OVER(PARTITION BY a,b,c ORDER BY sign(d)+sign(e) DESC),*
FROM tb
)
DELETE t WHERE rn>1--查询结果:
SELECT * FROM tb
/*
A B C D E
----------- ----------- ----------- ----------- -----------
1 2 3 4 5
2 2 5 2 1
3 4 6 2 9
7 6 7 8 NULL(4 行受影响)
*/
delete t from tb t
where
(exists(select 1 from tb where a=t.a and b=t.b and c=t.c and d is not null) and t.d is null ) or
(exists(select 1 from tb where a=t.a and b=t.b and c=t.c and e……
我观察好久了。自获得mvp之后, 小F的功力大不如以前了。 颓废了啊~~~
--drop table testcreate table test (a char, b char, c char, d char, e char)
insert into test
SELECT '1','2','3','4','5' UNION ALL
SELECT '1','2','3',NULL,NULL UNION ALL
SELECT '2','2','5','2','1' UNION ALL
SELECT '3','4','6','2','9' UNION ALL
SELECT '2','2','5','3',NULL UNION ALL
SELECT '7','6','7','8',NULL UNION ALL
SELECT '2','2','5','2','1' UNION ALL
SELECT '7','6','7','8',NULL
--select * from test
alter table test add idcol int identity (1, 1)
--drop table #deletion_keys
select
top 0 a, b, c, d, e, convert(int, 0) idcol
into
#deletion_keys
from
test
goselect
a, b, c, count(*)-1 reccount
into
#deletion_candidate
from
test
group by
a, b, c
having
count(*)>1declare deletion_candidate_cursor cursor local for
select
*
from
#deletion_candidatedeclare @key1 char, @key2 char, @key3 char, @rowcount intopen deletion_candidate_cursorfetch next from deletion_candidate_cursor into @key1, @key2, @key3, @rowcountwhile @@fetch_status=0
begin
set rowcount @rowcount insert into
#deletion_keys
select
*
from
test
where
a=@key1 and
b=@key2 and
c=@key3
order by
a, b, c, coalesce(d, char(0)), coalesce(e, char(0)) fetch next from deletion_candidate_cursor into @key1, @key2, @key3, @rowcount
end
set rowcount 0
close deletion_candidate_cursor
deallocate deletion_candidate_cursordelete
a
from
test a inner join #deletion_keys b on
a.idcol=b.idcolalter table test drop column idcoldrop table #deletion_keys
drop table #deletion_candidateselect * from test