MySQL SELECT 查询 WHERE "something is TRUE"

Question

我的数据库中有一个“用户”表，不幸的是里面有很多乱七八糟的东西，我试着把它移到新的。但只是精确的东西而不是复制/粘贴整个。

这里是数据库的例子:

    -------*/*------------*/*------------*/*------------*/*------------*/*------------
      id        level          name           kind         status          parentId
    -------*/*------------*/*------------*/*------------*/*----------*/*------------
    EMD123F |     2        |   OrgName1   |     Org      |            |  rootID
    ---------------------------------------------------------------------------------
    DHAD781 |     3        |   UserName1  |    Person    |   active   |  EMD123F 
    ---------------------------------------------------------------------------------
    7AJIZU7 |     3        |   UserName2  |    Person    |   active   |  EMD123F 
    ---------------------------------------------------------------------------------
    DME123F |     2        |   OrgName2   |     Org      |            |  rootID
    ---------------------------------------------------------------------------------
    TT5451AL|     3        |   UserName3  |    Person    |   active   |  DME123F 
    ---------------------------------------------------------------------------------
    RRMI7481|     2        |   OrgName3   |     Org      |            |  rootID
    ---------------------------------------------------------------------------------
    PPUNSAD9|     2        |   OrgName4   |     Org      |            |  rootID
    ---------------------------------------------------------------------------------
    GJASDNZB|     3        |   UserName4  |    Person    |  inactive  |  PPUNSAD9
    ---------------------------------------------------------------------------------
    KJNSCZM7|     2        |   OrgName5   |     Org      |            |  rootID
    ---------------------------------------------------------------------------------
    1UZGOPAS|     3        |   UserName5  |    Person    |   deleted  |  KJNSCZM7
    ---------------------------------------------------------------------------------

你在这里看到的是，有些“组织”有 0 个用户，所以绝对没用，有些组织有用户但他们不活跃(不活跃，删除......)。

我的问题是如何编写一个 select 语句来获取其中至少有 1 个活跃人员的所有组织。

RealData 有点复杂，但我尝试这样:

UPDATE users set org_status=1 WHERE (select count(STATUS) FROM users WHERE users.MEMBERKIND="Person" AND users.STATUS="Active" AND users.ROOTORGANIZATIONUSERID= users.ROOTORGANIZATIONIDCORRECTED)>0 AND MEMBERKIND = "Organization" AND LEVEL=2

我创建新行“org_status”并将其设置为 NULL，然后尝试更新它

Answer 1

select t1.*
from your_table t1
join your_table t2 on t2.parentId = t1.id
where t2.status = 'active'