sql select puzzle：当父项被过滤掉时删除子项

发布于 2024-10-19 06:27:31 字数 747 浏览 3 评论 0原文

我本质上有一个表：

name         has_children      parent_id    row_id values0.....valuesn
parent       1                 1            1
children     0                 1            2
children     0                 1            3
parent       0                 4            4
parent       1                 5            5
children     0                 5            6
children     0                 5            7

子项的值可以与父项的值不同。我想要一些选择/联接，它们将在值列（即> 10）上过滤表，并且如果其中一个子级对于过滤器为真，则将返回父级（即使过滤器为假）。

acceptable return:
parent=true    all children=false, return just parent
parent=false   >=1 children=true, return parent and all non-filtered child

我确信以前已经考虑过这个问题，但我不知道如何表达这个问题以找到解决方案。

原文

I have a table essentially:

name         has_children      parent_id    row_id values0.....valuesn
parent       1                 1            1
children     0                 1            2
children     0                 1            3
parent       0                 4            4
parent       1                 5            5
children     0                 5            6
children     0                 5            7

the values for the children can be different than the values for the parent.
i want some selects/joins that will filter the table on a value column (i.e. >10) and will return the parent (even if false for the filter) if one of it's children is true for the filter.

acceptable return:
parent=true    all children=false, return just parent
parent=false   >=1 children=true, return parent and all non-filtered child

i'm sure this has been thought about before but i don't have the faintest idea how to phrase the question to find a solution.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

喜爱纠缠 2024-10-26 06:27:31

符合 ANSI 标准。每个特定的 DBMS 可能有更快的实现

select *
from tbl
where id in-- PARENTS of CHILDREN that match
(   select parent_id from tbl
    where values0 > 10 and has_children = 0)
or id in   -- ONE CHILD ONLY
(   select MIN(id) from tbl
    where values0 > 10 and has_children = 0
    group by parent_id)
or id in   -- PARENTS
(   select id from tbl
    where values0 > 10 and has_children = 1)

更好地编写为 JOIN

select t.*
from 
(   select parent_id as ID from tbl
    where values0 > 10 and has_children = 0
    UNION
    select MIN(id) from tbl
    where values0 > 10 and has_children = 0
    group by parent_id
    UNION
    select id from tbl
    where values0 > 10 and has_children = 1) X
join tbl t on X.ID = t.ID

ANSI compliant. Each specific DBMS may have a faster implementation

select *
from tbl
where id in-- PARENTS of CHILDREN that match
(   select parent_id from tbl
    where values0 > 10 and has_children = 0)
or id in   -- ONE CHILD ONLY
(   select MIN(id) from tbl
    where values0 > 10 and has_children = 0
    group by parent_id)
or id in   -- PARENTS
(   select id from tbl
    where values0 > 10 and has_children = 1)

Better written as a JOIN

select t.*
from 
(   select parent_id as ID from tbl
    where values0 > 10 and has_children = 0
    UNION
    select MIN(id) from tbl
    where values0 > 10 and has_children = 0
    group by parent_id
    UNION
    select id from tbl
    where values0 > 10 and has_children = 1) X
join tbl t on X.ID = t.ID

回复收藏 0 原文