Oracle queries the current asset status, and another database, (query the first in duplicate data), subquery as a field query

Background: Oracle queries the current asset status, including asset information (table 1), Asset maintenance status (table 2), Asset retirement status (table 3)

As follows:

Asset Information:

Asset Repair:

Asset retirement:

After the asset application is finished, you can apply for maintenance and scrap of the current asset, with two processes in service, one for maintenance and then maintenance. The corresponding need to start the corresponding process to apply for repair or scrap

The process table is as follows:

Query all current assets and corresponding asset status (repair status, scrap status): SQL statements are as follows:

Select t.asset_id as AssetID,
T.asset_name as Assetname,
T.asset_sort as Assetsort,
T.asset_model as Assetmodel,
T.birth_date as BirthDate,
T.international_number as Internationalnumber,
T.manufacturer as Manufacturer,
T.purchase_date as Purchasedate,
T.net_salvage as Netsalvage,
T.expected_year as Expectedyear,
T.net_value as Netvalue,
T.original_value as OriginalValue,
T.depreciation_method as Depreciationmethod,
T.service_condition as Servicecondition,
T.storeman as Storeman,
T.remark as REMARK,
T.add_type as AddType,
T.processinstanceid as Processinstanceid,
T.custody_dept as Custodydept,
T.used_year as Usedyear,
T.id as ID,
M.status as Clearstatus,
--Nn.status as Maintainstatus,
(Select A.status
From (select a.asset_id, B.status
From Asset_maintain A
Join Workflow.workflow_execution b
On b.processinstanceid = A.processinstanceid
ORDER BY b.create_time Desc) A
where a.asset_id = t.asset_id
and rownum = 1) as Maintainstatus
From Asset_info t
Left JOIN (SELECT *
From Asset_clear C
Join Workflow.workflow_execution N
On c.processinstanceid = N.processinstanceid) m
On t.asset_id = m.asset_id

Here's the point:

Comprehension: Subqueries can be used as business fields, and can be associated with the table of the current main query, taking out the data they want

Oracle queries the current asset status, and another database, (query the first in duplicate data), subquery as a field query