22,210
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:[a1]
if object_id('[a1]') is not null drop table [a1]
create table [a1]([id] int,[dept] varchar(6),[age] int)
insert [a1]
select 4,'技术部',25 union all
select 1,'营销部',33 union all
select 4,'营销部',41 union all
select 2,'技术部',21 union all
select 2,'营销部',30 union all
select 1,'技术部',25 union all
select 5,'人事部',25 union all
select 2,'人事部',36 union all
select 6,'营销部',24 union all
select 5,'技术部',29 union all
select 4,'人事部',33 union all
select 6,'技术部',27 union all
select 1,'人事部',32
select * from [a1]
SELECT * FROM a1 A WHERE id in(SELECT TOP 2 id FROM a1 WHERE dept = A.dept ORDER BY age desc)
/*id dept age
1 营销部 33
4 营销部 41
2 人事部 36
5 技术部 29
4 人事部 33
6 技术部 27*/
--建立测试环境
IF OBJECT_ID('a','U') IS NOT NULL DROP TABLE a
GO
CREATE TABLE a
(
id int,
dept varchar(10),
age int
)
GO
insert a
select 4 ,'技术部', 25 union all
select 1 ,'营销部', 33 union all
select 4 ,'营销部', 41 union all
select 2 ,'技术部', 21 union all
select 2 ,'营销部', 30 union all
select 1 ,'技术部', 25 union all
select 5 ,'人事部', 25 union all
select 2 ,'人事部', 36 union all
select 6 ,'营销部', 24 union all
select 5 ,'技术部', 29 union all
select 4 ,'人事部', 33 union all
select 6 ,'技术部', 27 union all
select 1 ,'人事部', 32
go
select * from a t
where (select count(1) from a where dept=t.dept and age>t.age)<=1
order by dept,age desc
/*
id dept age
----------- ---------- -----------
5 技术部 29
6 技术部 27
2 人事部 36
4 人事部 33
4 营销部 41
1 营销部 33
(6 行受影响)
*/
--> 测试数据:[TA]
if object_id('[TA]') is not null drop table [TA]
create table [TA]([id] int,[dept] varchar(6),[age] int)
insert [TA]
select 4,'技术部',25 union all
select 1,'营销部',33 union all
select 4,'营销部',41 union all
select 2,'技术部',21 union all
select 2,'营销部',30 union all
select 1,'技术部',25 union all
select 5,'人事部',25 union all
select 2,'人事部',36 union all
select 6,'营销部',24 union all
select 5,'技术部',29 union all
select 4,'人事部',33 union all
select 6,'技术部',27 union all
select 1,'人事部',32
select * from [TA] A
where (select count(dept) from TA where [dept]=A.[dept] and age<A.age)<2
order by [dept]
/*
id dept age
----------- ------ -----------
4 技术部 25
2 技术部 21
1 技术部 25
5 人事部 25
1 人事部 32
6 营销部 24
2 营销部 30
(7 行受影响)
*/
drop table [TA]
select id, dept, age from (select * ,[order]=row_number()over(partition by dept order by age desc) from A ) T where T.[order]<3
select id, dept, age from (select * ,[order]=row_number()over(partition by dept order by age desc) from A ) A where A.[order]<3
select * from a as t where id in(select top 2 ID from a where dept=t.dept order by age desc)