SQL UNION 操作符的应用

1.UNION

UNION操作符用于合并两个或多个SELECT语句的结果集。但是要注意的是UNION连接的前后SELECT语句查找的字段要一致。

UNION是取不同的值,也就是不能重复的值。

2.UNION ALL

UNION ALL 可以取重复的值。

第一个例子是查找所有的字段，例子如下：

SELECT
            a.id,
            a.in_out_flag,
            a.document_number,
            a.document_date,
            a.warehouse_id,
            a.bill_type,
            a.storeman_id,
            a.purpose,
            a.illustrate,
            a.type,
            a.counterparty_code,
            a.bill_number,
            a.agency_department,
            a.operator,
            a.create_by,
            a.create_time,
            a.update_by,
            a.update_time,
            a.remark,
            a.del_flag,
            a.confirm_flag,
            a.project_number,
            a.order_no,
            k.username AS "storemanName",
            s3.user_name AS "operatorName",
            d.dept_name AS "agencyDepartmentName",
            w.warehouse_name AS "warehouseName",
            w.warehouse_code AS "warehouseCode"
        FROM
            (
        SELECT
            *
        FROM
            inv_bill
         UNION ALL
        SELECT
            *
        FROM
            inv_bill_out
            ) a
            LEFT JOIN inv_warehouse_keeper k ON a.storeman_id = k.id
            LEFT JOIN sys_user s3 ON a.operator = s3.user_id
            LEFT JOIN sys_dept d ON a.agency_department = d.dept_id
            LEFT JOIN inv_warehouse_def w ON a.warehouse_id = w.id
         WHERE
            a.confirm_flag = '1'
            AND a.del_flag = '0'

第二例子根据想要的条件进行返回结果集，例子如下：

SELECT
            a.id AS "id",
            a.in_out_flag AS "inOutFlag",
            a.document_number AS "documentNumber",
            a.document_date AS "documentDate",
            a.bill_type AS "billType",
            a.username AS "storemanName",
            a.quantity AS "quantity",
            a.user_name AS "operatorName",
            a.counterparty_code AS "counterpartyCode",
            a.material_code AS "materialCode",
            a.material_name AS "materialName",
            a.confirm_flag AS "confirmFlag",
            a.del_flag AS "delFlag",
            a.warehouse_name AS "warehouseName",
            a.warehouse_code AS "warehouseCode"
        FROM
            (
        SELECT
            a.id,
            a.in_out_flag,
            a.document_number,
            a.document_date,
            a.bill_type,
            k.username,
            d.quantity,
            s3.user_name,
            a.counterparty_code,
            d.material_code,
            d.material_name,
            a.confirm_flag,
            a.del_flag,
            w.warehouse_name,
            w.warehouse_code
        FROM
            inv_bill_detail d
            LEFT JOIN inv_bill a ON a.id = d.warehouse_bill_id
            LEFT JOIN inv_warehouse_keeper k ON a.storeman_id = k.id
            LEFT JOIN sys_user s3 ON a.operator = s3.user_id
            LEFT JOIN inv_warehouse_def w ON a.warehouse_id = w.id UNION ALL
        SELECT
            a.id,
            a.in_out_flag,
            a.document_number,
            a.document_date,
            a.bill_type,
            k.username,
            d.quantity,
            s3.user_name,
            a.counterparty_code,
            d.material_code,
            d.material_name,
            a.confirm_flag,
            a.del_flag,
            w.warehouse_name,
            w.warehouse_code
        FROM
            inv_bill_detail_out d
            LEFT JOIN inv_bill_out a ON a.id = d.warehouse_bill_id
            LEFT JOIN inv_warehouse_keeper k ON a.storeman_id = k.id
            LEFT JOIN sys_user s3 ON a.operator = s3.user_id
            LEFT JOIN inv_warehouse_def w ON a.warehouse_id = w.id
            ) a
        WHERE
            a.confirm_flag = '1'
            AND a.del_flag = '0'