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 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) */
with cte as ( select ROW_NUMBER()over(partition by 部门id order by 部门id) as num ,*from tab ) select * from cte where num=1
;with tab as( select rid=row_number() over(partition by 部门ID order by 薪水 desc),* from TB ) select * from tab where rid=1
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])
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*/
(员工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)
*/
(
select ROW_NUMBER()over(partition by 部门id order by 部门id) as num ,*from tab
)
select * from cte
where num=1
;with tab as(
select rid=row_number() over(partition by 部门ID order by 薪水 desc),* from TB
)
select * from tab where rid=1