时间戳

首页 > 解决方案 > 如何将选定的总和和其他信息插入到表中？

sql - 如何将选定的总和和其他信息插入到表中？

问题描述

我需要将新项目账单添加到具有

  1. 所有工作时间的总和乘以小时工资乘以折扣和

  2. 所有设备售价之和乘以它们的折扣乘以它们的数量和

  3. 项目清单

    也是项目的主键。

这是我的表：

CREATE TABLE labourtype (
lkey SERIAL PRIMARY KEY,
ltype VARCHAR(20),
hourwage DECIMAL);


CREATE TABLE project (
pkey SERIAL PRIMARY KEY,
ptype VARCHAR(20),
p_finished DATE,
has_been_paid INT DEFAULT 0);


CREATE TABLE equipment (
ekey SERIAL PRIMARY KEY,
ename VARCHAR(50) NOT NULL UNIQUE,
purchase_price DECIMAL NOT NULL,
in_stock INT DEFAULT 0);


CREATE TABLE labour_in_project (
lkey INT NOT NULL,
pkey INT NOT NULL,

workday DATE DEFAULT CURRENT_DATE,
workhours INT,
discount DECIMAL,

PRIMARY KEY(lkey, pkey, workday),
FOREIGN KEY (lkey) REFERENCES labourtype(lkey),
FOREIGN KEY (pkey) REFERENCES project(pkey));


CREATE TABLE equipments_used(
pkey INT NOT NULL,
ekey INT NOT NULL,
sold DATE DEFAULT CURRENT_DATE,

quantity int,
selling_price DECIMAL,
discount DECIMAL,

PRIMARY KEY(pkey, ekey, sold),
FOREIGN KEY (pkey) REFERENCES project(pkey),
FOREIGN KEY (ekey) REFERENCES equipment(ekey));


CREATE TABLE bill (
bkey SERIAL PRIMARY KEY,
pkey INT NOT NULL,
labour_price DECIMAL,
equipment_price DECIMAL,
FOREIGN KEY (pkey) REFERENCES project(pkey));

以下是我们工人辛勤劳动的总成本的选择：

SELECT SUM(price) AS workprice
FROM (SELECT labourtype.hourwage * lip.discount * lip.workhours as price
FROM labourtype INNER JOIN labour_in_project AS lip
    ON labourtype.lkey = lip.lkey AND pkey = 1) AS priceoflabour;

以下是我们的设备成本选择：

SELECT SUM(price) AS equipmentprice
FROM (SELECT eu.quantity * eu.selling_price * eu.discount as price
    FROM equipments_used AS eu
    WHERE eu.pkey = 1) AS equipment_costs;

现在我需要做的就是将 workprice、deviceprice 和 pkey 1 插入到账单表中。这该怎么做？

标签： sqlpostgresql

解决方案

如果您只想对 pkey 1 执行此操作，则解决方案非常明显：

INSERT INTO
    bill (pkey, labour_price, equipmentprice)
VALUES
(1,
 (SELECT
      SUM(llp.price)
  FROM
      (SELECT
               labourtype.hourwage * lip.discount * lip.workhours AS price
       FROM
           labourtype
               INNER JOIN labour_in_project AS lip
                          ON labourtype.lkey = lip.lkey AND pkey = 1) AS llp),
 (SELECT
      SUM(ep.price)
  FROM
      (SELECT
               eu.quantity * eu.selling_price * eu.discount AS price
       FROM
           equipments_used AS eu
       WHERE eu.pkey = 1) AS ep));

您可以编写一个以pkey作为参数的函数，并为给定的pkey.

您也可以pkey使用 INSERT INTO SELECT 对所有 s 执行此操作：

INSERT INTO
    bill (pkey, labour_price, equipment_price)
SELECT
    p.pkey                                                                            AS pkey,
    (SELECT
         SUM(llp.price)
     FROM
         (SELECT
              labourtype.hourwage * lip.discount * lip.workhours AS price
          FROM
              labourtype
                  INNER JOIN labour_in_project AS lip
                             ON labourtype.lkey = lip.lkey AND pkey = p.pkey) AS llp) AS workprice,
    (SELECT
         SUM(ep.price)
     FROM
         (SELECT
              eu.quantity * eu.selling_price * eu.discount AS price
          FROM
              equipments_used AS eu
          WHERE eu.pkey = p.pkey) AS ep)                                              AS equipmentprice
FROM
    project p;

推荐阅读