如何清理此SQL数据?

对于第一个查询,需要同时满足以下两个条件的行:

1. 这个 Name 在同一行中的表格中 LeftId 和 RightId 都是空的。
2. 这个 名字 在同一行的表中,至少有一个 左撇子 和 右派 是 不 无效的。

嗯,1由以下人员完成:

SELECT Name FROM Tbl WHERE (LeftId IS NULL) AND (RightId IS NULL)

并且2通过以下方式完成:

SELECT Name FROM Tbl WHERE (LeftId IS NOT NULL) OR (RightId IS NOT NULL)

你可以 横断 他们去看看 名字 S出现在两个列表中:

SELECT Name FROM Tbl WHERE (LeftId IS NULL) AND (RightId IS NULL)
INTERSECT
SELECT Name FROM Tbl WHERE (LeftId IS NOT NULL) OR (RightId IS NOT NULL)

回报:

Name
----
Cat
Cow

但是你想要 左撇子 和 右派 ,你不在乎是哪一个,所以我想我们会把名字加起来:

SELECT Name, MIN(LeftId) AS LeftId, MIN(RightId) AS RightId 
    FROM Tbl WHERE Tbl.Name IN (
      SELECT Name FROM Tbl WHERE (LeftId IS NULL) AND (RightId IS NULL)
      INTERSECT
      SELECT Name FROM Tbl WHERE (LeftId IS NOT NULL) OR (RightId IS NOT NULL)
    )
GROUP BY Name

哪些回报

Name  LeftId  RightId
----  ------  -------
Cat   1
Cow   6       7

lc 已经建议使用coalese将这两个ID转换为一个ID。那么这个怎么样:

SELECT Name, COALESCE(MIN(LeftId),MIN(RightId)) AS Id 
    FROM Tbl WHERE Tbl.Name IN (
      SELECT Name FROM Tbl WHERE (LeftId IS NULL) AND (RightId IS NULL)
      INTERSECT
      SELECT Name FROM Tbl WHERE (LeftId IS NOT NULL) OR (RightId IS NOT NULL)
    )
GROUP BY Name

回报:

Name  Id
----  --
Cat   1
Cow   6

对于第二个查询,需要符合以下条件的行:

1. 这个 名字 仅显示在没有 左撇子 和 右派

我想不出在一组条件中在SQL中执行这种自引用查询的方法,所以我将把它分为两个条件。 两个 必须遵守才能被接受:

1. 这个 名字 显示在没有 左撇子 和 右派
2. 这个 名字 做 不 显示在具有以下任一项的行中 左撇子 或 右派

做1只是:

从tbl中选择名称,其中(leftid为空)和(rightid为空)

但是2很棘手。当然,做与2相反的事情(“所有 名字 出现在具有 左撇子 或 右派 )就像以前一样:

从tbl中选择名称,其中(leftid不为空)或(rightid不为空)

现在出现了一个棘手的问题——我们需要所有服从1的行,但不要服从2的相反行。这里就是 除了 是有用的:

SELECT Name FROM Tbl WHERE (LeftId IS NULL) AND (RightId IS NULL)
EXCEPT
SELECT Name FROM Tbl WHERE (LeftId IS NOT NULL) OR (RightId IS NOT NULL)

回报:

Name
----
Bird

这就是我们想要的!

查询1)

SELECT * 
FROM Table 
WHERE (LeftID IS NULL AND RightID IS NOT NULL) 
    OR (LeftID IS NOT NULL AND RightID IS NULL)

查询2)

SELECT * 
FROM Table 
WHERE LeftID IS NULL AND RightID IS NULL

查询1:

SELECT [Name], [LeftID], [RightID]

FROM [TestTable]

WHERE -- "All rows which have at least 1 Id in one of the two columns"
      ([LeftID] IS NOT NULL OR [RightID] IS NOT NULL)
      OR
      -- "Rows with NO data in both of those two Id columns"
      ([LeftID] IS NULL AND [RightID] IS NULL)

查询2:

SELECT [Name], [LeftID], [RightID]

FROM [TestTable]

WHERE -- "All the rows where LeftId and RightId are NULL
      -- grouped by the same name"
      ([LeftID] IS NULL AND [RightID] IS NULL)
      AND
      -- "If another row (with the same name) has a value
      -- in the LeftId or RightId, this name will not be returned"    
      ([Name] NOT IN (SELECT DISTINCT [Name] FROM [TestTable]
                      WHERE [LeftID] IS NOT NULL
                            OR
                            [RightID] IS NOT NULL))

GROUP BY [Name], [LeftID], [RightID]

结果:

Name                                               LeftID      RightID
-------------------------------------------------- ----------- -----------
Cat                                                1           NULL
Cat                                                1           NULL
Dog                                                2           NULL
Dog                                                2           NULL
Dog                                                NULL        3
Dog                                                NULL        3
Gerbil                                             4           5
Cat                                                NULL        NULL
Bird                                               NULL        NULL
Cow                                                6           NULL
Cow                                                NULL        NULL
Cow                                                NULL        7
Dog                                                8           9

(13 row(s) affected)

Name                                               LeftID      RightID
-------------------------------------------------- ----------- -----------
Bird                                               NULL        NULL

(1 row(s) affected)

如果我正确理解你,那么这是相当微不足道的:

1:

SELECT * 
FROM your_table 
WHERE (LeftId IS NOT NULL 
AND RightId IS NULL)
OR
(LeftId IS NULL 
AND RightId IS NOT NULL)

2:

SELECT * 
FROM your_table
WHERE 
    NOT EXISTS 
              (SELECT * FROM your_table y1
               WHERE (y1.LeftId IS NOT NULL OR y1.RightId IS NOT NULL)
               AND y1.name = your_table.name)

如果这不正确,也许你可以澄清。

编辑:更新

希望我在这里能正确理解你。

查询1:

SELECT t1.Name, COALESCE(MIN(t1.LeftID), MIN(t1.RightID))
FROM Table t1
WHERE EXISTS(SELECT t2.Name
             FROM Table t2
             WHERE t2.Name = t1.Name 
             AND   t2.LeftID IS NULL AND t2.RightID IS NULL)
AND   COALESCE(MIN(t1.LeftID), MIN(t1.RightID)) IS NOT NULL
GROUP BY t1.Name

查询2:

SELECT t1.Name
FROM Table t1
WHERE NOT EXISTS(SELECT t2.Name
                 FROM Table t2
                 WHERE t2.Name = t1.Name
                 AND   (t2.LeftID IS NOT NULL OR t2.RightID IS NOT NULL))