人员表
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语句
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语句
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.personid
from 人员表
where exists(select 1 from (select CorpId,max(PersonWage) as PersonWage from 人员表 group by CorpId) T where CorpId=T.CorpId and PersonWage=T.PersonWage)
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 人员表
FROM 人员表
WHERE personwage in (SELECT max(personwage) FROM 人员表 group by CorpId)