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'