--对第一个字段分组,第二个字段取min()如: select 字段1, min(字段2) as 字段 from 表名 group by 字段1
--如果表中有唯一字段ID,可以这么处理: select * from 表名 as t where id = (select min(id) from 表名 where 字段1=t.字段1)
select * from t1 b where (select count(*) from t1 a where col1=b.col1 and col2<=b.col2 )=1 order by col2
表: create table t1(col1 varchar(20),col2 int) insert t1 select 'a', 1 union all select 'a', 2 union all select 'b', 1 union all select 'b' , 2
create table t1(col1 varchar(20),col2 int) insert t1 select 'a', 1 union all select 'a', 2 union all select 'b', 1 union all select 'b' , 2
select * from 表 ta where not exists (select 1 from 表 where 字段1=ta.字段1 and 字段2<ta.字段)
select * from 表 ta where not exists (select 1 from 表 where 字段1=ta.字段1 and 字段2<ta.字段2)
--因为有重复记录,所以用如下查,取到的不是1条,所以建议增加唯一ID字段create table 表(col1 varchar(20),col2 int) insert 表 select 'a', 1 insert 表 select 'a', 1 union all select 'a', 2 union all select 'b', 1 union all select 'b' , 2 select * from 表 ta where not exists (select 1 from 表 where col1=ta.col1 and col2<ta.col2) drop table 表 /*a 1 a 1 b 1 */
--如果表中有唯一字段ID,可以这么处理:select * from 表名 as t where id = (select min(id) from 表名 where col1=t.col1)
buptseoly() ( ) 信誉:100 Blog 加为好友 2007-4-12 0:14:33 得分: 0
没有唯一字段ID
------- 那就借用臨時表Select RowID = Identity(Int, 1, 1), * Into #T From TableName Select * From #T A Where RowID = (Select Min(RowID) From #T Where Col1 = A.Col1) Drop Table #T
select 字段1, min(字段2) as 字段
from 表名
group by 字段1
select *
from 表名 as t
where id = (select min(id) from 表名 where 字段1=t.字段1)
select * from t1 b where (select count(*) from t1 a
where col1=b.col1 and col2<=b.col2
)=1 order by col2
create table t1(col1 varchar(20),col2 int)
insert t1 select 'a', 1
union all select 'a', 2
union all select 'b', 1
union all select 'b' , 2
insert t1 select 'a', 1
union all select 'a', 2
union all select 'b', 1
union all select 'b' , 2
where not exists
(select 1 from 表 where 字段1=ta.字段1 and 字段2<ta.字段)
where not exists
(select 1 from 表 where 字段1=ta.字段1 and 字段2<ta.字段2)
--因为有重复记录,所以用如下查,取到的不是1条,所以建议增加唯一ID字段create table 表(col1 varchar(20),col2 int)
insert 表 select 'a', 1
insert 表 select 'a', 1
union all select 'a', 2
union all select 'b', 1
union all select 'b' , 2
select * from 表 ta
where not exists
(select 1 from 表 where col1=ta.col1 and col2<ta.col2)
drop table 表
/*a 1
a 1
b 1
*/
col1是企业名称,后面的一些字段都是地址之类的信息
企业名称可能有重复
想对企业名称去重,每个企业只保留一条记录
一个sql如何实现
from 表名 as t
where id = (select min(id) from 表名 where col1=t.col1)
没有唯一字段ID
-------
那就借用臨時表Select RowID = Identity(Int, 1, 1), * Into #T From TableName
Select * From #T A Where RowID = (Select Min(RowID) From #T Where Col1 = A.Col1)
Drop Table #T