如何取出最大值?

mryouhao 2015-08-01 08:51:02
如下所示:
Scount MkName Pid
18 A069 31547222
2 A072 31547222
12 A069 31522430
3 A068 31522430
5 A072 31522430
6 A070 31664001
2 A074 31664001
16 A075 31664001
结果如下:
Scount MkName Pid
18 A069 31547222
12 A069 31522430
16 A075 31664001
...全文
68 4 打赏 收藏 转发到动态 举报
写回复
用AI写文章
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
mryouhao 2015-08-01
  • 打赏
  • 举报
回复
http://www.cnblogs.com/zfanlong1314/p/3393946.html
  • 打赏
  • 举报
回复
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

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧