SELECT a.METERBOXID AS row_id, a.epu_name, a.epu_local,b.EPU_PROVINCE,b.epu_city, b.epu_district, b.epu_court ,
b.SUBSTAINID as SUBSTAIN_ID ,b.OUTGOINGCABINETID,b.branchboxid,b.METERBOXID, b.meterId, 'M0004' as EPU_TYPE
FROM T_METERBOX a
JOIN V_USING_TOPO_METERBOX b ON a.METERBOXID = b.METERBOXID
UNION ALL
SELECT a.meterId AS row_id, a.meter_name AS epu_name, a.house_id AS epu_local,b.EPU_PROVINCE,b.epu_city, b.epu_district, b.epu_court ,
b.SUBSTAINID as SUBSTAIN_ID ,b.OUTGOINGCABINETID,b.branchboxid,b.METERBOXID, b.meterId, 'M0005' as EPU_TYPE,a.CONSUMER_NO
FROM T_METER a
JOIN V_USING_TOPO b ON a.METERID = b.METERID
出现这种原因是,B表中有的字段而A表没有。可以通过补空值处理;正确sql如下
SELECT a.METERBOXID AS row_id, a.epu_name, a.epu_local,b.EPU_PROVINCE,b.epu_city, b.epu_district, b.epu_court ,
b.SUBSTAINID as SUBSTAIN_ID ,b.OUTGOINGCABINETID,b.branchboxid,b.METERBOXID, b.meterId, 'M0004' as EPU_TYPE,'a.CONSUMER_NO' as CONSUMER_NO
FROM T_METERBOX a
JOIN V_USING_TOPO_METERBOX b ON a.METERBOXID = b.METERBOXID
UNION ALL
SELECT a.meterId AS row_id, a.meter_name AS epu_name, a.house_id AS epu_local,b.EPU_PROVINCE,b.epu_city, b.epu_district, b.epu_court ,
b.SUBSTAINID as SUBSTAIN_ID ,b.OUTGOINGCABINETID,b.branchboxid,b.METERBOXID, b.meterId, 'M0005' as EPU_TYPE,a.CONSUMER_NO
FROM T_METER a
JOIN V_USING_TOPO b ON a.METERID = b.METERID
附加:UNION ALL :对结果集进行并集操作,不去重;
UNION :对结果集进行并集操作,会去重;