sql - 我如何从 PostgreSQL 获取特定日期的特定数据？

Question

我在 PostgreSQL 中有一个表，如下所示...

emp_id   emp_name     date     shift
  001     Rana     01.07.2019    G
  001     Rana     02.07.2019    G
  001     Rana     03.07.2019    G
  001     Rana     04.07.2019    G
  001     Rana     05.07.2019    Off
  001     Rana     06.07.2019    G
  001     Rana     07.07.2019    G
  ................................
  ................................
  ................................
  001     Rana     30.06.2020    G   [total 365 days]
  002     Tame     01.07.2019    G
  002     Tame     02.07.2019    G
  002     Tame     03.07.2019    G
  002     Tame     04.07.2019    G
  002     Tame     05.07.2019    Off
  002     Tame     06.07.2019    G
  002     Tame     07.07.2019    G
  ................................
  ................................
  ................................
  002     Tame     30.06.2020    G

我想将“date=04.07.2019”传递给参数，然后打印将...

emp_id   emp_name     Thursday   Friday  Saturday   Sunday  Monday  Tuesday   Wednesday
  001      Rana           G         Off     G         G       G        G          G
  002      Tame           G         Off     G         G       G        G          G

我还想将另一个“date=26.11.2019”传递到参数中，然后打印将...

emp_id   emp_name     Tuesday   Wednesday   Thursday   Friday  Saturday   Sunday  Monday  
  001      Rana          G          G          G         Off      G         G        G
  002      Tame          G          G          G         Off      G         G        G

Answer 1

您可以使用CASE 表达式 和聚合 函数

FIDDLE DEMO

SELECT E.emp_name,E.emp_id,
       MAX(CASE WHEN trim(to_char(D.date_trunc, 'Day')) = 'Monday' THEN E.shift Else NULL END) AS "Monday",
       MAX(CASE WHEN trim(to_char(D.date_trunc, 'Day')) = 'Tuesday' THEN E.shift Else NULL EnD) AS "Tuesday",
       MAX(CASE WHEN trim(to_char(D.date_trunc, 'Day')) = 'Wednesday' THEN E.shift Else NULL EnD) AS "Wednesday",
       MAX(CASE WHEN trim(to_char(D.date_trunc, 'Day')) = 'Thursday' THEN E.shift Else NULL EnD) AS "Thursday",
       MAX(CASE WHEN trim(to_char(D.date_trunc, 'Day')) = 'Friday' THEN E.shift Else NULL EnD) AS "Friday",
       MAX(CASE WHEN trim(to_char(D.date_trunc, 'Day')) = 'Saturday' THEN E.shift Else NULL EnD) AS "Saturday",
       MAX(CASE WHEN trim(to_char(D.date_trunc, 'Day')) = 'Sunday' THEN E.shift Else NULL EnD) AS "Sunday"
FROM
(
  SELECT date_trunc('day', dd):: date 
  FROM generate_series('04.07.2019'::timestamp,'04.07.2019'::timestamp + INTERVAL '6 day','1 day'::interval) dd
) D JOIN Employee E ON E.date = D.date_trunc
GROUP BY  E.emp_name,E.emp_id;

更新查询

FIDDLE DEMO

根据 a_horse_with_no_name建议，下面是修改后的查询，使用 filter() 会更容易阅读.

SELECT E.emp_name,E.emp_id,
       MAX(e.shift) filter (where trim(to_char(D.date_trunc, 'Day')) = 'Monday') AS "Monday",
       MAX(e.shift) filter (where trim(to_char(D.date_trunc, 'Day')) = 'Tuesday') AS "Tuesday",
       MAX(e.shift) filter (where trim(to_char(D.date_trunc, 'Day')) = 'Wednesday') AS "Wednesday",
       MAX(e.shift) filter (where trim(to_char(D.date_trunc, 'Day')) = 'Thursday') AS "Thursday",
       MAX(e.shift) filter (where trim(to_char(D.date_trunc, 'Day')) = 'Friday') AS "Friday",
       MAX(e.shift) filter (where trim(to_char(D.date_trunc, 'Day')) = 'Saturday') AS "Saturday",
       MAX(e.shift) filter (where trim(to_char(D.date_trunc, 'Day')) = 'Sunday') AS "Sunday"
FROM (
  SELECT date_trunc('day', dd):: date 
  FROM generate_series('04.07.2019'::timestamp,'04.07.2019'::timestamp + INTERVAL '6 day','1 day'::interval) dd
) D JOIN Employee E ON E.date = D.date_trunc
GROUP BY  E.emp_name,E.emp_id;

输出: