当前位置： 文江博客话题详情

PostgreSQL年，四分之一计算器

发布于 2025-01-26 16:11:42 字数 8719 浏览 3 评论 0原文

在一年和四分之一的情况下，一个过程应将值插入表TB_quarter，该值是空的，并且具有此结构：

CREATE TABLE erp.tb_quarter    (
    quarter            INT NOT NULL,
    year               INT NOT NULL,
    cust_no            CHARACTER(5) NOT NULL,
    iva_percent        INT NOT NULL,
    amount             REAL NOT NULL,
    CONSTRAINT pk_quarter_year PRIMARY KEY (quarter,year),
    CONSTRAINT fk_cust_no FOREIGN KEY (cust_no) REFERENCES erp.tb_customer (cust_no)
  );


CREATE TABLE erp.tb_invoice (
    co_code            CHARACTER(3) NOT NULL,
    invoice_id         INT NOT NULL,
    invoice_no         CHARACTER VARYING(15)  NOT NULL,
    cust_no            CHARACTER(5) NOT NULL,
    site_id            INT NOT NULL,
    payed              CHARACTER(1) NOT NULL DEFAULT 'N',
    net_amount         REAL NOT NULL,
    iva_amount         REAL NOT NULL,
    tot_amount         REAL NOT NULL,
    last_updated_by    CHARACTER VARYING(20) DEFAULT 'SYSTEM',
    last_update_date   DATE NOT NULL,
    CONSTRAINT pk_invoice PRIMARY KEY (invoice_id),
    CONSTRAINT fk_invoice_company FOREIGN KEY (co_code) REFERENCES erp.tb_company (co_code),
    CONSTRAINT fk_invoice_customer FOREIGN KEY (cust_no) REFERENCES erp.tb_customer (cust_no),
    CONSTRAINT fk_invoice_site FOREIGN KEY (site_id) REFERENCES erp.tb_site (site_id)  
  );

CREATE TABLE erp.tb_customer    (
    cust_no          CHARACTER(5) NOT NULL,
    cust_name        CHARACTER VARYING(50) NOT NULL,
    cust_cif         CHARACTER VARYING(150) NOT NULL,
    last_updated_by  CHARACTER VARYING(20) DEFAULT 'SYSTEM',
    last_update_date DATE NOT NULL,
    CONSTRAINT pk_customer PRIMARY KEY (cust_no)
  );

到目前为止，我已经有了此代码，但是对我来说，如何构建过程的主体尚不清楚。我认为光标可以帮助但不知道如何将所有Togheter放置：

CREATE PROCEDURE pr_calc_quarter( year INT , quarter INT ) AS $$
BEGIN
FOR 
    SELECT
        EXTRACT(QUARTER FROM i.last_update_date) AS trimestre,
        EXTRACT(year FROM i.last_update_date)  AS año,
        c.cust_no,
        round((i.iva_amount * 100 / net_amount)::numeric,0) AS iva_percent,
        SUM(i.iva_amount) AS amount
    FROM erp.tb_customer c JOIN erp.tb_invoice i ON
        i.cust_no = c. cust_no
    GROUP BY iva_percent, c.cust_no, i.last_update_date
    ORDER BY c.cust_no, iva_percent, amount
    LOOP
        INSERT INTO erp.tb_quarter
        VALUES (year, quarter, iva_percent, amount);
    END LOOP;  
 END;
 $$ LANGUAGE plpgsql;  
 CALL erp.pr_calc_quarter(2022,2)

最后，一些数据进行测试：

INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0001','PIENSOS MARTIN','A12345678','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0002','AGRICULTURA VIVES','A66666666','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0003','CULTIVOS MARAVILLA','A55555555','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0004','ASOCIADOS PEREZ','A23126743','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0005','TECNICOS AVA','B34211233','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0006','AGR AGRI','B78788999','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0007','AGRIMARCOS','B98766562','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0008','CULTIVANDO ALEGRIA','B12333123','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0009','MARCOS LIMPIEZA','A87727711','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0010','VIAJES MUNDO','A00099982','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0011','SEMILLAS MARIA','B98121222','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0012','PIENSOS JAIME','B12337788','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0013','GESTORIA LUIS','B18999001','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0014','CONSULTORES ORC','B17772211','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0015','APR T','A00123333','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0016','AGR SANT JOAN ','B81232171','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0017','DISFRUTA SEMILLA','A12341999','SYSTEM',current_date);

INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AB1',200,'F0000C000400200','C0004',6,'Y',25984.36,2802.34,28786.7,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AB1',201,'F0000C000400201','C0004',6,'N',5134.2,490.09,5624.29,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AB1',202,'F0000C000400202','C0004',6,'Y',22554.69,3120.85,25675.54,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AB1',203,'F0000C000400203','C0004',6,'Y',31766.97,3712.75,35479.72,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AB1',204,'F0000C000400204','C0004',6,'Y',16863.86,1811.27,18675.13,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AB1',205,'F0000C000400205','C0004',6,'Y',15772.42,1582.37,17354.79,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AB1',206,'F0000C000400206','C0004',6,'Y',21486.14,3045.65,24531.79,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AB1',207,'F0000C000400207','C0004',6,'Y',21496.86,2000.61,23497.47,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AB1',208,'F0000C000400208','C0004',6,'N',16715.91,2365.57,19081.48,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AB1',209,'F0000C000400209','C0004',6,'N',18246.24,2150.09,20396.33,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AB1',210,'F0000C000400210','C0004',6,'Y',19250.2,2595.01,21845.21,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AB1',211,'F0000C000400211','C0004',6,'Y',8213.28,1491.36,9704.64,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AX3',212,'F0000C000500212','C0005',7,'N',12193.41,2560.62,14754.03,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AX3',213,'F0000C000500213','C0005',7,'N',2540.08,533.42,3073.5,'SYSTEM',current_date);
原文

Given a year and a quarter, a procedure should INSERT values into table tb_quarter, which is empty and have this structure:

CREATE TABLE erp.tb_quarter    (
    quarter            INT NOT NULL,
    year               INT NOT NULL,
    cust_no            CHARACTER(5) NOT NULL,
    iva_percent        INT NOT NULL,
    amount             REAL NOT NULL,
    CONSTRAINT pk_quarter_year PRIMARY KEY (quarter,year),
    CONSTRAINT fk_cust_no FOREIGN KEY (cust_no) REFERENCES erp.tb_customer (cust_no)
  );


CREATE TABLE erp.tb_invoice (
    co_code            CHARACTER(3) NOT NULL,
    invoice_id         INT NOT NULL,
    invoice_no         CHARACTER VARYING(15)  NOT NULL,
    cust_no            CHARACTER(5) NOT NULL,
    site_id            INT NOT NULL,
    payed              CHARACTER(1) NOT NULL DEFAULT 'N',
    net_amount         REAL NOT NULL,
    iva_amount         REAL NOT NULL,
    tot_amount         REAL NOT NULL,
    last_updated_by    CHARACTER VARYING(20) DEFAULT 'SYSTEM',
    last_update_date   DATE NOT NULL,
    CONSTRAINT pk_invoice PRIMARY KEY (invoice_id),
    CONSTRAINT fk_invoice_company FOREIGN KEY (co_code) REFERENCES erp.tb_company (co_code),
    CONSTRAINT fk_invoice_customer FOREIGN KEY (cust_no) REFERENCES erp.tb_customer (cust_no),
    CONSTRAINT fk_invoice_site FOREIGN KEY (site_id) REFERENCES erp.tb_site (site_id)  
  );

CREATE TABLE erp.tb_customer    (
    cust_no          CHARACTER(5) NOT NULL,
    cust_name        CHARACTER VARYING(50) NOT NULL,
    cust_cif         CHARACTER VARYING(150) NOT NULL,
    last_updated_by  CHARACTER VARYING(20) DEFAULT 'SYSTEM',
    last_update_date DATE NOT NULL,
    CONSTRAINT pk_customer PRIMARY KEY (cust_no)
  );

So far, I've got this code, but it's not clear for me how to structure the body of the procedure. i think a cursor could help but don't know how to put all togheter:

CREATE PROCEDURE pr_calc_quarter( year INT , quarter INT ) AS $
BEGIN
FOR 
    SELECT
        EXTRACT(QUARTER FROM i.last_update_date) AS trimestre,
        EXTRACT(year FROM i.last_update_date)  AS año,
        c.cust_no,
        round((i.iva_amount * 100 / net_amount)::numeric,0) AS iva_percent,
        SUM(i.iva_amount) AS amount
    FROM erp.tb_customer c JOIN erp.tb_invoice i ON
        i.cust_no = c. cust_no
    GROUP BY iva_percent, c.cust_no, i.last_update_date
    ORDER BY c.cust_no, iva_percent, amount
    LOOP
        INSERT INTO erp.tb_quarter
        VALUES (year, quarter, iva_percent, amount);
    END LOOP;  
 END;
 $ LANGUAGE plpgsql;  
 CALL erp.pr_calc_quarter(2022,2)

Finally, some data to test it:

INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0001','PIENSOS MARTIN','A12345678','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0002','AGRICULTURA VIVES','A66666666','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0003','CULTIVOS MARAVILLA','A55555555','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0004','ASOCIADOS PEREZ','A23126743','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0005','TECNICOS AVA','B34211233','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0006','AGR AGRI','B78788999','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0007','AGRIMARCOS','B98766562','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0008','CULTIVANDO ALEGRIA','B12333123','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0009','MARCOS LIMPIEZA','A87727711','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0010','VIAJES MUNDO','A00099982','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0011','SEMILLAS MARIA','B98121222','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0012','PIENSOS JAIME','B12337788','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0013','GESTORIA LUIS','B18999001','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0014','CONSULTORES ORC','B17772211','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0015','APR T','A00123333','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0016','AGR SANT JOAN ','B81232171','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0017','DISFRUTA SEMILLA','A12341999','SYSTEM',current_date);

INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AB1',200,'F0000C000400200','C0004',6,'Y',25984.36,2802.34,28786.7,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AB1',201,'F0000C000400201','C0004',6,'N',5134.2,490.09,5624.29,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AB1',202,'F0000C000400202','C0004',6,'Y',22554.69,3120.85,25675.54,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AB1',203,'F0000C000400203','C0004',6,'Y',31766.97,3712.75,35479.72,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AB1',204,'F0000C000400204','C0004',6,'Y',16863.86,1811.27,18675.13,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AB1',205,'F0000C000400205','C0004',6,'Y',15772.42,1582.37,17354.79,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AB1',206,'F0000C000400206','C0004',6,'Y',21486.14,3045.65,24531.79,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AB1',207,'F0000C000400207','C0004',6,'Y',21496.86,2000.61,23497.47,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AB1',208,'F0000C000400208','C0004',6,'N',16715.91,2365.57,19081.48,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AB1',209,'F0000C000400209','C0004',6,'N',18246.24,2150.09,20396.33,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AB1',210,'F0000C000400210','C0004',6,'Y',19250.2,2595.01,21845.21,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AB1',211,'F0000C000400211','C0004',6,'Y',8213.28,1491.36,9704.64,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AX3',212,'F0000C000500212','C0005',7,'N',12193.41,2560.62,14754.03,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AX3',213,'F0000C000500213','C0005',7,'N',2540.08,533.42,3073.5,'SYSTEM',current_date);

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（1）

十年九夏 2025-02-02 16:11:42

https://wwwww.postgresql.org.org.org.org.org.org/docs/current/current/current/datatate-numeric-imeric-imeric-.orec-.orec-numeric-ericeric.-numeric-. html
请参阅数据类型（真实，双重精度）说明列：
可变精确，不精确！

类型数字可以用大量数字存储数字。
特别建议存储货币金额和其他
需要精确性的数量。

因此，使用数字数据类型存储需要精确的信息。

”

实现的视图或视图。您无需创建另一个表季度，因为可以通过加入客户和Invice表查询所有信息。

SELECT
    EXTRACT(QUARTER FROM i.last_update_date) AS quarter,
    EXTRACT(year FROM i.last_update_date) AS year,
    c.cust_no,
    round(((i.iva_amount * 100) / net_amount), 2) AS iva_percent,
    i.iva_amount
FROM
    erp.tb_customer c
    JOIN erp.tb_invoice i ON i.cust_no = c. cust_no;

另外，只需使用文本数据类型来存储文本字符：>使用数据类型“文本”存储字符串的缺点？

https://www.postgresql.org/docs/current/datatype-numeric.html
please refer to data type (real, double precision) description column:
variable-precision, inexact!

The type numeric can store numbers with a very large number of digits.
It is especially recommended for storing monetary amounts and other
quantities where exactness is required.

So use numeric datatype to store info that require exact.

demo

you can make it via materialized view or view. You don't need to create another table quarter, because all the info can be queried through join customer and invice table.

SELECT
    EXTRACT(QUARTER FROM i.last_update_date) AS quarter,
    EXTRACT(year FROM i.last_update_date) AS year,
    c.cust_no,
    round(((i.iva_amount * 100) / net_amount), 2) AS iva_percent,
    i.iva_amount
FROM
    erp.tb_customer c
    JOIN erp.tb_invoice i ON i.cust_no = c. cust_no;

also just use text data type for storing text characters: Any downsides of using data type "text" for storing strings?

回复 原文
~没有更多了~

关于作者

梦里兽

暂无简介

文章
评论
342 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

櫻之舞

文章 0 评论 0

弥枳

文章 0 评论 0

m2429

文章 0 评论 0

寻找一个思念的角度

文章 0 评论 0

野却迷人

文章 0 评论 0

我怀念的。

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文