求解答
uj5u.com熱心網友回復：
我自己做出來了，太棒了uj5u.com熱心網友回復：
USE tempdb
GO
IF OBJECT_ID('project') IS NOT NULL
DROP TABLE project
GO
CREATE TABLE project(
project_id INT,
employee_id INT
)
GO
INSERT INTO project VALUES(1,1)
INSERT INTO project VALUES(1,2)
INSERT INTO project VALUES(1,3)
INSERT INTO project VALUES(2,1)
INSERT INTO project VALUES(2,4)
GO
IF OBJECT_ID('employee') IS NOT NULL
DROP TABLE employee
GO
CREATE TABLE employee(
employee_id INT,
[name] NVARCHAR(20),
experience_years INT
)
GO
INSERT INTO employee VALUES (1,'Khaled',3)
INSERT INTO employee VALUES (2,'Ali',2)
INSERT INTO employee VALUES (3,'John',3)
INSERT INTO employee VALUES (4,'Doe',2)
GO
SELECT *
FROM (
SELECT Rank() OVER (PARTITION BY p.project_id ORDER BY e.experience_years DESC ) AS rid
,p.project_id
,e.employee_id
,e.[name]
,e.experience_years
FROM project AS p INNER JOIN employee AS e ON p.employee_id=e.employee_id
) AS t
WHERE t.rid=1
/*
rid project_id employee_id name experience_years
-------------------- ----------- ----------- -------------------- ----------------
1 1 1 Khaled 3
1 1 3 John 3
1 2 1 Khaled 3
*/
uj5u.com熱心網友回復：
謝謝你，你是用mysql做的嗎
uj5u.com熱心網友回復：
SQL server 論壇當然是寫SQL server 。不過MySQL 也一樣，8.0或以上有一樣的排名函式
uj5u.com熱心網友回復：
我看你建表和我不太一樣所以問問
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/101941.html
標籤：疑難問題