表结构如下
员工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
*/
------解决方案--------------------
方法2,
create table xz
(员工id int,部门id int,薪水 int)
insert into xz
select 1,1,2 union all