看下表,当字段一有重复值时,比较字段二,当字段二有重复值时,比较字段三,选出唯一的。这种多重重复的SQL查询语句如何写?谢谢字段一,字段二,字段三
A,a,1
A,a,1
A,b,2
A,c,1
A,a,2
A,a,3
A,b,1
A,c,1
A,c,2
B,c,1
B,a,2
B,b,1
C,c,1
D,a,1
查询结果:字段一,字段二,字段三A,a,1
A,b,2
A,c,1
A,a,2
A,a,3
A,b,1
A,c,2
B,c,1
B,a,2
B,b,1
C,c,1
D,a,1
A,a,1
A,a,1
A,b,2
A,c,1
A,a,2
A,a,3
A,b,1
A,c,1
A,c,2
B,c,1
B,a,2
B,b,1
C,c,1
D,a,1
查询结果:字段一,字段二,字段三A,a,1
A,b,2
A,c,1
A,a,2
A,a,3
A,b,1
A,c,2
B,c,1
B,a,2
B,b,1
C,c,1
D,a,1
(
select id=row_number()over(order by getdate()),* from tb
)select
*
from
f t
where
not exists(select 1 from tb where 字段1=t.字段1 and (字段2<t.字段2 or (字段2=t.字段2 and 字段3<t.字段3) or (字段2=t.字段2 and 字段3=t.字段3 and id<t.id)
(
select id=row_number()over(order by getdate()),* from tb
)select
*
from
f t
where
not exists(select 1 from tb where 字段1=t.字段1 and (字段2<t.字段2) or (字段2=t.字段2 and 字段3<t.字段3) or (字段2=t.字段2 and 字段3=t.字段3 and id<t.id))
if(object_id('a')is not null) drop table a
go
create table a
(
a varchar(1),
b varchar(1),
c int
)
go
insert into a
select 'A','a',1 union all
select 'A','a',1 union all
select 'A','b',2 union all
select 'A','c',1 union all
select 'A','a',2 union all
select 'A','a',3 union all
select 'A','b',1 union all
select 'A','c',1 union all
select 'A','c',2 union all
select 'B','c',1 union all
select 'B','a',2 union all
select 'B','b',1 union all
select 'C','c',1 union all
select 'D','a',1select a,b,c from a group by a,b,c order by a,c,b
/*
a b c
---- ---- -----------
A a 1
A b 1
A c 1
A a 2
A b 2
A c 2
A a 3
B b 1
B c 1
B a 2
C c 1
D a 1(12 行受影响)
*/
;with T as(
select distinct 字段一,字段二,字段三 from tb
)
insert into 待插入数据的表名(字段一,字段二,字段三)
select * from T