代码：
SQL语句：
superuser登录，默认查全部，包含下级：
--[936, 2000, 0, 936, 2000, 0, -1, 2000, 0]
select *
from (select h.hostid,
h.hostno,
e.verify_status as verifyStatus,
e.host_brand as hostBrand,
d.full_name as usefullname,
o.full_name as ownfullname,
h.displacement,
e.displacement_type as displacementType,
e.asset_attribute as assetAttribute,
e.purchase_type as purchaseType,
to_char(e.purchase_date, 'yyyy-mm-dd') as purchaseDate,
e.usage_property as usageProperty,
e.host_purpose as hostPurpose,
e.host_veh_type as hostVehType,
e.host_price_with_tax as hostPriceWithTax,
e.host_price as hostPrice,
e.host_tax as hostTax,
h.original_mile as originalMile,
e.engine_id as engineId,
e.host_vin as hostVin,
h.appsysid,
h.appdeptid,
e.allot_type as allotType,
to_char(e.allot_date, 'yyyy-mm-dd') as allotDate,
e.service_object as serviceObject,
e.keeper as kepper,
e.keeper_phone as kepperPhone,
e.owner_appsysid as ownerAppSysId,
e.owner_appdeptid as ownerAppDeptId,
e.fixed_asset_id as fixedAssetId,
e.host_tag_flag as hostTagFlag,
e.process_status as processStatus,
e.manufacturer,
e.oil_type as oilType,
e.xszh,
h.hostno_color as hostNoColor,
e.host_color as hostColor,
e.rated_kg as ratedKg,
e.CREATE_USER_ID as CREATEUSERID,
h.UPDATESTATUSTIME as updatetimeorder,
e.veh_kg as vehKg,
e.tyre_size as tyreSize,
e.rated_passenger as ratedPassenger,
e.remark,
h.CREATUSERID as basecrtuser,
e.CREATE_USER_NAME as createUserName,
e.CREATE_TIME as createTime,
e.UPDATE_USER_NAME as updateUserName,
e.UPDATE_TIME as updateTime,
d.DEPT_HIERARCHY as uselevel,
d.DEPT_PROPERTY as useprop,
o.DEPT_HIERARCHY as ownLevel,
o.DEPT_PROPERTY as ownProp,
e.DRIVING_LICENSE_DEPTID as drivingLicenseDeptId,
e.DRIVING_LICENSE_SYSID as drivingLicenseSysId,
to_char(e.DRIVING_LICENSE_DATE, 'yyyy-mm-dd') as drivingLicenseDate,
e.STATE_OF_ORIGIN as stateOfOrigin,
e.HAS_PASSPORT as hasPassport,
e.HAS_ETC as hasETC,
to_char(e.ANNUAL_SURVEY_DATE, 'yyyy-mm-dd') as annualSurveyDate,
e.UPDATE_RECORDS as updateRecords,
e.VEHICLENO as vehicleNo,
e.VEHICLE_MODEL as vehicleModel,
e.VEHICLE_BRAND as vehicleBrand
from position_host_info h
left join position_host_info_extend e
on h.hostid = e.hostid
left join appdeptinfo d
on h.appsysid = d.appsysid
and h.appdeptid = d.appdeptid
left join appdeptinfo o
on e.owner_appsysid = o.appsysid
and e.owner_appdeptid = o.appdeptid
where exists (select appsysid, appdeptid
from table(get_subdept(936, 2000, 0)) f
where h.appsysid = f.appsysid
and h.appdeptid = f.appdeptid)
union
select h.hostid,
h.hostno,
e.verify_status as verifyStatus,
e.host_brand as hostBrand,
d.full_name as usefullname,
o.full_name as ownfullname,
h.displacement,
e.displacement_type as displacementType,
e.asset_attribute as assetAttribute,
e.purchase_type as purchaseType,
to_char(e.purchase_date, 'yyyy-mm-dd') as purchaseDate,
e.usage_property as usageProperty,
e.host_purpose as hostPurpose,
e.host_veh_type as hostVehType,
e.host_price_with_tax as hostPriceWithTax,
e.host_price as hostPrice,
e.host_tax as hostTax,
h.original_mile as originalMile,
e.engine_id as engineId,
e.host_vin as hostVin,
h.appsysid,
h.appdeptid,
e.allot_type as allotType,
to_char(e.allot_date, 'yyyy-mm-dd') as allotDate,
e.service_object as serviceObject,
e.keeper as kepper,
e.keeper_phone as kepperPhone,
e.owner_appsysid as ownerAppSysId,
e.owner_appdeptid as ownerAppDeptId,
e.fixed_asset_id as fixedAssetId,
e.host_tag_flag as hostTagFlag,
e.process_status as processStatus,
e.manufacturer,
e.oil_type as oilType,
e.xszh,
h.hostno_color as hostNoColor,
e.host_color as hostColor,
e.rated_kg as ratedKg,
e.CREATE_USER_ID as CREATEUSERID,
h.UPDATESTATUSTIME as updatetimeorder,
e.veh_kg as vehKg,
e.tyre_size as tyreSize,
e.rated_passenger as ratedPassenger,
e.remark,
h.CREATUSERID as basecrtuser,
e.CREATE_USER_NAME as createUserName,
e.CREATE_TIME as createTime,
e.UPDATE_USER_NAME as updateUserName,
e.UPDATE_TIME as updateTime,
d.DEPT_HIERARCHY as uselevel,
d.DEPT_PROPERTY as useprop,
o.DEPT_HIERARCHY as ownLevel,
o.DEPT_PROPERTY as ownProp,
e.DRIVING_LICENSE_DEPTID as drivingLicenseDeptId,
e.DRIVING_LICENSE_SYSID as drivingLicenseSysId,
to_char(e.DRIVING_LICENSE_DATE, 'yyyy-mm-dd') as drivingLicenseDate,
e.STATE_OF_ORIGIN as stateOfOrigin,
e.HAS_PASSPORT as hasPassport,
e.HAS_ETC as hasETC,
to_char(e.ANNUAL_SURVEY_DATE, 'yyyy-mm-dd') as annualSurveyDate,
e.UPDATE_RECORDS as updateRecords,
e.VEHICLENO as vehicleNo,
e.VEHICLE_MODEL as vehicleModel,
e.VEHICLE_BRAND as vehicleBrand
from position_host_info h
left join position_host_info_extend e
on h.hostid = e.hostid
left join appdeptinfo d
on h.appsysid = d.appsysid
and h.appdeptid = d.appdeptid
left join appdeptinfo o
on e.owner_appsysid = o.appsysid
and e.owner_appdeptid = o.appdeptid
where exists (select appsysid, appdeptid
from table(get_subdept(936, 2000, 0)) g
where g.appsysid = e.owner_appsysid
and g.appdeptid = e.owner_appdeptid)) t
where exists (select appsysid, appdeptid
from table(get_subdept(-1, 2000, 0)) g
where g.appsysid = t.appsysid
and g.appdeptid = t.appdeptid)
order by t.updatetimeorder desc, t.hostno
--[936, 2000, 0, 936, 2000, 0, 2000, 0]
select *
from (select h.hostid,
h.hostno,
e.verify_status as verifyStatus,
e.host_brand as hostBrand,
d.full_name as usefullname,
o.full_name as ownfullname,
h.displacement,
e.displacement_type as displacementType,
e.asset_attribute as assetAttribute,
e.purchase_type as purchaseType,
to_char(e.purchase_date, 'yyyy-mm-dd') as purchaseDate,
e.usage_property as usageProperty,
e.host_purpose as hostPurpose,
e.host_veh_type as hostVehType,
e.host_price_with_tax as hostPriceWithTax,
e.host_price as hostPrice,
e.host_tax as hostTax,
h.original_mile as originalMile,
e.engine_id as engineId,
e.host_vin as hostVin,
h.appsysid,
h.appdeptid,
e.allot_type as allotType,
to_char(e.allot_date, 'yyyy-mm-dd') as allotDate,
e.service_object as serviceObject,
e.keeper as kepper,
e.keeper_phone as kepperPhone,
e.owner_appsysid as ownerAppSysId,
e.owner_appdeptid as ownerAppDeptId,
e.fixed_asset_id as fixedAssetId,
e.host_tag_flag as hostTagFlag,
e.process_status as processStatus,
e.manufacturer,
e.oil_type as oilType,
e.xszh,
h.hostno_color as hostNoColor,
e.host_color as hostColor,
e.rated_kg as ratedKg,
e.CREATE_USER_ID as CREATEUSERID,
h.UPDATESTATUSTIME as updatetimeorder,
e.veh_kg as vehKg,
e.tyre_size as tyreSize,
e.rated_passenger as ratedPassenger,
e.remark,
h.CREATUSERID as basecrtuser,
e.CREATE_USER_NAME as createUserName,
e.CREATE_TIME as createTime,
e.UPDATE_USER_NAME as updateUserName,
e.UPDATE_TIME as updateTime,
d.DEPT_HIERARCHY as uselevel,
d.DEPT_PROPERTY as useprop,
o.DEPT_HIERARCHY as ownLevel,
o.DEPT_PROPERTY as ownProp,
e.DRIVING_LICENSE_DEPTID as drivingLicenseDeptId,
e.DRIVING_LICENSE_SYSID as drivingLicenseSysId,
to_char(e.DRIVING_LICENSE_DATE, 'yyyy-mm-dd') as drivingLicenseDate,
e.STATE_OF_ORIGIN as stateOfOrigin,
e.HAS_PASSPORT as hasPassport,
e.HAS_ETC as hasETC,
to_char(e.ANNUAL_SURVEY_DATE, 'yyyy-mm-dd') as annualSurveyDate,
e.UPDATE_RECORDS as updateRecords,
e.VEHICLENO as vehicleNo,
e.VEHICLE_MODEL as vehicleModel,
e.VEHICLE_BRAND as vehicleBrand
from position_host_info h
left join position_host_info_extend e
on h.hostid = e.hostid
left join appdeptinfo d
on h.appsysid = d.appsysid
and h.appdeptid = d.appdeptid
left join appdeptinfo o
on e.owner_appsysid = o.appsysid
and e.owner_appdeptid = o.appdeptid
where exists (select appsysid, appdeptid
from table(get_subdept(936, 2000, 0)) f
where h.appsysid = f.appsysid
and h.appdeptid = f.appdeptid)
union
select h.hostid,
h.hostno,
e.verify_status as verifyStatus,
e.host_brand as hostBrand,
d.full_name as usefullname,
o.full_name as ownfullname,
h.displacement,
e.displacement_type as displacementType,
e.asset_attribute as assetAttribute,
e.purchase_type as purchaseType,
to_char(e.purchase_date, 'yyyy-mm-dd') as purchaseDate,
e.usage_property as usageProperty,
e.host_purpose as hostPurpose,
e.host_veh_type as hostVehType,
e.host_price_with_tax as hostPriceWithTax,
e.host_price as hostPrice,
e.host_tax as hostTax,
h.original_mile as originalMile,
e.engine_id as engineId,
e.host_vin as hostVin,
h.appsysid,
h.appdeptid,
e.allot_type as allotType,
to_char(e.allot_date, 'yyyy-mm-dd') as allotDate,
e.service_object as serviceObject,
e.keeper as kepper,
e.keeper_phone as kepperPhone,
e.owner_appsysid as ownerAppSysId,
e.owner_appdeptid as ownerAppDeptId,
e.fixed_asset_id as fixedAssetId,
e.host_tag_flag as hostTagFlag,
e.process_status as processStatus,
e.manufacturer,
e.oil_type as oilType,
e.xszh,
h.hostno_color as hostNoColor,
e.host_color as hostColor,
e.rated_kg as ratedKg,
e.CREATE_USER_ID as CREATEUSERID,
h.UPDATESTATUSTIME as updatetimeorder,
e.veh_kg as vehKg,
e.tyre_size as tyreSize,
e.rated_passenger as ratedPassenger,
e.remark,
h.CREATUSERID as basecrtuser,
e.CREATE_USER_NAME as createUserName,
e.CREATE_TIME as createTime,
e.UPDATE_USER_NAME as updateUserName,
e.UPDATE_TIME as updateTime,
d.DEPT_HIERARCHY as uselevel,
d.DEPT_PROPERTY as useprop,
o.DEPT_HIERARCHY as ownLevel,
o.DEPT_PROPERTY as ownProp,
e.DRIVING_LICENSE_DEPTID as drivingLicenseDeptId,
e.DRIVING_LICENSE_SYSID as drivingLicenseSysId,
to_char(e.DRIVING_LICENSE_DATE, 'yyyy-mm-dd') as drivingLicenseDate,
e.STATE_OF_ORIGIN as stateOfOrigin,
e.HAS_PASSPORT as hasPassport,
e.HAS_ETC as hasETC,
to_char(e.ANNUAL_SURVEY_DATE, 'yyyy-mm-dd') as annualSurveyDate,
e.UPDATE_RECORDS as updateRecords,
e.VEHICLENO as vehicleNo,
e.VEHICLE_MODEL as vehicleModel,
e.VEHICLE_BRAND as vehicleBrand
from position_host_info h
left join position_host_info_extend e
on h.hostid = e.hostid
left join appdeptinfo d
on h.appsysid = d.appsysid
and h.appdeptid = d.appdeptid
left join appdeptinfo o
on e.owner_appsysid = o.appsysid
and e.owner_appdeptid = o.appdeptid
where exists (select appsysid, appdeptid
from table(get_subdept(936, 2000, 0)) g
where g.appsysid = e.owner_appsysid
and g.appdeptid = e.owner_appdeptid)) t
where t.appsysid = 2000
and t.appdeptid = 0
order by t.updatetimeorder desc, t.hostno
人事处登录，人事处已经没有下级了，勾选不勾选，结果都一样，只是sql语句不一样而已
默认查全部，包含下级（其实没有下级了）：
--[8196, 23551, 3, 8196, 23551, 3, -1, 23551, 3]
select *
from (select h.hostid,
h.hostno,
e.verify_status as verifyStatus,
e.host_brand as hostBrand,
d.full_name as usefullname,
o.full_name as ownfullname,
h.displacement,
e.displacement_type as displacementType,
e.asset_attribute as assetAttribute,
e.purchase_type as purchaseType,
to_char(e.purchase_date, 'yyyy-mm-dd') as purchaseDate,
e.usage_property as usageProperty,
e.host_purpose as hostPurpose,
e.host_veh_type as hostVehType,
e.host_price_with_tax as hostPriceWithTax,
e.host_price as hostPrice,
e.host_tax as hostTax,
h.original_mile as originalMile,
e.engine_id as engineId,
e.host_vin as hostVin,
h.appsysid,
h.appdeptid,
e.allot_type as allotType,
to_char(e.allot_date, 'yyyy-mm-dd') as allotDate,
e.service_object as serviceObject,
e.keeper as kepper,
e.keeper_phone as kepperPhone,
e.owner_appsysid as ownerAppSysId,
e.owner_appdeptid as ownerAppDeptId,
e.fixed_asset_id as fixedAssetId,
e.host_tag_flag as hostTagFlag,
e.process_status as processStatus,
e.manufacturer,
e.oil_type as oilType,
e.xszh,
h.hostno_color as hostNoColor,
e.host_color as hostColor,
e.rated_kg as ratedKg,
e.CREATE_USER_ID as CREATEUSERID,
h.UPDATESTATUSTIME as updatetimeorder,
e.veh_kg as vehKg,
e.tyre_size as tyreSize,
e.rated_passenger as ratedPassenger,
e.remark,
h.CREATUSERID as basecrtuser,
e.CREATE_USER_NAME as createUserName,
e.CREATE_TIME as createTime,
e.UPDATE_USER_NAME as updateUserName,
e.UPDATE_TIME as updateTime,
d.DEPT_HIERARCHY as uselevel,
d.DEPT_PROPERTY as useprop,
o.DEPT_HIERARCHY as ownLevel,
o.DEPT_PROPERTY as ownProp,
e.DRIVING_LICENSE_DEPTID as drivingLicenseDeptId,
e.DRIVING_LICENSE_SYSID as drivingLicenseSysId,
to_char(e.DRIVING_LICENSE_DATE, 'yyyy-mm-dd') as drivingLicenseDate,
e.STATE_OF_ORIGIN as stateOfOrigin,
e.HAS_PASSPORT as hasPassport,
e.HAS_ETC as hasETC,
to_char(e.ANNUAL_SURVEY_DATE, 'yyyy-mm-dd') as annualSurveyDate,
e.UPDATE_RECORDS as updateRecords,
e.VEHICLENO as vehicleNo,
e.VEHICLE_MODEL as vehicleModel,
e.VEHICLE_BRAND as vehicleBrand
from position_host_info h
left join position_host_info_extend e
on h.hostid = e.hostid
left join appdeptinfo d
on h.appsysid = d.appsysid
and h.appdeptid = d.appdeptid
left join appdeptinfo o
on e.owner_appsysid = o.appsysid
and e.owner_appdeptid = o.appdeptid
where exists (select appsysid, appdeptid
from table(get_subdept(8196, 23551, 3)) f
where h.appsysid = f.appsysid
and h.appdeptid = f.appdeptid)
union
select h.hostid,
h.hostno,
e.verify_status as verifyStatus,
e.host_brand as hostBrand,
d.full_name as usefullname,
o.full_name as ownfullname,
h.displacement,
e.displacement_type as displacementType,
e.asset_attribute as assetAttribute,
e.purchase_type as purchaseType,
to_char(e.purchase_date, 'yyyy-mm-dd') as purchaseDate,
e.usage_property as usageProperty,
e.host_purpose as hostPurpose,
e.host_veh_type as hostVehType,
e.host_price_with_tax as hostPriceWithTax,
e.host_price as hostPrice,
e.host_tax as hostTax,
h.original_mile as originalMile,
e.engine_id as engineId,
e.host_vin as hostVin,
h.appsysid,
h.appdeptid,
e.allot_type as allotType,
to_char(e.allot_date, 'yyyy-mm-dd') as allotDate,
e.service_object as serviceObject,
e.keeper as kepper,
e.keeper_phone as kepperPhone,
e.owner_appsysid as ownerAppSysId,
e.owner_appdeptid as ownerAppDeptId,
e.fixed_asset_id as fixedAssetId,
e.host_tag_flag as hostTagFlag,
e.process_status as processStatus,
e.manufacturer,
e.oil_type as oilType,
e.xszh,
h.hostno_color as hostNoColor,
e.host_color as hostColor,
e.rated_kg as ratedKg,
e.CREATE_USER_ID as CREATEUSERID,
h.UPDATESTATUSTIME as updatetimeorder,
e.veh_kg as vehKg,
e.tyre_size as tyreSize,
e.rated_passenger as ratedPassenger,
e.remark,
h.CREATUSERID as basecrtuser,
e.CREATE_USER_NAME as createUserName,
e.CREATE_TIME as createTime,
e.UPDATE_USER_NAME as updateUserName,
e.UPDATE_TIME as updateTime,
d.DEPT_HIERARCHY as uselevel,
d.DEPT_PROPERTY as useprop,
o.DEPT_HIERARCHY as ownLevel,
o.DEPT_PROPERTY as ownProp,
e.DRIVING_LICENSE_DEPTID as drivingLicenseDeptId,
e.DRIVING_LICENSE_SYSID as drivingLicenseSysId,
to_char(e.DRIVING_LICENSE_DATE, 'yyyy-mm-dd') as drivingLicenseDate,
e.STATE_OF_ORIGIN as stateOfOrigin,
e.HAS_PASSPORT as hasPassport,
e.HAS_ETC as hasETC,
to_char(e.ANNUAL_SURVEY_DATE, 'yyyy-mm-dd') as annualSurveyDate,
e.UPDATE_RECORDS as updateRecords,
e.VEHICLENO as vehicleNo,
e.VEHICLE_MODEL as vehicleModel,
e.VEHICLE_BRAND as vehicleBrand
from position_host_info h
left join position_host_info_extend e
on h.hostid = e.hostid
left join appdeptinfo d
on h.appsysid = d.appsysid
and h.appdeptid = d.appdeptid
left join appdeptinfo o
on e.owner_appsysid = o.appsysid
and e.owner_appdeptid = o.appdeptid
where exists (select appsysid, appdeptid
from table(get_subdept(8196, 23551, 3)) g
where g.appsysid = e.owner_appsysid
and g.appdeptid = e.owner_appdeptid)) t
where exists (select appsysid, appdeptid
from table(get_subdept(-1, 23551, 3)) g
where g.appsysid = t.appsysid
and g.appdeptid = t.appdeptid)
order by t.updatetimeorder desc, t.hostno
--[8196, 23551, 3, 8196, 23551, 3, 23551, 3]
--bao1人事处登录
select *
from (select h.hostid,
h.hostno,
e.verify_status as verifyStatus,
e.host_brand as hostBrand,
d.full_name as usefullname,
o.full_name as ownfullname,
h.displacement,
e.displacement_type as displacementType,
e.asset_attribute as assetAttribute,
e.purchase_type as purchaseType,
to_char(e.purchase_date, 'yyyy-mm-dd') as purchaseDate,
e.usage_property as usageProperty,
e.host_purpose as hostPurpose,
e.host_veh_type as hostVehType,
e.host_price_with_tax as hostPriceWithTax,
e.host_price as hostPrice,
e.host_tax as hostTax,
h.original_mile as originalMile,
e.engine_id as engineId,
e.host_vin as hostVin,
h.appsysid,
h.appdeptid,
e.allot_type as allotType,
to_char(e.allot_date, 'yyyy-mm-dd') as allotDate,
e.service_object as serviceObject,
e.keeper as kepper,
e.keeper_phone as kepperPhone,
e.owner_appsysid as ownerAppSysId,
e.owner_appdeptid as ownerAppDeptId,
e.fixed_asset_id as fixedAssetId,
e.host_tag_flag as hostTagFlag,
e.process_status as processStatus,
e.manufacturer,
e.oil_type as oilType,
e.xszh,
h.hostno_color as hostNoColor,
e.host_color as hostColor,
e.rated_kg as ratedKg,
e.CREATE_USER_ID as CREATEUSERID,
h.UPDATESTATUSTIME as updatetimeorder,
e.veh_kg as vehKg,
e.tyre_size as tyreSize,
e.rated_passenger as ratedPassenger,
e.remark,
h.CREATUSERID as basecrtuser,
e.CREATE_USER_NAME as createUserName,
e.CREATE_TIME as createTime,
e.UPDATE_USER_NAME as updateUserName,
e.UPDATE_TIME as updateTime,
d.DEPT_HIERARCHY as uselevel,
d.DEPT_PROPERTY as useprop,
o.DEPT_HIERARCHY as ownLevel,
o.DEPT_PROPERTY as ownProp,
e.DRIVING_LICENSE_DEPTID as drivingLicenseDeptId,
e.DRIVING_LICENSE_SYSID as drivingLicenseSysId,
to_char(e.DRIVING_LICENSE_DATE, 'yyyy-mm-dd') as drivingLicenseDate,
e.STATE_OF_ORIGIN as stateOfOrigin,
e.HAS_PASSPORT as hasPassport,
e.HAS_ETC as hasETC,
to_char(e.ANNUAL_SURVEY_DATE, 'yyyy-mm-dd') as annualSurveyDate,
e.UPDATE_RECORDS as updateRecords,
e.VEHICLENO as vehicleNo,
e.VEHICLE_MODEL as vehicleModel,
e.VEHICLE_BRAND as vehicleBrand
from position_host_info h
left join position_host_info_extend e
on h.hostid = e.hostid
left join appdeptinfo d
on h.appsysid = d.appsysid
and h.appdeptid = d.appdeptid
left join appdeptinfo o
on e.owner_appsysid = o.appsysid
and e.owner_appdeptid = o.appdeptid
where exists (select appsysid, appdeptid
from table(get_subdept(8196, 23551, 3)) f
where h.appsysid = f.appsysid
and h.appdeptid = f.appdeptid)
union
select h.hostid,
h.hostno,
e.verify_status as verifyStatus,
e.host_brand as hostBrand,
d.full_name as usefullname,
o.full_name as ownfullname,
h.displacement,
e.displacement_type as displacementType,
e.asset_attribute as assetAttribute,
e.purchase_type as purchaseType,
to_char(e.purchase_date, 'yyyy-mm-dd') as purchaseDate,
e.usage_property as usageProperty,
e.host_purpose as hostPurpose,
e.host_veh_type as hostVehType,
e.host_price_with_tax as hostPriceWithTax,
e.host_price as hostPrice,
e.host_tax as hostTax,
h.original_mile as originalMile,
e.engine_id as engineId,
e.host_vin as hostVin,
h.appsysid,
h.appdeptid,
e.allot_type as allotType,
to_char(e.allot_date, 'yyyy-mm-dd') as allotDate,
e.service_object as serviceObject,
e.keeper as kepper,
e.keeper_phone as kepperPhone,
e.owner_appsysid as ownerAppSysId,
e.owner_appdeptid as ownerAppDeptId,
e.fixed_asset_id as fixedAssetId,
e.host_tag_flag as hostTagFlag,
e.process_status as processStatus,
e.manufacturer,
e.oil_type as oilType,
e.xszh,
h.hostno_color as hostNoColor,
e.host_color as hostColor,
e.rated_kg as ratedKg,
e.CREATE_USER_ID as CREATEUSERID,
h.UPDATESTATUSTIME as updatetimeorder,
e.veh_kg as vehKg,
e.tyre_size as tyreSize,
e.rated_passenger as ratedPassenger,
e.remark,
h.CREATUSERID as basecrtuser,
e.CREATE_USER_NAME as createUserName,
e.CREATE_TIME as createTime,
e.UPDATE_USER_NAME as updateUserName,
e.UPDATE_TIME as updateTime,
d.DEPT_HIERARCHY as uselevel,
d.DEPT_PROPERTY as useprop,
o.DEPT_HIERARCHY as ownLevel,
o.DEPT_PROPERTY as ownProp,
e.DRIVING_LICENSE_DEPTID as drivingLicenseDeptId,
e.DRIVING_LICENSE_SYSID as drivingLicenseSysId,
to_char(e.DRIVING_LICENSE_DATE, 'yyyy-mm-dd') as drivingLicenseDate,
e.STATE_OF_ORIGIN as stateOfOrigin,
e.HAS_PASSPORT as hasPassport,
e.HAS_ETC as hasETC,
to_char(e.ANNUAL_SURVEY_DATE, 'yyyy-mm-dd') as annualSurveyDate,
e.UPDATE_RECORDS as updateRecords,
e.VEHICLENO as vehicleNo,
e.VEHICLE_MODEL as vehicleModel,
e.VEHICLE_BRAND as vehicleBrand
from position_host_info h
left join position_host_info_extend e
on h.hostid = e.hostid
left join appdeptinfo d
on h.appsysid = d.appsysid
and h.appdeptid = d.appdeptid
left join appdeptinfo o
on e.owner_appsysid = o.appsysid
and e.owner_appdeptid = o.appdeptid
where exists (select appsysid, appdeptid
from table(get_subdept(8196, 23551, 3)) g
where g.appsysid = e.owner_appsysid
and g.appdeptid = e.owner_appdeptid)) t
where t.appsysid = 23551
and t.appdeptid = 3
order by t.updatetimeorder desc, t.hostno
519
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
