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_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;

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;