sql - 比较不同表中的两列并进行插入

Question

我有两个表:

1.Tbl_project

fld_id fld_allocated_days
1       10
2        3
3        1
4       99

2.Tbl_project_timesheet

fld_id fld_allocated_time fld_project_id
1       8.00                1
2       8.00                1
3       8.00                2
4       8.00                3
5       8.00                2
6       8.00                2
7       8.00                1
8       8.00                4
9       8.00                1

--fld_project_id 对来自 tbl_project 的 (fld_id) 的引用

我想创建一个函数，将表 tbl_project 中的列 fld_allocated_days 与 tbl_project_timesheet 中的列 SUM(fld_allocated_time) 进行比较。

如果第一列 (fld_allocated_time) 小于第二列 (fld_allocated_days)，则在表 tbl_project_timesheet 上进行插入。

函数参数之一是 p_project_id，它将选择为哪个项目插入分配的时间。

我有一个函数可以插入分配的时间但不检查是否超过分配的天数。

CREATE OR REPLACE FUNCTION function_add_timesheet_record( p_project_id integer, p_allocated_time numeric)
  RETURNS void AS

BEGIN

INSERT INTO tbl_project_timesheet(fld_project_id,fld_allocated_time)
    VALUES (p_project_id, p_allocated_time);
END

Answer 1

根据以上评论，我们假设 1 day来自 tbl_project等于 8 hours来自表 tbl_project_timesheet .这样，根据您提供的数据，项目 1 缺少 6 天，项目 4 缺少 98 天。我们将这些转换为秒并比较两个表中的值。

然后我们用 8 的整数填充数组，并在数组中附加除法的余数(以防天数不完整)。

然后我们取消嵌套数组，这样每行(天)就有 8 个小时。最后我们插入数据。

WITH
  d AS (
    SELECT
      p.fld_id,
      p.fld_allocated_days * 8 * 3600 - COALESCE(
        SUM(
          EXTRACT(epoch FROM t.fld_allocated_time)::int
        ),
      0) AS diff
    FROM
      tbl_project p LEFT JOIN tbl_project_timesheet t
    ON
      p.fld_id=t.fld_project_id
    GROUP BY
      p.fld_id
  ),
  agg_d AS (
    SELECT
      fld_id,
      UNNEST(
        ARRAY_APPEND(
          ARRAY_FILL(
            '8:00'::time,
            ARRAY[(diff / (3600 * 8))::int]
          ),
          ((diff % (3600 * 8)) * interval '1 second')::time
        )
      ) AS hours FROM d
    WHERE
      diff > 0
  )
INSERT INTO tbl_project_timesheet
  (fld_project_id, fld_allocated_time)
SELECT
  fld_id, hours
FROM
  agg_d
WHERE
  hours > '0:0'::time;