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]也就是根据编号进行过滤,如果相同编号就取任意一条数据
[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]也就是根据编号进行过滤,如果相同编号就取任意一条数据
group by [bh]
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
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 2000,如果某个字段能根据bh区分大小,例如email
select 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 t
select 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)
就是没有唯一字段啊,按照你这样写的话会重复的
13 zhang 1 [email protected]
13 zhang 1 [email protected]我现在是要把这个写在视图里面,视图里面不可以用临时表!
--查询
select bh , max(name) name , max(sex) sex , max(email) emial from testdt group by bh
--创建视图
create view my_view as
select bh , max(name) name , max(sex) sex , max(email) emial from testdt group by bh