合并和删除数据库中的值

Question

我有Question_Master和Assessment_Master桌子。在question_Master中，我有一些关于亲子关系的问题。当我回答父级问题时，当我过滤已回答的按钮筛选器时，我需要显示父级和子级。当我回答子项问题时，当我筛选已回答按钮筛选器时，我需要显示父项和子项。但我的问题是，当我回答这两个问题时，我得到的是孩子的副本。

当回答家长问题时，它得到的是父子关系：

  SELECT a.QM_ID,a.QM_QCM_ID,
       a.QM_Question,a.QM_Type,
       a.QM_Parent_Id,
       c.AM_Answer, 
       c.AM_Comments 
  FROM question_master a 
       INNER JOIN Assessment_master c 
             ON (c.AM_QM_ID = a.QM_ID 
                 AND c.AM_HNM_ID = %d 
                 AND c.AM_HM_ID = %d 
                 AND c.AM_ASM_Local_Id = %@) 
 WHERE a.QM_Parent_Id = 0 
       AND a.QM_Status = 'A' 
       AND a.QM_QCM_ID = %@ 
       AND a.QM_QRM_Id = %@

UNION
SELECT b.QM_ID,
       b.QM_QCM_ID,
       b.QM_Question,
       b.QM_Type,
       b.QM_Parent_Id,
       null, 
       null 
  FROM question_master b 
       INNER JOIN Assessment_master d 
             ON (d. AM_QM_ID = b.QM_Parent_Id 
                 AND d.AM_HNM_ID = %d 
                 AND d.AM_HM_ID = %d 
                 AND d.AM_ASM_Local_Id = %@) 
 WHERE b.QM_Parent_Id != 0 
       AND b.QM_Status = 'A' 
       AND b.QM_QCM_ID = %@ 
       AND b.QM_QRM_Id = %@

当我回答孩子的问题时，它得到了父母-孩子：

    SELECT a.QM_ID,a.QM_QCM_ID,
       a.QM_Question,a.QM_Type,
       a.QM_Parent_Id,
       c.AM_Answer, 
       c.AM_Comments 
  FROM question_master a 
       INNER JOIN Assessment_master c 
             ON (c.AM_QM_ID = a.QM_ID 
                 AND c.AM_HNM_ID = %d 
                 AND c.AM_HM_ID = %d 
                 AND c.AM_ASM_Local_Id = %@) 
 WHERE a.QM_Parent_Id = 0 
       AND a.QM_Status = 'A' 
       AND a.QM_QCM_ID = %@ 
       AND a.QM_QRM_Id = %@

UNION
SELECT b.QM_ID,
       b.QM_QCM_ID,
       b.QM_Question,
       b.QM_Type,
       b.QM_Parent_Id,
       d.AM_Answer, 
       d.AM_Comments 
  FROM question_master b 
       INNER JOIN Assessment_master d 
             ON (d. AM_QM_ID = b.QM_ID 
                 AND d.AM_HNM_ID = %d 
                 AND d.AM_HM_ID = %d 
                 AND d.AM_ASM_Local_Id = %@) 
 WHERE b.QM_Parent_Id IN (SELECT QM_ID 
                            FROM question_master 
                           WHERE QM_Parent_Id = 0 
                                 AND QM_Status = 'A' 
                                 AND QM_QCM_ID = %@ 
                                 AND QM_QRM_Id = %@)

我使用了上面的组合查询：

 SELECT a.QM_ID,a.QM_QCM_ID,
       a.QM_Question,a.QM_Type,
       a.QM_Parent_Id,
       c.AM_Answer, 
       c.AM_Comments 
  FROM question_master a 
       INNER JOIN Assessment_master c 
             ON (c.AM_QM_ID = a.QM_ID 
                 AND c.AM_HNM_ID = %d 
                 AND c.AM_HM_ID = %d 
                 AND c.AM_ASM_Local_Id = %@) 
 WHERE a.QM_Parent_Id = 0 
       AND a.QM_Status = 'A' 
       AND a.QM_QCM_ID = %@ 
       AND a.QM_QRM_Id = %@

UNION
SELECT b.QM_ID,
       b.QM_QCM_ID,
       b.QM_Question,
       b.QM_Type,
       b.QM_Parent_Id,
       null, 
       null 
  FROM question_master b 
       INNER JOIN Assessment_master d 
             ON (d. AM_QM_ID = b.QM_Parent_Id 
                 AND d.AM_HNM_ID = %d 
                 AND d.AM_HM_ID = %d 
                 AND d.AM_ASM_Local_Id = %@) 
 WHERE b.QM_Parent_Id != 0 
       AND b.QM_Status = 'A' 
       AND b.QM_QCM_ID = %@ 
       AND b.QM_QRM_Id = %@

UNION
SELECT b.QM_ID,
       b.QM_QCM_ID,
       b.QM_Question,
       b.QM_Type,
       b.QM_Parent_Id,
       d.AM_Answer, 
       d.AM_Comments 
  FROM question_master b 
       INNER JOIN Assessment_master d 
             ON (d. AM_QM_ID = b.QM_ID 
                 AND d.AM_HNM_ID = %d 
                 AND d.AM_HM_ID = %d 
                 AND d.AM_ASM_Local_Id = %@) 
 WHERE b.QM_Parent_Id IN (SELECT QM_ID 
                            FROM question_master 
                           WHERE QM_Parent_Id = 0 
                                 AND QM_Status = 'A' 
                                 AND QM_QCM_ID = %@ 
                                 AND QM_QRM_Id = %@)

但是在这里我得到了额外的子复制值。当我使用LEFT it getting all子问题时，如果我使用EXCEPT回答父问题，它不会获得父子关系。

Question_Master：

QM_ID    QM_QRM_ID   QM_LCM_ID  QM_QCM_ID   QM_Question       QM_Parent_Id

432         5            19       1         question_parent       0
433         5            19       1         question_child        432
434         5            19       1           question_child1      432

Assessment_Master：

AM_ID     AM_UM_ID   AM_ASM_Local_Id  AM_QM_ID   AM_Answer   AM_Comments  AM_HNM_ID
1          8            1                433       NULL        testing      1

如果我回答了孩子的问题433，我得到了唯一的父母和433个孩子。我没有得到434的问题。

a.QM_ID  a.QM_QCM_ID  a.QM_Question     a.QM_Parent_Id c.AM_Answer  c.AM_Comments
         432       1          question_parent     0                Null        NULL
          433      1          question_child       432             null        value

Answer 1

您使用的是哪个数据库引擎？

您是否尝试过重复创建简单的父子关系？

如果ORACLE随后查看了CONNECT BY PRIOR，

如果是MS，那么这里是等价的：Simulation of CONNECT BY PRIOR of ORACLE in SQL SERVER。

您将不必使用任何联合，并且结果将更容易过滤和处理。

Answer 2

我假设以下几点：

• 您正在使用SQLite或MySQL
• 有问题的最后一行qm_id为434而不是433 (否则我看不到主键)
• 您希望在一行中显示所有内容，并且子项没有自己的

的子项

然后，这给出了您在评论中所要求的内容( QM_ID 432,433,434)：

SELECT a.QM_ID,GROUP_CONCAT(b.QM_ID) 
FROM question_master a JOIN 
     question_master b ON a.QM_ID = b.QM_PARENT_ID
GROUP BY a.QM_ID;

从assessment_master添加所需的任何内容。如果你有更深的层次结构，这在不同的平台上也是不同的。如果你想把它们放在不同的行上，那很简单。