34,593
社区成员
发帖
与我相关
我的任务
分享
select *
from table i
where exists
(
select 1
from
(
select no, partno from table group by no, partno having count(*) = 1
) t
where t.no = i.no and t.partno = i.partno
)
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-06 15:30:25
---------------------------------
--> 生成测试数据表-tb
if not object_id('tb') is null
drop table tb
Go
Create table tb([no] int,[partno] int,[other] nvarchar(5),[num1] int)
Insert tb
select 1,2,'abcde',2 union all
select 1,2,'mmm',0 union all
select 1,3,'kkk',1 union all
select 1,4,'mkl',5
Go
--Select * from tb
-->SQL查询如下:
select *
from tb t
where not exists(
select 1
from tb
where [no]=t.[no] and [partno]=t.[partno]
group by [no],[partno]
having count(1)>1)
/*
no partno other num1
----------- ----------- ----- -----------
1 3 kkk 1
1 4 mkl 5
(2 行受影响)
*/
DECLARE @T TABLE(no INT,partno INT,other VARCHAR(10),num1 INT)
INSERT @T SELECT 1 , 2, 'abcde' , 2
INSERT @T SELECT 1 , 2 , 'mmm' , 0
INSERT @T SELECT 1 , 3 , 'kkk' , 1
INSERT @T SELECT 1, 4 , 'mkl' , 5
SELECT * FROM @T A,(
SELECT no,partno FROM @T GROUP BY no,partno HAVING COUNT(*)=1)B
WHERE A.no=B.no AND A.partno=B.partno
/*no partno other num1 no partno
----------- ----------- ---------- ----------- ----------- -----------
1 3 kkk 1 1 3
1 4 mkl 5 1 4
(2 個資料列受到影響)*/
if object_id('[tab]') is not null drop table [tab]
create table [tab]([no] int,[partno] int,[other] varchar(5),[num1] int)
insert [tab]
select 1,2,'abcde',2 union all
select 1,2,'mmm',0 union all
select 1,3,'kkk',1 union all
select 1,4,'mkl',5
select * from [tab] t where not exists(select 1 from tab where t.partno=partno group by partno having count(partno)>1)
/*
no partno other num1
----------- ----------- ----- -----------
1 3 kkk 1
1 4 mkl 5
(所影响的行数为 2 行)
*/
drop table tab
SELECT * FROM TB A,(
SELECT no,partno FROM TB GROUP BY no,partno HAVING COUNT(*)=1)B
WHERE A.no=B.no AND A.partno=B.partno
--3、删除重复记录没有大小关系时,处理重复值
--> --> (Roy)生成測試數據
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([Num] int,[Name] nvarchar(1))
Insert #T
select 1,N'A' union all
select 1,N'A' union all
select 1,N'A' union all
select 2,N'B' union all
select 2,N'B'
Go
方法1:
if object_id('Tempdb..#') is not null
drop table #
Select distinct * into # from #T--排除重复记录结果集生成临时表#
truncate table #T--清空表
insert #T select * from # --把临时表#插入到表#T中
--查看结果
select * from #T
/*
Num Name
----------- ----
1 A
2 B
(2 行受影响)
*/
--重新执行测试数据后用方法2
方法2:
alter table #T add ID int identity--新增标识列
go
delete a from #T a where exists(select 1 from #T where Num=a.Num and Name=a.Name and ID>a.ID)--只保留一条记录
go
alter table #T drop column ID--删除标识列
--查看结果
select * from #T
/*
Num Name
----------- ----
1 A
2 B
(2 行受影响)
*/
--重新执行测试数据后用方法3
方法3:
declare Roy_Cursor cursor local for
select count(1)-1,Num,Name from #T group by Num,Name having count(1)>1
declare @con int,@Num int,@Name nvarchar(1)
open Roy_Cursor
fetch next from Roy_Cursor into @con,@Num,@Name
while @@Fetch_status=0
begin
set rowcount @con;
delete #T where Num=@Num and Name=@Name
set rowcount 0;
fetch next from Roy_Cursor into @con,@Num,@Name
end
close Roy_Cursor
deallocate Roy_Cursor
--查看结果
select * from #T
/*
Num Name
----------- ----
1 A
2 B
(2 行受影响)
*/