首页 > > 网络编程 > 其它 >

对sql作业的总结（PostgreSQL的递归查询）

2018-06-17 20:07:23来源：未知阅读 ()

已知条件如下：

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;