SQL语句寻求解决方法
人员表
PersonId+PersonName+CorpId+PersonWage
1 +张1 + 1 +1200
2 +张2 + 1 +1900
3 +张3 + 2 +1800
4 +张4 + 3 +1500
5 +张5 + 2 +1100
6 +张6 + 4 +1500
7 +张7 + 3 +1300
我想查询每个单位的最好工资，而且还要查询到这个人的姓名
结果为
PersonId+PersonName+CorpId+PersonWage
2 +张2 + 1 +1900
3 +张3 + 2 +1800
4 +张4 + 3 +1500
6 +张6 + 4 +1500
谁能给个SQL语句
问题点数：20、回复次数：8Top
1 楼dulei115（前途无亮）回复于 2006-06-01 12:33:41 得分 20
select *
from 人员表 a
where PersonWage in (select max(PersonWage)
from 人员表 where CorpId = a.CorpId)Top
2 楼lxzm1001（*~悠悠蓝星梦~*）回复于 2006-06-01 12:38:01 得分 0
create table 人员表(PersonId int,PersonName varchar(20),CorpId int,PersonWage int)
insert 人员表 select 1,'张1',1,1200
union all select 2,'张2',1,1900
union all select 3,'张3',2,1800
union all select 4,'张4',3,1500
union all select 5,'张5',2,1100
union all select 6,'张6',4,1500
union all select 7,'张7',3,1300
go
select * from 人员表 t where not exists(select 1 from 人员表 where CorpId=t.CorpId and PersonWage>t.PersonWage)
go
select * from 人员表 t where(select count(1) from 人员表 where CorpId=t.CorpId and PersonWage>=t.PersonWage)<=1
go
select t.* from 人员表 t,(select corpid,max(personwage) as personwage from 人员表 group by corpid) p
where t.corpid=p.corpid and t.personwage=p.personwage order by t.personidTop
3 楼dulei115（前途无亮）回复于 2006-06-01 12:39:01 得分 0
if object_id('人员表') is not null drop table 人员表
select 1 as Personid, '张1' as Personname, 1 as CorpId, 1200 as PersonWage
into 人员表
union select 2, '张2', 1, 1900
union select 3, '张3', 2, 1800
union select 4, '张4', 3, 1500
union select 5, '张5', 2, 1100
union select 6, '张6', 4, 1500
union select 7, '张7', 3, 1300
--------------------------------------------------------
select *
from 人员表 a
where PersonWage in (select max(PersonWage)
from 人员表 where CorpId = a.CorpId)
order by Personid
/*
Persoid personname corpId PersonWage
2 张2 1 1900
3 张3 2 1800
4 张4 3 1500
6 张6 4 1500
*/
--------------------------------------------------------
drop table 人员表Top
4 楼jasonren（jason）回复于 2006-06-01 13:37:23 得分 0
select *
from 人员表 a
where PersonWage in (select max(PersonWage)
from 人员表 where CorpId = a.CorpId)
方便,简单Top
5 楼n29882942（骑牛上北京）回复于 2006-06-01 13:41:41 得分 0
select *
from 人员表 a
where PersonWage in (select max(PersonWage)
from 人员表 where CorpId = a.CorpId)
这个好！Top
6 楼fzycool（风之羽）回复于 2006-06-01 14:12:57 得分 0
select * from 人员表 group by CorpId order by PersonWage
试试这个吧,应该能实现.Top
7 楼fzycool（风之羽）回复于 2006-06-01 14:13:42 得分 0
顺序错了的话就把order by 换个顺序.Top
8 楼mylover002（靠近您,温暖我!）回复于 2006-06-01 14:36:21 得分 0
select t1.PersonId,t1.PersonName,t2.CorpId,t2.PersonWage
from 人员表 t1
inner join (select CorpId,PersonWage=max(PersonWage) from 人员表 group by CorpID) t2
on t1.CorpId=t2.CorpId and t1.PersonWage=t2.PersonWage
order by t1.CorpIDTop
