34,575
社区成员
发帖
与我相关
我的任务
分享
create table #tmp(Scount int,MkName char(5),Pid char(10) )
create table #tmp1(Scount char(5),Pid char(10))
insert into #tmp(Scount,MkName,Pid) values (18, 'A069', '31547222')
insert into #tmp(Scount,MkName,Pid) values (2, 'A072', '31547222')
insert into #tmp(Scount,MkName,Pid) values (12 , 'A069', '31522430')
insert into #tmp(Scount,MkName,Pid) values (3, 'A068', '31522430')
insert into #tmp(Scount,MkName,Pid) values (5, 'A072', '31522430')
insert into #tmp(Scount,MkName,Pid) values (6 , 'A070', '31664001')
insert into #tmp(Scount,MkName,Pid) values (2, 'A074', '31664001')
insert into #tmp(Scount,MkName,Pid) values (16 , 'A075', '31664001' )
insert into #tmp1(Pid,Scount)
select distinct Pid ,MAX(Scount) from #tmp
group by pid
select #tmp.Scount,#tmp.MkName,#tmp.Pid from #tmp,#tmp1 where #tmp.Pid=#tmp1.Pid and #tmp.Scount=#tmp1.Scount
drop table #tmp
drop table #tmp1
原谅我是渣渣,语句不优化,不过你想要的结果是有的,纯手打,赶紧给分create table #tmp(Scount int,MkName char(5),Pid char(10) )
insert into #tmp(Scount,MkName,Pid) values (18, 'A069', '31547222')
insert into #tmp(Scount,MkName,Pid) values (2, 'A072', '31547222')
insert into #tmp(Scount,MkName,Pid) values (12 , 'A069', '31522430')
insert into #tmp(Scount,MkName,Pid) values (3, 'A068', '31522430')
insert into #tmp(Scount,MkName,Pid) values (5, 'A072', '31522430')
insert into #tmp(Scount,MkName,Pid) values (6 , 'A070', '31664001')
insert into #tmp(Scount,MkName,Pid) values (2, 'A074', '31664001')
insert into #tmp(Scount,MkName,Pid) values (16 , 'A075', '31664001' )
select distinct Pid ,MAX(Scount) from #tmp
group by pid
drop table #tmp
select MAX(Scount),MkName,Pid from table1 where pid in (
select distinct pid from table1 )
group by pid