问题描述
我有一个表Demo，其中包含以下记录，
Hi, I have a table "Demo" which has following record,
GroupId GroupName ParentGroupId IsADGroup
----------- -------------------------------------------------- ------------- ---------
1 Default NULL 0
6 Hs-First Floor 1 0
7 Hs-First Floor-1 6 0
8 Hs-Second Floor 6 0
9 Hs-Third Floor 6 0
10 Hs-Second Floor-1 8 0
11 Hs-Second Floor -1-1 10 0在这些编辑过程中，我希望得到那些组ID为&父组ID是
不在该记录中。
例如：假设我想编辑Hs-第一层（组ID = 6 ）在那种情况下，基于那个id，
我想要显示一个记录的parentid&组ID不是6，我也不想显示组ID =10& 11。因为他的父id =8和10，而8和10的父ID = 6。
所以，这里预期的结果应该是1默认NULL 0
In these while editing I want to get those record whose group id & parent group id is
not in that record.
for Example: Suppose I want to edit Hs- first Floor (Group Id=6) in that case Based on that id,
I want to show a record whose parentid & group id is not a "6" and also I don''t want to show group id="10" & "11" .because his parent id="8 and 10", and "8 and 10 has parent id=6."
So, here expected result should be 1 Default NULL 0
推荐答案
SELECT DISTINCT A.* from Demo A LEFT JOIN DEMO B ON A.GroupID != B.ParentGroupId
WHERE ISNULL(A.ParentGroupId,-99) != 6 AND ISNULL(A.ParentGroupId,-99) NOT IN ( SELECT DISTINCT A.GroupID from Demo A LEFT JOIN DEMO B ON A.GroupID = B.ParentGroupId WHERE B.ParentGroupId !=6)
希望它能解决你的问题（我知道它看起来很奇怪，但为了避免递归它可以使用）
如果这对您来说是正确的，请将其标记为答案
Hope that it should solve your problem(i know it looks weird but for sake of avoiding recursion it can be used)
Please mark it as answer if this seems correct to you
这篇关于用于过滤记录的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！