Sql UNION ALL 应用实例(Postgres数据库)

最新推荐文章于 2023-10-02 22:01:42 发布

tof21

最新推荐文章于 2023-10-02 22:01:42 发布

阅读量1.7k

点赞数

分类专栏：工作文章标签：数据库 sql character payment class classification

本文链接：https://blog.csdn.net/sjdl9396/article/details/6081389

版权

工作专栏收录该内容

12 篇文章 0 订阅

订阅专栏

<1>应用视图1

合并几张表的数据，并且从合并的数据中统计出数据。

原则是合并前两张表，然后把前两张表当作一个表，在和下一个表合并，依次类推。最后给出统计条件.

-- View: view_class_supplier_current_balance_general1

-- DROP VIEW view_class_supplier_current_balance_general1;

CREATE OR REPLACE VIEW view_class_supplier_current_balance_general1 AS
((( SELECT nextval('purchaseview'::regclass) AS view_id, p.partner_name, '预付'::character varying::character varying(255) AS balance_name, sum(p.total_amount) AS balance
   FROM ((( SELECT a.order_number, a.partner_name, a.total_amount, a.business_type, 'inboundPayment' AS class_name, a.classification_status1, '预付' AS pay_type
           FROM inbound_payment a
          WHERE a.order_state::text = '有效'::text AND a.classification_status1::text = '预付账款'::text
UNION ALL
         SELECT a.order_number, a.partner_name, a.total_amount, a.business_type, 'inboundPayment' AS class_name, a.classification_status1, '预付' AS pay_type
           FROM inbound_payment a
          WHERE a.order_state::text = '有效'::text AND a.classification_status1::text = '应付转预付'::text)
UNION ALL
         SELECT a.order_number, a.partner_name, - a.total_amount AS total_amount, a.business_type, 'inboundPayment' AS class_name, a.classification_status1, '预付' AS pay_type
           FROM inbound_payment a
          WHERE a.order_state::text = '有效'::text AND a.classification_status1::text = '预付冲应付'::text)
UNION ALL
         SELECT a.order_number, a.partner_name, sum(a.sub_total_amt) AS total_amount, a.business_type, 'inboundReturn' AS class_name, a.classification_status1, '预付' AS pay_type
           FROM inbound_return_item a
          WHERE a.order_state::text = '有效'::text AND a.classification_status1::text = '冲抵往来'::text AND a.refer_order_number IS NULL
          GROUP BY a.order_number, a.partner_name, a.business_type, a.classification_status1) p
GROUP BY p.partner_name
UNION ALL
SELECT nextval('purchaseview'::regclass) AS view_id, p.partner_name, '应付'::character varying::character varying(255) AS balance_name, sum(p.total_amount) AS balance
   FROM ((( SELECT a.order_number, a.partner_name, - a.total_amount AS total_amount, a.business_type, 'inboundPayment' AS class_name, a.classification_status1, '应付' AS pay_type
           FROM inbound_payment a
          WHERE a.order_state::text = '有效'::text AND a.classification_status1::text = '预付冲应付'::text
UNION ALL
         SELECT a.order_number, a.partner_name, a.total_amount, a.business_type, 'inboundPayment' AS class_name, a.classification_status1, '应付' AS pay_type
           FROM inbound_payment a
          WHERE a.order_state::text = '有效'::text AND a.classification_status1::text = '应付转预付'::text)
UNION ALL
         SELECT a.order_number, a.partner_name, - sum(a.sub_total_amt) AS total_amount, a.business_type, 'inboundReturn' AS class_name, a.classification_status1, '应付' AS pay_type
           FROM inbound_return_item a
          WHERE a.order_state::text = '有效'::text AND a.classification_status1::text = '冲抵往来'::text AND a.refer_order_number IS NOT NULL
          GROUP BY a.order_number, a.partner_name, a.business_type, a.classification_status1)
UNION ALL
         SELECT a.order_number, a.partner_name, a.total_amount, a.business_type, 'inboundOrder' AS class_name, '' AS classification_status1, '应付' AS pay_type
           FROM inbound_order a
          WHERE a.order_state::text = '有效'::text) p
GROUP BY p.partner_name)
UNION ALL
SELECT nextval('purchaseview'::regclass) AS view_id, p.partner_name, '已付'::character varying::character varying(255) AS balance_name, sum(p.total_amount) AS balance
   FROM ( SELECT a.order_number, a.partner_name, a.total_amount, a.business_type, 'inboundPayment' AS class_name, a.classification_status1, '已付' AS pay_type
           FROM inbound_payment a
          WHERE a.order_state::text = '有效'::text AND a.classification_status1::text = '应付账款'::text) p
GROUP BY p.partner_name)
UNION ALL
SELECT nextval('purchaseview'::regclass) AS view_id, a.client_name AS partner_name, '初始应付'::character varying::character varying(255) AS balance_name, a.init_payable AS balance
   FROM supplier a)
UNION ALL
SELECT nextval('purchaseview'::regclass) AS view_id, a.client_name AS partner_name, '信用额度'::character varying::character varying(255) AS balance_name, a.credit_amount AS balance
   FROM supplier a;

ALTER TABLE view_class_supplier_current_balance_general1 OWNER TO postgres;

<2>应用视图2

单纯的合并几张表中的数据。

-- View: viewpayquery_temp_returned_detail

-- DROP VIEW viewpayquery_temp_returned_detail;

CREATE OR REPLACE VIEW viewpayquery_temp_returned_detail AS
SELECT a.refer_order_number, sum(a.sub_total_amt) AS return_amount_real
FROM inbound_return_item a
WHERE a.order_state::text = '有效'::text AND a.classification_status1::text = '冲抵往来'::text
GROUP BY a.refer_order_number
UNION ALL
SELECT a.refer_order_number, sum(a.sub_total_amt) AS return_amount_real
FROM sale_return_item a
WHERE a.order_state::text = '有效'::text AND a.classification_status1::text = '冲抵往来'::text
GROUP BY a.refer_order_number;

<3>应用视图3

合并几张表的数据，把每次合并的表作为最后查询出结果集的一行

-- View: view_class_supplier_current_balance_general1

-- DROP VIEW view_class_supplier_current_balance_general1;

ALTER TABLE view_class_supplier_current_balance_general1 OWNER TO postgres;

tof21

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
Sql UNION ALL 应用实例(Postgres数据库)

 <1>应用视图1 合并几张表的数据，并且从合并的数据中统计出数据。 原则是合并前两张表，然后把前两张表当作一个表，在和下一个表合并，依次类推。最后给出统计条件. -- View: view_class_supplier_current_balance_general1 -- DROP VIEW view_class_supplier_current_balance_general1; CREATE OR REPLACE VIEW view_cla
复制链接

扫一扫