對sql作業的總結（PostgreSQL的遞歸查詢）

已知條件如下：

CREATE TABLE appointment (
emp_id integer NOT NULL,
jobtitle varchar(128) NOT NULL,
salary decimal(10,2) NOT NULL,
start_date date NOT NULL,
end_date date NULL
);
ALTER TABLE appointment ADD CONSTRAINT pkey_appointment PRIMARY KEY (emp_id, jobtitle, start_date);
ALTER TABLE appointment ADD CONSTRAINT chk_appointment_period CHECK (start_date <= end_date);

插入數據如下：

INSERT INTO appointment VALUES
(1, ’tutor’, 40000, ’2008-01-01’, ’2009-02-01’),
(1, ’tutor’, 42000, ’2009-01-01’, ’2010-09-30’),
(1, ’tutor’, 45000, ’2012-04-01’, ’2013-12-31’),
(1, ’tutor’, 46000, ’2014-01-01’, ’2014-12-31’),
(1, ’lecturer’, 65000, ’2014-06-01’, NULL),
(2, ’librarian’, 35000, ’2014-01-01’, NULL),
(2, ’tutor’, 20000, ’2014-01-01’, NULL),
(3, ’lecturer’, 65000, ’2014-06-01’, ’2015-01-01’);

既：

 問題如下：

Write an SQL query which returns for all current employees the start of their current period of continuous employment. That is, we are asking for the oldest date X such that the employee had one or more appointments on every day since X. 

then the query should return

Hint: First construct a subquery to compute appointments for current employees that do not overlap with (or are adjacent to) appointments (for the same employee) starting earlier then select for each employee the latest start-date of such appointments.

代碼如下：

WITH RECURSIVE start_appointment AS (
  SELECT emp_id, start_date
  FROM appointment
  WHERE end_date IS NULL
  UNION
    SELECT a.emp_id, a.start_date
  FROM appointment a, start_appointment sa
  WHERE a.emp_id = sa.emp_id AND
    a.end_date >= (sa.start_date - 1) AND
    a.start_date <= sa.start_date
)
SELECT emp_id, min(start_date) as start_date
FROM start_appointment
GROUP BY emp_id;

Union用於合併兩個或多個 SELECT 語句的結果集, UNION 內部的 SELECT 語句必須擁有相同數量的列。列也必須擁有相似的數據類型。同時，每條 SELECT 語句中的列的順序必須相同。

Union 的話只會選取不同的值，添加All的話重覆的 不重覆的都會選上。

with recursive 是 postgresql 支持的一種寫法,既遞歸查詢。

我們現在有兩個表，通過null得表a，where條件是一個表的工作截止日期必須比另個表的開始日期大，開始日期還要比下一個表的開始日期要小，這樣才能確保是工作時間是連著的,通過遞歸不斷選擇我們所需要的數據，最後min一下得到最小值再gruop by 排好。

結果如下：