关系划分 - 模仿"只在"

Question

我正在尝试在Oracle SQL中编写一个查询，该查询采用两个参数，并在整个表中查找仅出现或出现的所有实例，无论搜索中有多少个实例。以下是我正在寻找的示例：

| ID  | FileType | COUNT(FileType) |
|-----|----------|-----------------|
| 1   | txt      | 1               |
| 1   | png      | 3               |
| 1   | jpg      | 2               |
====================================
| 2   | txt      | 0               |
| 2   | png      | 6               |
| 2   | jpg      | 0               |
====================================
| 3   | txt      | 0               |
| 3   | png      | 0               |
| 3   | jpg      | 5               |
====================================
| 4   | txt      | 0               |
| 4   | png      | 3               |
| 4   | jpg      | 1               |
====================================
| 5   | txt      | 5               |
| 5   | png      | 0               |
| 5   | jpg      | 3               |

我的目标是只用 png 或 jpg 获取所有 ID 并省略其余的，所以我只希望返回 ID 的 2、3 和 4。

我

试图在这里查找解决方案，但没有找到任何似乎与我的情况相匹配的东西。（最接近的是这个问题：SQL选择只有特定值的行）

我确实设法发现这个问题可以通过关系划分来实现，但到目前为止我没有任何经验。到目前为止，我的查询如下所示：

SELECT DISTINCT ID, FileType, COUNT(FileType)
FROM Table ta
WHERE (ta.FileType = 'jpg' or ta.FileType = 'png') and
NOT EXISTS
    (SELECT *
     FROM Table tb
     WHERE ta.FileType = tb.FileType and
     (tb.FileType != 'jpg' or tb.FileType != 'png'))
GROUP BY ID, FileType;

当我尝试这个时，我没有得到任何结果。有人知道我在这里哪里出错了？

Answer 1

您可以使用集合运算符：

SELECT ID FROM tab WHERE FileType IN ('jpg', 'png')
MINUS
SELECT ID FROM tab WHERE FileType NOT IN ('jpg', 'png')

假设：文件类型不可为空。

---

处理空值：

SELECT ID FROM tab WHERE FileType IN ('jpg', 'png')
MINUS
SELECT ID 
FROM (SELECT * FROM tab WHERE FileType IS NOT NULL) 
WHERE FileType NOT IN ('jpg', 'png')

Answer 2

你很接近。只需查看您有多少种不同的文件类型。以下是您的身份证件：

SELECT ID
FROM Table ta
GROUP BY ID
HAVING count(distinct FileType) = 1 and max(ta.FileType) in ('jpg','png');

更新：对于案例 4，上部将失败。

这会做到，但它很丑陋：

SELECT ID
FROM Table ta
GROUP BY ID
HAVING count(distinct FileType) <= 2 
   and max(ta.FileType) in ('jpg','png') 
   and min(ta.FileType) in ('jpg','png');

它很丑陋，因为您不能将其扩展为 3 个值。

Answer 3

只需使用"帮助程序"表：

WITH TypeCounts AS
(
  SELECT ID, FileType, COUNT(*) AS CNT
  FROM Table
  GROUP BY ID, FileType
)
SELECT *
FROM Table
LEFT JOIN TypeCounts txt ON txt.ID = Table.ID AND txt.FileType = 'txt'
LEFT JOIN TypeCounts jpg ON jpg.ID = Table.ID AND jpg.FileType = 'jpg'
LEFT JOIN TypeCounts png ON png.ID = Table.ID AND png.FileType = 'png'
WHERE COALESCE(txt.CNT,0) = 0 AND
    ( COALESCE(jpg.CNT,0) > 0 OR COALESCE(png.CNT,0) > 0)

此解决方案的优点在于，它非常清楚业务规则是什么，因此更易于维护。