如果任何子值为Null，则使用SQL显示父记录FALSE

I have a collection of parent items and each has an indeterminate set of child items. Each child item as an attribute that is X or NULL. (Apologies for the odd Boolean structure, but that's what I have to work with.)

我有一组父项，每个项都有一组不确定的子项。每个子项作为X或NULL的属性。（为奇怪的布尔结构道歉，但这是我必须使用的。）

Parent  Child  Attribute
------  -----  ---------
  A       1       X
  A       2       X
  A       3      NULL

  B       1       X
  B       2      NULL
  B       3      NULL

  C       1       X
  C       2       X

  D       1      NULL

  E       1      NULL
  E       2      NULL

I want to identify parent items as True or False based on the child items. One NULL value will result in a False return for that parent item. Here are the desired results:

我想根据子项将父项标识为True或False。一个NULL值将导致该父项的False返回。这是期望的结果：

  A       False
  B       False
  C       True
  D       False
  E       False

The ideal solution would even handle a parent that has no child records (result is False).

理想的解决方案甚至可以处理没有子记录的父级（结果为False）。

I can do this using temporary tables. That solution is fairly long and I don't think presenting it would add any value to this post.

我可以使用临时表来做到这一点。这个解决方案相当长，我不认为它会给这篇文章增加任何价值。

How can I do this with an SQL query using no temporary tables?

如何使用不使用临时表的SQL查询来执行此操作？

I would present "things I've tried" but I don't know enough how to even start.

我会提出“我尝试过的东西”但我不知道如何开始。

2 个解决方案

#1

I don't think this is equivalent to nor. You just want to know if any values are NULL -- or equivalently if all values are 'X'. Use case and aggregation:

我不认为这相当于也不是。您只想知道任何值是否为NULL - 或等效所有值都是'X'。用例和聚合：

select parent,
       (case when count(*) = count(attribute) then 'true'
             else 'false'
        end)
from t
group by parent;

To handle a parent with no children requires a list of parents, separate from this list:

要处理没有子节点的父节点，需要父节点列表，与此列表分开：

select p.parent,
       (case when count(*) = count(t.attribute) and count(t.parent) > 0 then 'true'
             else 'false'
        end)
from parents p left join
     t
     on p.parent = t.parent
group by p.parent;

As a reminder, count(*) counts the number of rows in the result set. COUNT() with an expression (including a column) counts the number of non-NULL values. When these are the same, there are no non-NULL values.

提醒一下，count（*）计算结果集中的行数。带表达式（包括列）的COUNT（）计算非NULL值的数量。当它们相同时，没有非NULL值。

#2

You can do this via aggregates..,when no attributes,i have kept the column as blank..

你可以通过聚合来做到这一点..，当没有属性时，我将列保持为空白..

In summary assign true or false to each row and get min value

总之，为每一行指定true或false并获得最小值

create table #test
(
id int,
name char(2)
)

insert into #test

select 1,'x'
union all
select 1,null
union all
select 2,null
union all
select 3,''

with cte
as
(
select  id,b.*
from #test t1
cross apply
(
select case when name is null or name='' then 'False' Else 'True' end as 'chk' from #test t2 where t2.id=t1.id ) b
)
select id,min(chk)
from
cte group by id

You can remove even cross apply:

您甚至可以删除交叉申请：

with cte
as
(
select  id,case when name is null or name='' then 'False' Else 'True' end as 'chk'
from #test t1
)
select id,min(chk)
from
cte group by id

Output:

输出：

1   False
2   False
3   False

#1