求一条分组查询最大值的sql语句
表结构如下
员工id（主键） 部门id 薪水
1 1 2
2 1 1
3 2 5
4 2 6
5 3 3
我要得到各部门最好薪水的员工，即查询结果为
员工id（主键） 部门id 薪水
1 1 2
4 2 6
5 3 3
求该sql语句
[解决办法]
create table xz
(员工id int,部门id int,薪水 int)
insert into xz
select 1,1,2 union all
select 2,1,1 union all
select 3,2,5 union all
select 4,2,6 union all
select 5,3,3
select a.员工id,a.部门id,a.薪水
from xz a
inner join
(select 部门id,max(薪水) '薪水'
from xz group by 部门id) b on a.部门id=b.部门id and a.薪水=b.薪水
order by a.部门id
/*
员工id 部门id 薪水
----------- ----------- -----------
1 1 2
4 2 6
5 3 3
(3 row(s) affected)
*/
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-10-22 11:23:26
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([员工id] int,[部门id] int,[薪水] int)
insert [huang]
select 1,1,2 union all
select 2,1,1 union all
select 3,2,5 union all
select 4,2,6 union all
select 5,3,3
--------------开始查询--------------------------
select * from [huang]a
WHERE EXISTS(SELECT 1 FROM (SELECT [部门id],MAX(薪水)薪水 FROM huang GROUP BY [部门id])b
WHERE a.薪水=b.薪水 AND a.[部门id]=b.[部门id])
----------------结果----------------------------
/*
员工id 部门id 薪水
----------- ----------- -----------
1 1 2
4 2 6
5 3 3
*/
create table xz
(员工id int,部门id int,薪水 int)
insert into xz
select 1,1,2 union all
select 2,1,1 union all
select 3,2,5 union all
select 4,2,6 union all
select 5,3,3
select 员工id,部门id,薪水
from xz a
where not exists
(select 1 from xz b
where b.部门id=a.部门id and b.薪水>a.薪水)
order by 部门id
/*
员工id 部门id 薪水
----------- ----------- -----------
1 1 2
4 2 6
5 3 3
(3 row(s) affected)
*/
