表A,字段如下:
A B C D E
值: a1 b1 c1 d1 e1
a1 b1 c1 d1 e1
a1 b1 c2 d2 e1
a2 b2 c1 d1 e1 a3 b3 c2 d2 e2
想要得到只要字段A、B 相等,只保留任意一条记录,如下:
a1 b1 c1 d1 e1
a2 b2 c1 d1 e1
a3 b3 c2 d2 e2
如何处理能够得到上面的结果啊?
A B C D E
值: a1 b1 c1 d1 e1
a1 b1 c1 d1 e1
a1 b1 c2 d2 e1
a2 b2 c1 d1 e1 a3 b3 c2 d2 e2
想要得到只要字段A、B 相等,只保留任意一条记录,如下:
a1 b1 c1 d1 e1
a2 b2 c1 d1 e1
a3 b3 c2 d2 e2
如何处理能够得到上面的结果啊?
http://blog.csdn.net/htl258/archive/2009/04/16/4083040.aspx
select 1 from tb where A=a.a and B=a.b and checksum(c,d,e)>checksum(a.c,a.d,a.e)
)
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-21 10:31:26
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([A] varchar(2),[B] varchar(2),[C] varchar(2),[D] varchar(2),[E] varchar(2))
insert [tb]
select 'a1','b1','c1','d1','e1' union all
select 'a1','b1','c1','d1','e1' union all
select 'a1','b1','c2','d2','e1' union all
select 'a2','b2','c1','d1','e1' union all
select 'a3','b3','c2','d2','e2'
--------------开始查询--------------------------
select
distinct *
from
tb a
where
not exists( select 1 from tb where A=a.a and B=a.b and checksum(c,d,e)>checksum(a.c,a.d,a.e) )
----------------结果----------------------------
/* A B C D E
---- ---- ---- ---- ----
a1 b1 c1 d1 e1
a2 b2 c1 d1 e1
a3 b3 c2 d2 e2(3 行受影响)*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([A] varchar(2),[B] varchar(2),[C] varchar(2),[D] varchar(2),[E] varchar(2))
insert [tb]
select 'a1','b1','c1','d1','e1' union all
select 'a1','b1','c1','d1','e1' union all
select 'a1','b1','c2','d2','e1' union all
select 'a2','b2','c1','d1','e1' union all
select 'a3','b3','c2','d2','e2'---SQL2005
;with szy as
(
select *,px=row_number()over(partition by A,B order by newid())
from tb
)select * from szy where px=1A B C D E px
---- ---- ---- ---- ---- --------------------
a1 b1 c1 d1 e1 1
a2 b2 c1 d1 e1 1
a3 b3 c2 d2 e2 1(3 行受影响)