如何與 min(date) 分組並在同一個表中選擇另一列

我有一個查詢返回類似的東西：

Name   Gender Job         date of hire
John   M      mechanic    2012-05-08
John   M      electrician 2010-01-01
Vicky  F      scientific  2012-11-11
Bob    M      NULL        NULL

我需要知道每個人的第一份工作的姓名、性別和工作名稱。但我不知道怎麼做。我的查詢如下所示：

select name,gender,jobname,hiredate
from person p
left join job j on p.personid = j.personid

我在 Microsoft SQL Server 2000 上

我需要這個作為結果：

Name   Gender Job         
John   M      electrician
Vicky  F      scientific 
Bob    M      NULL       

我推斷您的數據如下所示：

人表

╔══════════╦═══════╦════════╗
║ PersonID ║ Name  ║ Gender ║
╠══════════╬═══════╬════════╣
║        1 ║ John  ║ M      ║
║        2 ║ Vicky ║ F      ║
║        3 ║ Bob   ║ M      ║
╚══════════╩═══════╩════════╝

工作表

╔══════════╦═════════════╦════════════╗
║ PersonID ║   JobName   ║  HireDate  ║
╠══════════╬═════════════╬════════════╣
║        1 ║ Electrician ║ 2010-01-01 ║
║        1 ║ Mechanic    ║ 2012-05-08 ║
║        2 ║ Scientific  ║ 2012-11-11 ║
╚══════════╩═════════════╩════════════╝

第一項任務是為每個人找到第一份工作（按僱用日期）。一種巧妙的方法是使用相關子查詢：

SELECT j.*
FROM dbo.Job AS j
WHERE 
   j.HireDate =
   (
       SELECT MIN(j2.HireDate)
       FROM dbo.Job AS j2
       WHERE j2.PersonID = j.PersonID
   );

注意WHERE j2.PersonID = j.PersonID那裡的內部和外部查詢之間的相關性。該查詢的輸出是：

╔══════════╦═════════════╦════════════╗
║ PersonID ║   JobName   ║  HireDate  ║
╠══════════╬═════════════╬════════════╣
║        1 ║ Electrician ║ 2010-01-01 ║
║        2 ║ Scientific  ║ 2012-11-11 ║
╚══════════╩═════════════╩════════════╝

PRIMARY KEY執行計劃（給定一個集群PersonID, HireDate）是：

該計劃的有趣之處在於 Job 表只被掃描一次，儘管在原始查詢中有兩次引用它。該計劃使用我稱之為Segment Top的優化。本質上，執行引擎利用索引順序來檢測新組（段）的開始，並只取每個組的第一行（頂部）。

現在我們有了這個結果，我們需要做的就是將它加入到 Person 表中：

SELECT
   p.PersonName,
   p.Gender, 
   j.JobName
FROM dbo.Person AS p
LEFT JOIN
(
   -- Previous query
   SELECT j.*
   FROM dbo.Job AS j
   WHERE 
       j.HireDate =
       (
           SELECT MIN(j2.HireDate)
           FROM dbo.Job AS j2
           WHERE j2.PersonID = j.PersonID
       )
) AS j ON 
   j.PersonID = p.PersonID
OPTION (MERGE JOIN);

執行計劃是：

OPTION (MERGE JOIN)不是必需的；我剛剛添加它是為了顯示當表包含的行數比這個小範例中更多時您可能會得到的計劃。

---

表定義和範例數據：

CREATE TABLE dbo.Person
(
   PersonID    integer NOT NULL,
   PersonName  varchar(30) NOT NULL,
   Gender      char(1) NOT NULL,

   PRIMARY KEY (PersonID)
);

CREATE TABLE dbo.Job
(
   PersonID    integer NOT NULL,
   JobName     varchar(30) NOT NULL,
   HireDate    datetime NOT NULL,

   PRIMARY KEY (PersonID, HireDate)
);

INSERT dbo.Person
   (PersonID, PersonName, Gender)
SELECT 1, 'John', 'M' UNION ALL
SELECT 2, 'Vicky', 'F' UNION ALL
SELECT 3, 'Bob', 'M';

INSERT dbo.Job
   (PersonID, JobName, HireDate)
SELECT 1, 'Mechanic', '20120508' UNION ALL
SELECT 1, 'Electrician', '20100101' UNION ALL
SELECT 2, 'Scientific', '20121111';

引用自：https://dba.stackexchange.com/questions/29789