下面我给出实际数据供大家使用。
表tab
col1 col2 col3
111 aaa ok
222 aaa ok
333 bbb ok
111 bbb no
111 aaa no
222 aaa no
111 aaa ok 第一个问题:查找col1,col2相同的重复记录,请写出sql查询语句
查询结果应该如下
col1 col2 col3
111 aaa ok
111 aaa no
111 aaa ok
222 aaa ok
222 aaa no 第二个问题:如何删除col1,col2,col3完全相同的字段,而只保留一条数据
删除后,tab表中数据应为
col1 col2 col3
222 aaa ok
333 bbb ok
111 bbb no
111 aaa no
222 aaa no
111 aaa ok
表tab
col1 col2 col3
111 aaa ok
222 aaa ok
333 bbb ok
111 bbb no
111 aaa no
222 aaa no
111 aaa ok 第一个问题:查找col1,col2相同的重复记录,请写出sql查询语句
查询结果应该如下
col1 col2 col3
111 aaa ok
111 aaa no
111 aaa ok
222 aaa ok
222 aaa no 第二个问题:如何删除col1,col2,col3完全相同的字段,而只保留一条数据
删除后,tab表中数据应为
col1 col2 col3
222 aaa ok
333 bbb ok
111 bbb no
111 aaa no
222 aaa no
111 aaa ok
解决方案 »
- 请高级人才进来说话,关于数据库文件迁移拓展的问题
- 我要做进销存,麻烦帮忙设计表,物料表,材料进出库表,当前库存表,要考虑先进先出,移动加权平均,不核算成本,谢谢!
- 求租:随机取数的sql语句
- hibernate中HQL好象没有left关键字,我不知道怎么用hibernate的原生注入,请问SQL还有其它函数可以取一个字段的前多少位吗?
- 条码编程的问题 请解决呀 谢谢了
- 邹大侠请进,关于sql 2000里面的全文目录
- 存储过程 字符串转化为整型
- 如何知道DTS执行没有成功?
- 请教各位大虾!帮个忙,急!非常急,在线等待
- 初来匝道~~~请各位数据库高手到指教!!!!!!!!!!!
- 大家讨论一下,QQ好友的数据关系吧。在线
- 这么检索null列,但得到的结果是false或true?
go
create table [tb] (col1 int,col2 nvarchar(6),col3 nvarchar(4))
insert into [tb]
select 111,'aaa','ok' union all
select 222,'aaa','ok' union all
select 333,'bbb','ok' union all
select 111,'bbb','no' union all
select 111,'aaa','no' union all
select 222,'aaa','no' union all
select 111,'aaa','ok'
select * from tb t where exists
(select 1 from tb where col1=t.col1 and col2=t.col2 group by col1,col2 having count(*)>1 )
order by col1
/*
col1 col2 col3
----------- ------ ----
111 aaa ok
111 aaa no
111 aaa ok
222 aaa no
222 aaa ok(5 個資料列受到影響)*/
select t.* from tab t where exists(select 1 from (select col1 , col2 from tab group by col1 , col2 having count(1) > 1) m where col1 = t.col1 and col2 = t.col2 )第二个问题:如何删除col1,col2,col3完全相同的字段,而只保留一条数据
需要用到临时表.select distinct * into tmp from tab
delete from tab
insert into tab select * from tmp
drop table tmp
go
create table [tb] (col1 int,col2 nvarchar(6),col3 nvarchar(4))
insert into [tb]
select 111,'aaa','ok' union all
select 222,'aaa','ok' union all
select 333,'bbb','ok' union all
select 111,'bbb','no' union all
select 111,'aaa','no' union all
select 222,'aaa','no' union all
select 111,'aaa','ok'
---2000
alter table tb
add id int identity
go
delete t from tb t where exists(select 1 from tb
where col1=t.col1 and col2=t.col2 and col3=t.col3 and id>t.id)go
alter table tb
drop column id
go
select * from tb
/*
col1 col2 col3
----------- ------ ----
222 aaa ok
333 bbb ok
111 bbb no
111 aaa no
222 aaa no
111 aaa ok(6 個資料列受到影響)
*/
create table [tab] (col1 int,col2 nvarchar(6),col3 nvarchar(4))
insert into [tab]
select 111,'aaa','ok' union all
select 222,'aaa','ok' union all
select 333,'bbb','ok' union all
select 111,'bbb','no' union all
select 111,'aaa','no' union all
select 222,'aaa','no' union all
select 111,'aaa','ok'select t.* from tab t where exists(select 1 from (select col1 , col2 from tab group by col1 , col2 having count(1) > 1) m where col1 = t.col1 and col2 = t.col2 ) order by t.col1 , t.col2drop table tab/*
col1 col2 col3
----------- ------ ----
111 aaa ok
111 aaa no
111 aaa ok
222 aaa ok
222 aaa no(所影响的行数为 5 行)
*/第二个问题:如何删除col1,col2,col3完全相同的字段,而只保留一条数据
需要用到临时表.
create table [tab] (col1 int,col2 nvarchar(6),col3 nvarchar(4))
insert into [tab]
select 111,'aaa','ok' union all
select 222,'aaa','ok' union all
select 333,'bbb','ok' union all
select 111,'bbb','no' union all
select 111,'aaa','no' union all
select 222,'aaa','no' union all
select 111,'aaa','ok'select distinct * into tmp from tab
delete from tab
insert into tab select * from tmp select * from tabdrop table tmp,tab
/*
col1 col2 col3
----------- ------ ----
111 aaa no
111 aaa ok
111 bbb no
222 aaa no
222 aaa ok
333 bbb ok(所影响的行数为 6 行)
*/
go
create table [tb] (col1 int,col2 nvarchar(6),col3 nvarchar(4))
insert into [tb]
select 111,'aaa','ok' union all
select 222,'aaa','ok' union all
select 333,'bbb','ok' union all
select 111,'bbb','no' union all
select 111,'aaa','no' union all
select 222,'aaa','no' union all
select 111,'aaa','ok'
---2005
;with wu
as
(select *,id=row_number()over(partition by col1,col2,col3 order by getdate()) from tb)
delete t from wu t where exists(select 1 from wu
where col1=t.col1 and col2=t.col2 and col3=t.col3 and id>t.id)
select * from tb
/*
col1 col2 col3
----------- ------ ----
222 aaa ok
333 bbb ok
111 bbb no
111 aaa no
222 aaa no
111 aaa ok(6 個資料列受到影響)
*/
我列出的字段当中没有id这个字段,所以你第二个问题的答案不能解决我的问题啊