时间戳

首页 > 解决方案 > SQL 查询逻辑，用于获取所有可能的层次结构的 IsActive 为 false 的 Id

sql - SQL 查询逻辑，用于获取所有可能的层次结构的 IsActive 为 false 的 Id

问题描述

最终结果不应包括 id 24，因为此 id 具有与公司层次结构的活动映射：1176->1781->1787 并且 1787 在第二个表中处于活动状态。我尝试的查询无法获得所需的结果。

样本数据：

Table 1 #tempCTE_CompMCompDir: 

Id  CompanyId   ChildId
24  1776        1781
24  1776        1782
25  1777        1783
24  1781        1786
24  1781        1787

Table 2 #tempContComM:
CompanyId   ContactId   IsActive
1787            2903    1
1783            2903    0
1778            2903    0
1786            2903    0

输入：

  1. 表 1 有分层数据公司和孩子。

  2. 表 2 具有 IsActive 为真或假的叶级公司。

输出：

如果表 1 中的 Company Id 或 Child Id 通过层次结构映射到表 2 的 Company Id 并且此映射的 IsActive 在所有情况下都是错误的。

样本数据的输出应该是：

Id
25

我的错误输出查询是：

SELECT CTE_CompMCompDir.Id
    FROM #tempCTE_CompMCompDir  CTE_CompMCompDir
    JOIN #tempContComM          tempContComM
        ON   ( CTE_CompMCompDir.CompanyId  = tempContComM.CompanyId 
                    AND tempContComM.IsActive = 0)
            OR ( CTE_CompMCompDir.ChildId     = tempContComM.CompanyId
                    AND tempContComM.IsActive = 0)

标签： sqlsql-servertsqlhierarchical-data

解决方案

如果您只在第二个表中查找所有错误 Isactive 值的所有子行条目，则可能的解决方案

live demo link 

create table tempCTE_CompMCompDir (Id  int, CompanyId int,   ChildId int)

insert into tempCTE_CompMCompDir
values
(24,  1776,  1781),
(24,  1776,  1782),
(25,  1777,  1783),
(24,  1781,  1786),
(24,  1781,  1787);


create table tempContComM(CompanyId  int,  ContactId  int, IsActive bit);

insert into tempContComM 
values
(1787  ,2903 , 1),
(1783  ,2903 , 0),
(1778  ,2903 , 0),
(1786  ,2903 , 0);

select t.* from tempCTE_CompMCompDir t join (
    select 
    id,value=max(case when ISNULL(Isactive,-1)=0 then 0 else 1 end)
    from tempCTE_CompMCompDir l 
        left join tempContComM r
            on l.companyid=r.companyid or l.childid=r.companyid
    group by id 
    ) t2 
        on t.id=t2.id and t2.value=0

几点注意事项：

  1. 我将丢失的映射值视为既不假也不真
  2. 你可以调整 max (case..) 来改变你的规则和标准

推荐阅读