主页 / dba / 问题 / 125892
Accepted
LiamB
Asked: 2016-01-12 06:30:34 +0800 CST

嵌套 SQL 查询（查询数据子集）

  • 772

假设我有以下结构......

Structures
- ID
- Name

Fields
- ID
- StructureID
- Key

这些表包含简单的“结构”（认为非常简单的表）。

然后有一个额外的表

DataValues
- ID
- FieldID 
- Value
- RowID

表内的示例数据...

ID | FieldID | Value             | RowID
1  | 5       | Blog Post Title   | 1
2  | 6       | Blog Post Body    | 1
3  | 5       | Another Post H1   | 2
4  | 6       | Another Body      | 2

现在我希望能够对每个数据集进行动态查询，因此获取所有遵循特定模式的 RowID。

SELECT DV.RowID FROM DataValues as DV 
    INNER JOIN Fields as F ON DV.FieldID = F.ID 
    INNER JOIN DataRows as DR ON DV.RowID = DR.ID 
WHERE DR.StructureID = 2 
AND (F.[Key] = 'Title' AND DV.[Value] = 'Blog Post Title')

问题是当我们想要做多个条件时

SELECT DV.RowID FROM DataValues as DV 
    INNER JOIN Fields as F ON DV.FieldID = F.ID 
    INNER JOIN DataRows as DR ON DV.RowID = DR.ID 
WHERE DR.StructureID = 2 
AND (F.[Key] = 'Title' AND DV.[Value] = 'Blog Post Title') 
AND (F.[Key] = 'Body' AND DV.[Value] <> 'A Body We Dont Want')

这显然行不通，有没有办法查询数据的子集？

sql-server

1 个回答

  1. Best Answer

    如果我理解你的意思正确（我不确定我是否理解），你应该为每个 RowID 返回 2 行以获得“匹配”结果。

    因此，您可以OR对正在搜索的关键数据进行计算，GROUP BY RowID并计算每个子集都有匹配的结果。

    SELECT 
  DV.RowID,
  MIN(CASE 
        WHEN F.[Key] = 'Title' THEN 1 
        WHEN F.[Key] = 'Body' THEN 2
      END
    ) AS Ordinal 
FROM DataValues as DV,
INNER JOIN Fields as F ON DV.FieldID = F.ID 
INNER JOIN DataRows as DR ON DV.RowID = DR.ID 
WHERE DR.StructureID = 2 
AND (
     (F.[Key] = 'Title' AND DV.[Value] = 'Blog Post Title') 
OR   (F.[Key] = 'Body' AND DV.[Value] <> 'A Body We Dont Want') 
    )
GROUP BY DV.RowID
HAVING COUNT(*) = 2
ORDER BY Ordinal ASC;

    或者，您可以为满足这两个条件INTERSECT的所有值执行两个结果集中的一个：RowID

    SELECT DV.RowID, 1 as Ordinal 
FROM DataValues as DV 
INNER JOIN Fields as F ON DV.FieldID = F.ID 
INNER JOIN DataRows as DR ON DV.RowID = DR.ID 
WHERE DR.StructureID = 2 
  AND (F.[Key] = 'Title' AND DV.[Value] = 'Blog Post Title') 
INTERSECT
SELECT DV.RowID, 2 AS Ordinal
FROM DataValues as DV 
INNER JOIN Fields as F ON DV.FieldID = F.ID 
INNER JOIN DataRows as DR ON DV.RowID = DR.ID 
WHERE DR.StructureID = 2 
  AND (F.[Key] = 'Body' AND DV.[Value] <> 'A Body We Dont Want')
ORDER BY Ordinal ASC;
    • 4

相关问题