如表Table1中有如下记录
Name TypeID
a -1
b -1
b 1
c -1
d -1
d 1如果存在多个相同Name,去掉TypeID为-1的记录,结果如下
a -1
b 1
c -1
d 1请教高手该SQL语句如何写
Name TypeID
a -1
b -1
b 1
c -1
d -1
d 1如果存在多个相同Name,去掉TypeID为-1的记录,结果如下
a -1
b 1
c -1
d 1请教高手该SQL语句如何写
if object_id('[tt]') is not null drop table [tt]
go
create table [tt] (Name varchar(1),TypeID int)
insert into [tt]
select 'a',-1 union all
select 'b',-1 union all
select 'b',1 union all
select 'c',-1 union all
select 'd',-1 union all
select 'd',1select * from [tt] t
where not exists(select * from [tt] where Name=t.Name and TypeID>t.TypeID)
--找出相同name中TypeID 最大的记录
--如果TypeID 的-1 是最大的话,将会选出-1
Name TypeID
---- -----------
a -1
b 1
c -1
d 1(4 row(s) affected)
select Name, max(TypeID) as TypeID from tb group by Name
create table #Test(name varchar(10), TypeID int)
--数据
insert into #Test
select 'a',-1 union all
select 'b',-1 union all
select 'b',1 union all
select 'c',-1 union all
select 'd',-1 union all
select 'd',1
--显示
select name,max(TypeID) as TypeID from #test group by namename TypeID
---------- -----------
a -1
b 1
c -1
d 1
;with t as (select name,typeid,count(name)over(partition by name ) as cnt from tt)
select name,typeid from t
where cnt <2 or (typeid<>-1 and cnt>=2 )
create table t1
(
cname varchar(2),
iid int
)
insert into t1
select 'a', -1 union all
select 'b', -1 union all
select 'b', 1 union all
select 'c', -1 union all
select 'd', -1 union all
select 'd', 1select * from
(select row_number() over(partition by cname order by cname,iid desc) as rowindex,* from t1) a
where a.rowindex=1