34,594
社区成员
发帖
与我相关
我的任务
分享
declare @T table([content] varchar(7),[sCount] int,[eid] int)
insert @T
select 'asd',1,1 union all
select 'sdfsdf',1,1 union all
select 'abc',2,1 union all
select 'adcd',3,1 union all
select 'asdfsdf',11,2 union all
select 'sdfsfd',22,2 union all
select 'asdf',33,2 union all
select 'ss',111,3 union all
select 'ssss',222,3 union all
select 'ss',333,3 union all
select 'ss',444,3
--sql 2000办法
select *
from @T t
where not exists
(
select 1
from @T
where eid=t.eid
and scount<t.scount
)
content sCount eid
------- ----------- -----------
asd 1 1
sdfsdf 1 1
asdfsdf 11 2
ss 111 3
(4 行受影响)
--SQL 2005 办法
select content,sCount,eid
from
(
select *,rank=rank() over(partition by [eid] order by [sCount])
from @T
) A
where rank=1
content sCount eid
------- ----------- -----------
asd 1 1
sdfsdf 1 1
asdfsdf 11 2
ss 111 3
(4 行受影响)
SELECT ID=IDENTITY(INT,1,1),* INTO #TB FROM TB
SELECT content,sCount, eid FROM #TB T WHERE NOT EXISTS(SELECT 1 FROM #TB WHERE EID=T.EID AND T.ID>ID)
select * from tb t where not exists (select 1 from tb where eid=t.eid and scount<=t.scount)
select * from #T t
where not exists(select 1 from #T where t.content=content and t.eid>eid)
select * from #T t
where eid =(select min(eid) from #T where t.content = content )
select * from tab t where not exists(select 1 from tab where eid=t.eid and scount<t.scount)
select content,sCount,eid
from
(
select *,rank=rank() over(partition by [eid] order by [sCount])
from @T
) A
where rank=1
select max(content),sCount,eid from @tb t where not exists(select 1 from @tb where eid=t.eid and scount <t.scount)
group by eid,sCount
;with leno as
(
select content,Scount,edid,row_number() over(partition by eid order by scount ) Rownum
from Tab
)
select * from leno where Rownum = 1
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([content] varchar(7),[sCount] int,[eid] int)
insert [tb]
select 'asd',1,1 union all
select 'sdfsdf',1,1 union all
select 'abc',2,1 union all
select 'adcd',3,1 union all
select 'asdfsdf',11,2 union all
select 'sdfsfd',22,2 union all
select 'asdf',33,2 union all
select 'ss',111,3 union all
select 'ssss',222,3 union all
select 'ss',333,3 union all
select 'ss',444,3
go
--select * from [tb]
select content,scount,eid
from tb t
where not exists(select 1 from tb where eid=t.eid and (scount<t.scount or scount=t.scount and content<t.content))
/*
content scount eid
------- ----------- -----------
asd 1 1
asdfsdf 11 2
ss 111 3
(3 行受影响)
*/
SELECT ID=IDENTITY(INT,1,1),* INTO #TB FROM TB
SELECT content,sCount, eid FROM #TB T WHERE NOT EXISTS(SELECT 1 FROM #TB WHERE EID=T.EID AND T.ID>ID)
not exists(select * from tab where eid=t.eid and scount<t.scount)