主页 / dba / 问题 / 155153
Accepted
Louis Somers
Asked: 2016-11-14 15:56:23 +0800 CST

声明性地从所有（动态数量的）相关实体中选择一个需要关系的实体

  • 772

我正在尝试构建一个 where 子句，该子句需要 X 数量的子项与具有 N 对 N 关系的特定父项建立关系。类似于“获取这些产品一起销售的所有收据”。一个示例场景更容易解释：

DECLARE @Nodes TABLE(
  Id INT
);

DECLARE @Arc TABLE(
  Id INT IDENTITY(1,1),
  Source INT, -- FK @Nodes.Id
  Dest INT    -- FK @Nodes.Id
);

INSERT INTO @Nodes (Id) VALUES (1),(2),(3),(4);

INSERT INTO @Arc (Source, Dest) VALUES
   (1, 2)
  ,(1, 3)
  ,(1, 3)
  ,(2, 1)
  ,(2, 3)
  ,(2, 4);

在 SP 中，输入被预处理成这样的表：

DECLARE @InputConnectedNodes TABLE ( Id INT );
INSERT INTO @InputConnectedNodes (Id) VALUES (3),(4); -- not a fixed number of nodes

在此示例中，我们希望所有节点都与节点 3 和 4 有弧（上例中只有节点 2）。目前我们有这个：

SELECT DISTINCT Source 
    FROM @Arc
    WHERE Dest IN (SELECT Id FROM @InputConnectedNodes);

然后我们有一些丑陋的程序代码循环检查所有关系的输入和（嵌套）输出。我想用一些声明来代替它。到目前为止我想到的最好的是：

SELECT Source 
    FROM @Arc 
    WHERE Dest IN (SELECT Id FROM @InputConnectedNodes) 
    GROUP BY Source
    HAVING COUNT(Source) = (SELECT COUNT(*) FROM @InputConnectedNodes)

但在我的示例中，当节点具有多个（相同的）弧（如 (1->3)）时，它将失败。

我也可以通过动态生成 where 子句来解决这个问题，但我更喜欢完全声明式的解决方案。

sql-server performance

1 个回答

  1. Best Answer

    我建议放松您对完全声明性解决方案的决心。可能没有一种声明式的方法来解决您的问题，并且在许多情况下，动态 SQL 在性能方面是更好的解决方案——即使它缺乏可读性。

    动态 SQL 使优化器有机会使用针对不同参数优化的不同计划，而不必提出能够很好地解决所有排列的单一计划。后者是相当难以实现的。

    您的问题称为。查看该标签中的问题并：

    大多数 Stack Overflow 解决方案都可以按原样使用，或者对 SQL Server 进行少量修改。请注意，具有多个连接或EXISTS子查询（需要为任意情况动态生成的代码）的更复杂的查询比GROUP BY查询更有效。

    • 1

相关问题