基于条件父级到子层次结构的sql筛选行

Question

厄温，我的错误与正确的例子和数据。如有需要，请在下面找到更新的查询和所有数据。我们有主表，其中包含资产ID之间的所有关系，如下所示。资产层次结构是S1，它有F11、F12、F13，而F11则有D111、D112、D113，同样F12有D121、D122、D123等。

母版表和插入脚本

请参阅链接木琴

CREATE TABLE tbl2 (c_id text ,d_id text, f_id text, s_id text);
INSERT INTO tbl2 values('100000019403','D111','F11','S1');
INSERT INTO tbl2 values('100000019404','D112','F11','S1');
INSERT INTO tbl2 values('100000019405','D113','F11','S1');
INSERT INTO tbl2 values('100000019406','D111','F11','S1');
INSERT INTO tbl2 values('100000019407','D112','F11','S1');
INSERT INTO tbl2 values('100000019408','D121','F12','S1');
INSERT INTO tbl2 values('100000019409','D122','F12','S1');
INSERT INTO tbl2 values('100000019410','D123','F12','S1');
INSERT INTO tbl2 values('100000019411','D123','F12','S1');
INSERT INTO tbl2 values('100000019412','D141','F14','S1');
INSERT INTO tbl2 values('100000019413','D131','F13','S1');
INSERT INTO tbl2 values('100000019414','D142','F14','S1');
INSERT INTO tbl2 values('100000019415','D132','F13','S1');
INSERT INTO tbl2 values('100000019416','D143','F14','S1');
INSERT INTO tbl2 values('100000019417','D133','F13','S1');
INSERT INTO tbl2 values('100000019418','D144','F14','S1');
INSERT INTO tbl2 values('100000019419','D211','F21','S2');
INSERT INTO tbl2 values('100000019420','D212','F21','S2');
INSERT INTO tbl2 values('100000019421','D213','F21','S2');
INSERT INTO tbl2 values('100000019422','D211','F21','S2');
INSERT INTO tbl2 values('100000019423','D212','F21','S2');
INSERT INTO tbl2 values('100000019424','D221','F22','S2');
INSERT INTO tbl2 values('100000019425','D222','F22','S2');
INSERT INTO tbl2 values('100000019426','D223','F22','S2');
INSERT INTO tbl2 values('100000019427','D223','F22','S2');
INSERT INTO tbl2 values('100000019428','D241','F24','S2');
INSERT INTO tbl2 values('100000019429','D231','F23','S2');
INSERT INTO tbl2 values('100000019430','D242','F24','S2');
INSERT INTO tbl2 values('100000019431','D232','F23','S2');
INSERT INTO tbl2 values('100000019432','D243','F24','S2');
INSERT INTO tbl2 values('100000019433','D233','F23','S2');
INSERT INTO tbl2 values('100000019434','D244','F24','S2');
INSERT INTO tbl2 values('100000019435','D311','F31','S3');
INSERT INTO tbl2 values('100000019436','D312','F31','S3');
INSERT INTO tbl2 values('100000019437','D313','F31','S3');
INSERT INTO tbl2 values('100000019438','D311','F31','S3');
INSERT INTO tbl2 values('100000019439','D312','F31','S3');
INSERT INTO tbl2 values('100000019440','D321','F32','S3');
INSERT INTO tbl2 values('100000019441','D322','F32','S3');
INSERT INTO tbl2 values('100000019442','D323','F32','S3');
INSERT INTO tbl2 values('100000019443','D323','F32','S3');
INSERT INTO tbl2 values('100000019444','D341','F34','S3');
INSERT INTO tbl2 values('100000019445','D331','F33','S3');
INSERT INTO tbl2 values('100000019446','D342','F34','S3');
INSERT INTO tbl2 values('100000019447','D332','F33','S3');
INSERT INTO tbl2 values('100000019448','D343','F34','S3');
INSERT INTO tbl2 values('100000019449','D333','F33','S3');
INSERT INTO tbl2 values('100000019450','D344','F34','S3');
INSERT INTO tbl2 values('100000019451','D111','F11','S1');
INSERT INTO tbl2 values('100000019452','D112','F11','S1');
INSERT INTO tbl2 values('100000019453','D113','F11','S1');
INSERT INTO tbl2 values('100000019454','D111','F11','S1');
INSERT INTO tbl2 values('100000019455','D112','F11','S1');
INSERT INTO tbl2 values('100000019456','D121','F12','S1');
INSERT INTO tbl2 values('100000019457','D122','F12','S1');
INSERT INTO tbl2 values('100000019458','D123','F12','S1');
INSERT INTO tbl2 values('100000019459','D123','F12','S1');
INSERT INTO tbl2 values('100000019460','D141','F14','S1');
INSERT INTO tbl2 values('100000019461','D131','F13','S1');
INSERT INTO tbl2 values('100000019462','D142','F14','S1');
INSERT INTO tbl2 values('100000019463','D132','F13','S1');
INSERT INTO tbl2 values('100000019464','D143','F14','S1');
INSERT INTO tbl2 values('100000019465','D133','F13','S1');
INSERT INTO tbl2 values('100000019466','D144','F14','S1');

我们有带有资产ID的事务表，它只提供有关失败资产的信息。假设如果处于较高级别的资产ID失败，那么子数据也会被填充，但是在获取记录时，我们需要忽略子记录，只获取最高级别的记录。

事务数据可用3天，这将讨论三种不同的方案。场景1，对于讨论S1资产的2013-10-10年，它失败了，这也给出了需要忽略的子记录。

场景2的2013年10-11，其中谈到F21，F22，F23的失败和它的子记录。我们得忽视孩子们。注意到资产ID S1没有失败，因此不会出现在场景2的事务表中。

场景3的2013年-10-12，其中谈到F33的失败与其子女D331，D332，D333.我们只需要考虑F33，而忽略孩子。考虑D311，D312，D313，D321，D322和D323。

我们总是需要按startdatetime字段进行分组，因为父记录和子记录对于给定的条目总是具有相同的列值。

请参阅上面的链接，该链接还具有事务表和数据。

CREATE TABLE tbl1 (startdatetime text, enddatetime text, assetid text);

INSERT INTO tbl1  values('2013-10-10 22:10:11', '2013-10-10 22:10:11', 'S1');
 INSERT INTO tbl1  values('2013-10-10 22:10:11', '2013-10-10 22:10:11', 'F11');
 INSERT INTO tbl1  values('2013-10-10 22:10:11', '2013-10-10 22:10:11', 'F12');
 INSERT INTO tbl1  values('2013-10-10 22:10:11', '2013-10-10 22:10:11', 'F13');
 INSERT INTO tbl1  values('2013-10-10 22:10:11', '2013-10-10 22:10:11', 'D111');
 INSERT INTO tbl1  values('2013-10-10 22:10:11', '2013-10-10 22:10:11', 'D112');
 INSERT INTO tbl1  values('2013-10-10 22:10:11', '2013-10-10 22:10:11', 'D113');
 INSERT INTO tbl1  values('2013-10-10 22:10:11', '2013-10-10 22:10:11', 'D121');
 INSERT INTO tbl1  values('2013-10-10 22:10:11', '2013-10-10 22:10:11', 'D122');
 INSERT INTO tbl1  values('2013-10-10 22:10:11', '2013-10-10 22:10:11', 'D123');
 INSERT INTO tbl1  values('2013-10-10 22:10:11', '2013-10-10 22:10:11', 'D131');
 INSERT INTO tbl1  values('2013-10-10 22:10:11', '2013-10-10 22:10:11', 'D132');
 INSERT INTO tbl1  values('2013-10-10 22:10:11', '2013-10-10 22:10:11', 'D133');

 INSERT INTO tbl1  values('2013-10-11 22:10:11', '2013-10-11 22:12:11', 'F21');
 INSERT INTO tbl1  values('2013-10-11 22:10:11', '2013-10-11 22:12:11', 'F22');
 INSERT INTO tbl1  values('2013-10-11 22:10:11', '2013-10-11 22:12:11', 'F23');
 INSERT INTO tbl1  values('2013-10-11 22:10:11', '2013-10-11 22:12:11', 'D211');
 INSERT INTO tbl1  values('2013-10-11 22:10:11', '2013-10-11 22:12:11', 'D212');
 INSERT INTO tbl1  values('2013-10-11 22:10:11', '2013-10-11 22:12:11', 'D213');
 INSERT INTO tbl1  values('2013-10-11 22:10:11', '2013-10-11 22:12:11', 'D221');
 INSERT INTO tbl1  values('2013-10-11 22:10:11', '2013-10-11 22:12:11', 'D222');
 INSERT INTO tbl1  values('2013-10-11 22:10:11', '2013-10-11 22:12:11', 'D223');
 INSERT INTO tbl1  values('2013-10-11 22:10:11', '2013-10-11 22:12:11', 'D231');
 INSERT INTO tbl1  values('2013-10-11 22:10:11', '2013-10-11 22:12:11', 'D232');
 INSERT INTO tbl1  values('2013-10-11 22:10:11', '2013-10-11 22:12:11', 'D233');

 INSERT INTO tbl1  values('2013-10-12 22:10:11', '2013-10-12 22:16:11', 'F33');
 INSERT INTO tbl1  values('2013-10-12 22:10:11', '2013-10-12 22:16:11', 'D311');
 INSERT INTO tbl1  values('2013-10-12 22:10:11', '2013-10-12 22:16:11', 'D312');
 INSERT INTO tbl1  values('2013-10-12 22:10:11', '2013-10-12 22:16:11', 'D313');
 INSERT INTO tbl1  values('2013-10-12 22:10:11', '2013-10-12 22:16:11', 'D321');
 INSERT INTO tbl1  values('2013-10-12 22:10:11', '2013-10-12 22:16:11', 'D322');
 INSERT INTO tbl1  values('2013-10-12 22:10:11', '2013-10-12 22:16:11', 'D323');
 INSERT INTO tbl1  values('2013-10-12 22:10:11', '2013-10-12 22:16:11', 'D331');
 INSERT INTO tbl1  values('2013-10-12 22:10:11', '2013-10-12 22:16:11', 'D332');
 INSERT INTO tbl1  values('2013-10-12 22:10:11', '2013-10-12 22:16:11', 'D333');

预期产出：

根据事务数据表对受影响的使用者进行计数。

请帮助我对上述场景的c_id记录细节，我会得到计数。

Answer 1

case1：s_id存在，然后忽略子行(F_id)和子子行(D_id)

使用CTE，解决方案可以如下所示：

WITH x AS (
   SELECT d_id, f_id
   FROM   tbl2 t2
   JOIN   tbl1 t1 ON t1.assetid = t2.s_id
   )
SELECT t.*
FROM   tbl1 t
LEFT   JOIN d ON d.d_id = t.assetid
LEFT   JOIN f ON f.f_id = t.assetid
WHERE  d.d_id IS NULL
AND    f.f_id IS NULL

解释

• CTE x标识应该忽略的d_id和f_id。
• 主选择左联接到这个排除表x两次，以消除在d_id或f_id上匹配的行。

case2：f_id存在，然后忽略父(S_id)和低(D_id)

WITH x AS (
   SELECT d_id, s_id
   FROM   tbl2 f
   JOIN   tbl1 t ON t.assetid = f.f_id
   )
SELECT t.*
FROM   tbl1 t
LEFT   JOIN x d ON d.d_id = t.assetid
LEFT   JOIN x s ON s.s_id = t.assetid
WHERE  d.d_id IS NULL
AND    s.s_id IS NULL;

-> SQLfiddle demo

case3：d_id存在，而忽略了父s_id，f_id

你明白了，不是吗？