时间戳

首页 > 解决方案 > 选择具有 null 而不是 null 子级的父级

sql - 选择具有 null 而不是 null 子级的父级

问题描述

给定 3 个表，例如：

[Table_Main]   ---->   [Table_Sub]   ---->   [Table_Prop]
                1-N                   0-N

我想选择其中的项目[Table_Main]：
- 有多个[Table_Sub]。
-[Table_Sub]有[Table_Prop]和没有的线。

要选择我使用的那些值：

SELECT      Table_Main.Field_ID
FROM        Table_Main
    INNER JOIN  Table_Sub       on  Table_Main.Field_ID  =  Table_Sub.Table_Main_Field_ID
    LEFT JOIN   Table_Prop      on  Table_Sub.Field_ID   =  Table_Prop.Table_Sub_Field_ID

如果我们重命名表 Family、Child 和 Pet。我需要一些孩子有宠物但有些孩子没有的家庭。

家庭：身份证、姓名

1, Foo      -- Family with 2 childs, one of them has a pet
2, Bar      -- Family with 2 childs, 0 pet
3, Abc      -- Family with 2 childs, both have pet

孩子：ID、Family_Id、姓名

1, 1, John      -- Child of Foo
2, 1, Joe       -- Child of Foo
3, 2, Jane
4, 2, Jessica
5, 3, XXX
6, 3, YYY

宠物：Id、Child_Id、姓名

1, 2, FooBar    -- Joe's pet
2, 5, Huey 
3, 6, Dewey

预期结果：1，Foo

少于 2 个孩子的家庭不包括在示例中，前提是他们可以满足以下两个约束条件：
- 有一个带宠物
的孩子 - 有一个没有宠物的孩子。

表创建：

CREATE TABLE Family(
    1    INTEGER  NOT NULL PRIMARY KEY
    ,Foo  VARCHAR(20) NOT NULL
);
INSERT INTO Family(1,Foo) VALUES (1,'Foo');
INSERT INTO Family(1,Foo) VALUES (2,'Bar');
INSERT INTO Family(1,Foo) VALUES (3,'Abc');


CREATE TABLE Child(
   Id        INTEGER  NOT NULL PRIMARY KEY 
  ,Family_Id INTEGER  NOT NULL
  ,Name      VARCHAR(20) NOT NULL
);
INSERT INTO Child(Id,Family_Id,Name) VALUES (1,1,'John');
INSERT INTO Child(Id,Family_Id,Name) VALUES (2,1,'Joe');
INSERT INTO Child(Id,Family_Id,Name) VALUES (3,2,'Jane');
INSERT INTO Child(Id,Family_Id,Name) VALUES (4,2,'Jessica');
INSERT INTO Child(Id,Family_Id,Name) VALUES (5,3,'XXX');
INSERT INTO Child(Id,Family_Id,Name) VALUES (6,3,'YYY');


CREATE TABLE Pet(
   Id       INTEGER  NOT NULL PRIMARY KEY 
  ,Family_I INTEGER  NOT NULL
  ,Name     VARCHAR(20) NOT NULL
);
INSERT INTO Pet(Id,Family_Id,Name) VALUES (1,2,'FooBar');
INSERT INTO Pet(Id,Family_Id,Name) VALUES (2,5,'Huey');
INSERT INTO Pet(Id,Family_Id,Name) VALUES (3,6,'Dewey');

标签： sqlsql-servertsql

解决方案

这会给你想要的结果。

;with family as
(
    select 1 FamilyID, 'Foo' Family union select 2, 'Bar' union select 3, 'ABC'
), child as
(
    select 1 ChildID, 1 FamilyID ,'John' ChildName union
    select 2, 1, 'Joe' union
    select 3, 2, 'Jane' union
    select 4, 2, 'Jessica' union
    select 5, 3, 'XXX'union
    select 6, 3, 'YYY'
), pets as 
(
    select 1 petid , 2 childid, 'FooBar' pet  union
    select 2, 5, 'Huey' union
    select 3, 6, 'Dewey'  
)

SELECT T.FamilyID, Max(Family) Family, MIN(CNT) [Min] , MAX(CNT) [Max]  FROM 
(
    SELECT f.FamilyID, C.ChildID, SUM(case when petid is null then 0 else 1 end) CNT  FROM Family F 
    JOIN Child C ON F.FamilyID = C.FamilyID 
    LEFT JOIN Pets P ON C.ChildID = P.ChildID
    GROUP BY F.FamilyID, C.ChildID 
    ) T JOIN Family F on T.FamilyID = F.FamilyID GROUP BY T.FamilyID 
HAVING MIN(CNT) = 0 AND  MAX(CNT) > 0

推荐阅读