- SQL code
CREATE TABLE [dbo].[testDT]( [bh] [varchar](50) NULL, [name] [varchar](50) NULL, [sex] [int] NULL, [email] [varchar](50) NULL)
内容
12 li 1 [email protected]
12 li 1 [email protected]
15 wang 0 [email protected]
13 zhang 1 [email protected]
13 zhang 1 [email protected]
想要的结果
12 li 1 [email protected]
15 wang 0 [email protected]
13 zhang 1 [email protected]
也就是根据编号进行过滤，如果相同编号就取任意一条数据
------解决方案--------------------
- SQL code
select [bh],min(name) name,min(sex) sex,min(email) email from [testDT]group by [bh]
------解决方案--------------------
--sql 2005
select bh , name , sex , email from
(
select t.* , row_number() over(partition by bh order by name , sex , email) px from testdt t
) m
where px = 1
------解决方案--------------------
- SQL code
--sql 2005select bh , name , sex , email from( select t.* , row_number() over(partition by bh order by name , sex , email) px from testdt t) mwhere px = 1--sql 2000，如果某个字段能根据bh区分大小，例如emailselect t.* from testdt t where email = (select min(email) from testdt where bh = t.bh)select t.* from testdt t where not exists (select 1 from testdt where bh = t.bh and email < t.email)--如果没有任何一个（多个）字段能根据bh区分大小。则需要使用临时表select t.* , px = identity(int,1,1) into tmp from testdt tselect t.bh , t.name , t.sex , t.email from tmp t where px = (select min(px) from tmp where bh = t.bh)select t.bh , t.name , t.sex , t.email from tmp t where not exists (select 1 from tmp where bh = t.bh and px < t.px)