SQL查询来比较列中的每个条目和另一个列中的每个条目

Question

我有以下NIST 800-53控件的邻接列表：

id    item        content         parent
1     Checklists  null            0
2     NIST80053   NIST 80053      1
3     AC          Access Control  2
4     1           POLICY..        3
5     2           ACCOUNT..       3
6     3           ACCESS..        3

我正在使用的NIST版本(800-53a)是以一种不使用特定item的方式构造的。任何这样的item都不会有它在parent列中提到的对应的item。我正在设计一个SQL查询，它检查*每个id，看看它是否在“父”列中提到过，并显示任何成功的结果。

现在我得到的最接近的语句是这个简单的语句，它不起作用，因为它只检查每个行的id到同一个行父(这总是不相等的，因为一个条目不能是它自己的父项)。该行不只是获取未分配的项，而是返回整个表：

 SELECT * FROM MySQL_IMPORTv2 where category_id != parent;

我对SQL很陌生，所以希望我在这里没有遗漏一些明显的东西，但是任何帮助都是值得感谢的。

干杯,

哑光

Answer 1

幸运的是，使用exists()语句很容易做到这一点。虽然我通常不建议对子查询进行限定，但exsits()的效率与它们所提供的一样高。如果子查询找到一个结果(任何匹配的结果)，它将立即停止并返回true。

下面的查询将返回列为另一个id的父级的任何id的所有四个字段

select
  id
  , item
  , content
  , parent
from
  MySQL_IMPORTv2
where
  exists (
    select
      1
    from
      MySQL_IMPORTv2 M
    where
      M.parent = MySQL_IMPORTv2.id
  )

Answer 2

您可以使用一个join来查找其id不在任何其他行中用作父id的行。

有关示例，请参阅此SQL Fiddle。

select
    p.*
from 
    MySQL_IMPORTv2 p                    -- Parent Row
    left outer join MySQL_IMPORTv2 c    -- Child Row
        on (p.id=c.parent)
where
    c.id is null

此查询将获取MySQL_IMPORTv2表中的每一行(使用表别名p)，并将其与表中的每一行(表别名c)进行比较。由于left outer join，查询将从p返回每一行，而不管c中是否有匹配。但是，如果MySQL无法从联接条件中在c中找到匹配的行，它将使用null值填充c，因此您可以通过在c中检查空值来过滤它。因此，您将在表中找到在另一列中没有匹配值的所有行。

如果您想要更多的信息，您可以查看谷歌有关查找孤立行的信息。

Answer 3

显示任何成功的结果

什么是成功的结果？带有id的记录，它也显示为父记录，还是父记录？后者可能会产生重复的条目，您希望每个匹配的父母只报告一个案例吗？是否要在其父记录旁边查看原始记录？

有多大比例的记录没有父母？父母的平均子女数是多少？叶节点的平均深度是多少？表上定义了哪些索引？虽然这些功能对功能并不重要，但对解决方案的性能有很大的影响。

id项内容父级 ..。 从MySQL_IMPORTv2中选择* category_id != parent；

什么category_id？

一个快速的解决办法是：

SELECT parent.id, parent.item, parent.content, GROUP_CONCAT(children.id)
FROM MySQL_IMPORTv2 AS children
INNER JOIN FROM MySQL_IMPORTv2 AS parents
ON children.parent_id=parent.id
GROUP BY parent.id, parent.item, parent.content

为了了解有父母的孩子的详细情况：

SELECT children.*
FROM MySQL_IMPORTv2 AS children
INNER JOIN FROM MySQL_IMPORTv2 AS parents
ON children.parent_id=parent.id

无论如何查看您的数据，这意味着所有的记录都是id=1记录的子记录，因此您可能需要.

SELECT parent.id, parent.item, parent.content, GROUP_CONCAT(children.id)
FROM MySQL_IMPORTv2 AS children
INNER JOIN FROM MySQL_IMPORTv2 AS parents
ON children.parent_id=parent.id
AND children.parent_id>1
GROUP BY parent.id, parent.item, parent.content