22,209
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('dbo.[emp]') IS NOT NULL
DROP TABLE dbo.[emp]
GO
CREATE TABLE dbo.[emp](
[eid] INT
,[score] INT
,[did] INT
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[emp] VALUES(N'1',N'90',N'1')
INSERT INTO dbo.[emp] VALUES(N'2',N'80',N'1')
INSERT INTO dbo.[emp] VALUES(N'3',N'72',N'1')
INSERT INTO dbo.[emp] VALUES(N'4',N'90',N'2')
INSERT INTO dbo.[emp] VALUES(N'5',N'80',N'2')
INSERT INTO dbo.[emp] VALUES(N'6',N'72',N'2')
GO
IF OBJECT_ID('dbo.[lev]') IS NOT NULL
DROP TABLE dbo.[lev]
GO
CREATE TABLE dbo.[lev](
[did] INT
,[score] INT
,[title] NVARCHAR(10)
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[lev] VALUES(N'1',N'90',N'优秀')
INSERT INTO dbo.[lev] VALUES(N'1',N'80',N'良好')
INSERT INTO dbo.[lev] VALUES(N'1',N'70',N'中等')
INSERT INTO dbo.[lev] VALUES(N'1',N'60',N'及格')
INSERT INTO dbo.[lev] VALUES(N'2',N'85',N'良好')
INSERT INTO dbo.[lev] VALUES(N'2',N'60',N'及格')
------ 以上为测试表及测试数据 --------
SELECT emp.*,f.title
FROM emp OUTER APPLY (
SELECT TOP 1 lev.title FROM dbo.lev WHERE emp.did=lev.did AND emp.score>=lev.score ORDER BY lev.score DESC
) AS f
/*
eid score did title
1 90 1 优秀
2 80 1 良好
3 72 1 中等
4 90 2 良好
5 80 2 及格
6 72 2 及格
*/
create table students(sid int ,score int,did int )
insert into students
select 1,90,1
union
select 2,80,1
union
select 3,72,1
union
select 4,90,2
union
select 5,80,2
union
select 6,72,2
create table degres(id int ,score int,title varchar(20))
insert into degres
select 1,90,'优秀'
union
select 1,80,'良好'
union
select 1,70,'中等'
union
select 1,60,'及格'
union
select 2,85,'良好'
union
select 2,60,'及格'
select a.sid,a.score,a.did,a.title from (
select s.*,d.title ,
row_number() over(partition by s.sid order by d.score desc) as n
from students as s
left join degres as d on s.did=d.id
where s.score>=d.score
)as a where n=1