表结构如下
字段1 字段2 字段3
1 aaa bbaaa
2 bbaaa bbaaa
3 ccc bbccc
4 bbccc bbccc
5 ddd bbddd要求结果如下:2 bbaaa bbaaa
4 bbccc bbccc
5 ddd bbddd字段3相同的记录取 字段2长的那条!谢谢了
字段1 字段2 字段3
1 aaa bbaaa
2 bbaaa bbaaa
3 ccc bbccc
4 bbccc bbccc
5 ddd bbddd要求结果如下:2 bbaaa bbaaa
4 bbccc bbccc
5 ddd bbddd字段3相同的记录取 字段2长的那条!谢谢了
from tb t
where not exists(select 1 from tb where col3=t.col3 and len(col2)>len(t.col2))
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col1] int,[col2] varchar(5),[col3] varchar(5))
insert [tb]
select 1,'aaa','bbaaa' union all
select 2,'bbaaa','bbaaa' union all
select 3,'ccc','bbccc' union all
select 4,'bbccc','bbccc' union all
select 5,'ddd','bbddd'
---查询---
select *
from tb t
where not exists(select 1 from tb where col3=t.col3 and len(col2)>len(t.col2))---结果---
col1 col2 col3
----------- ----- -----
2 bbaaa bbaaa
4 bbccc bbccc
5 ddd bbddd(3 行受影响)