--以下SQL建立孤立CI视图，既不在关系表 源中也不在 对端 中的CI
create or replace viewcmdb_nr as
(selectc.classid,c.instanceid from
(selecta1.classid,a1.instanceid,b1.destination_instanceid from
(select * from
(selecta.classid,a.instanceid,b.source_instanceid
frombmc_core_bmc_baseelement a left join bmc_core_bmc_baserelationship b ona.instanceid=b.source_instanceid
where a.datasetid ='BMC.ASSET' and a.markasdeleted <> 1 --此处过滤指定数据集的未删除CI
) t--此处建立第一层View数据，即不在关系中源CI
wheret.source_instanceid is null
) a1 left joinbmc_core_bmc_baserelationship b1 on a1.instanceid = b1.destination_instanceid
) c wherec.destination_instanceid is null --此处建立第二层View数据，即不在关系中对端CI
)
create or replace viewcmdb_nr as
(selectc.classid,c.instanceid from
(selecta1.classid,a1.instanceid,b1.destination_instanceid from
(select * from
(selecta.classid,a.instanceid,b.source_instanceid
frombmc_core_bmc_baseelement a left join bmc_core_bmc_baserelationship b ona.instanceid=b.source_instanceid
where a.datasetid ='BMC.ASSET' and a.markasdeleted <> 1 --此处过滤指定数据集的未删除CI
) t--此处建立第一层View数据，即不在关系中源CI
wheret.source_instanceid is null
) a1 left joinbmc_core_bmc_baserelationship b1 on a1.instanceid = b1.destination_instanceid
) c wherec.destination_instanceid is null --此处建立第二层View数据，即不在关系中对端CI
)
