SELECT
' ' as hospitalcode,
' ' as pat_type,
m."clinic_patient_id" as pat_no,
m."patient_id" as pat_id,
m."treatment_card_no" as pat_cardno,
' ' as inp_id,
' ' as inp_date,
a."patient_name" as pat_name,
a."patient_sex" as pat_sex,
b."birthday" as pat_birth,
a."diagnosis" as pat_diag,
' ' as charge_typeno,
' ' as req_wardno,
' ' as req_bedno,
' ' as req_comm,
m."treatment_office_id" as req_deptno,
m."doctor_id" as req_docno,
m."operate_time" as req_dt,
' ' as emer_flag,
' ' as original_reqno,
m."exec_dept_id" as perform_dept,
m."system_type" as req_groupna,
' ' as specimen_name,
' ' as sample_detail,
' ' as req_reason,
' ' as sample_items,
m."status" as charge_flag,
' ' as charge_user,
' ' as charge_dt,
' ' as secrecy,
' ' as other_stat,
' ' as abo_bldtype,
' ' as rh_bldtype,
' ' as pat_diag_icd,
b."address" as pat_address,
b."nationality" as pat_nation,
b."idcard_no" as pat_idcardno,
b."mobile" as pat_phone,
' ' as pat_height,
' ' as weight,
m."drug_no" as his_itemcode,
' ' as req_itemcode,
m."drug_name" as req_itemname,
' ' as combitemna,
m."unit_price" as base_price,
m."unit_price" as item_price,
m."open_quantity" as qty,
m."total_price" as amount,
m."id" as his_recordid,
' ' as his_refcol1,
' ' as his_refcol2,
' ' as his_refcol3
FROM
"his_clinic_doctor_rx_drug" m LEFT JOIN "his_clinic_doctor_rx_info" a ON m. "prescription_no" = a."prescription_no" LEFT JOIN "his_clinic_patient_info" b ON b."patient_id" = m."patient_id";
UNION ALL
SELECT
' ' as hospitalcode,
' ' as pat_type,
n."clinic_patient_id" as pat_no,
n."patient_id" as pat_id,
n."treatment_card_no" as pat_cardno,
' ' as inp_id,
' ' as inp_date,
c."patient_name" as pat_name,
c."patient_sex" as pat_sex,
p."birthday" as pat_birth,
n."diagnosis" as pat_diag,
' ' as charge_typeno,
' ' as req_wardno,
' ' as req_bedno,
' ' as req_comm,
n."treatment_office_id" as req_deptno,
n."doctor_id" as req_docno,
n."operate_time" as req_dt,
' ' as emer_flag,
' ' as original_reqno,
n."exec_dept_id" as perform_dept,
n."system_type" as req_groupna,
' ' as specimen_name,
' ' as sample_detail,
' ' as req_reason,
' ' as sample_items,
n."status" as charge_flag,
' ' as charge_user,
' ' as charge_dt,
' ' as secrecy,
' ' as other_stat,
' ' as abo_bldtype,
' ' as rh_bldtype,
' ' as pat_diag_icd,
p."address" as pat_address,
p."nationality" as pat_nation,
p."idcard_no" as pat_idcardno,
p."mobile" as pat_phone,
' ' as pat_height,
' ' as weight,
n."item_no" as his_itemcode,
' ' as req_itemcode,
n."item_name" as req_itemname,
' ' as combitemna,
n."unit_price" as base_price,
n."unit_price" as item_price,
n."item_quantity" as qty,
n."total_price" as amount,
n."id" as his_recordid,
' ' as his_refcol1,
' ' as his_refcol2,
' ' as his_refcol3
FROM
"his_clinic_doctor_rx_others" n LEFT JOIN "his_clinic_doctor_rx_info" c ON n. "prescription_no" = c."prescription_no" LEFT JOIN "his_clinic_patient_info" p ON p."patient_id" = n."patient_id";
错误原因:
UNION ALL 两个select 语句时报的两个select 时出现,union 两个select集合时
错误1 :字段名没对齐
错误2:每个字段对应的数据类型不一样
解决方法:
要union all select两个数据集,必须字段名、字段数据类型一样!