复杂的SQL查询 - 查找匹配多个不同外键的项目

所以想象一下你有一张Products (ID int, Name nvarchar(200))和另外两张桌子，ProductsCategories (ProductID int, CategoryID int)和InvoiceProducts (InvoiceID int, ProductID int)。我需要编写一个查询来生成一组与给定的发票ID和类别ID匹配的产品，这样产品列表就可以匹配所有指定的类别和所有指定的发票，而不会回退到动态SQL。想象一下，我需要找到1类和2类以及发票3和4中的产品列表。首先，我编写了一个存储过程，它接受类别ID和发票ID作为字符串，并将它们解析为表：

 CREATE PROCEDURE dbo.SearchProducts (@categories varchar(max), @invoices varchar(max))
 AS BEGIN
      with catids as (select cast([value] as int) from dbo.split(@categories, ' ')),
           invoiceids as (select cast([value] as int) from dbo.split(@invoices, ' '))
           select * from products --- insert awesomeness here
 END

我提出的不同解决方案看起来很糟糕，而且表现更差。我发现的最好的事情是生成一个由所有条件的左连接组成的视图，但这似乎非常昂贵，并没有解决匹配指定的所有不同键的问题。更新：这是我编写的一个示例查询，可以产生预期的结果。我错过了任何优化机会吗？像忍者的魔法独角兽矩阵操作一样？

with catids as (select distinct cast([value] as int) [value] from dbo.split(@categories, ' ')),
  invoiceids as (select distinct cast([value] as int) [value] from dbo.split(@invoices, ' '))

  select pc.ProductID from ProductsCategories pc (nolock)
    inner join catids c on c.value = pc.CategoryID 
    group by pc.ProductID 
    having COUNT(*) = (select COUNT(*) from catids)  
  intersect
  select ip.ProductID from InvoiceProducts ip (nolock)
    inner join invoiceids i on i.value = ip.InvoiceID 
    group by ip.ProductID 
    having COUNT(*) = (select COUNT(*) from invoiceids)

已邀请:

5 个回复

琶竞捆栓

如果(ProductID, CategoryID)和(ProductID, InvoiceID)都有唯一的索引：

SELECT  ProductID
FROM    (
        SELECT  ProductID
        FROM    ProductInvoice
        WHERE   InvoiceID IN (1, 2)
        UNION ALL
        SELECT  ProductID
        FROM    ProductCategory pc
        WHERE   CategoryID IN (3, 4)
        ) q
GROUP BY
        ProductID
HAVING  COUNT(*) = 4

或者，如果您的值以CSV字符串传递：

WITH    catids(value) AS
        (
        SELECT  DISTINCT CAST([value] AS INT)
        FROM    dbo.split(@categories, ' '))
        ), 
        (
        SELECT  DISTINCT CAST([value] AS INT)
        FROM    dbo.split(@invoices, ' '))
        )
SELECT  ProductID
FROM    (
        SELECT  ProductID
        FROM    ProductInvoice
        WHERE   InvoiceID IN
                (
                SELECT  value
                FROM    invoiceids
                )
        UNION ALL
        SELECT  ProductID
        FROM    ProductCategory pc
        WHERE   CategoryID IN
                (
                SELECT  value
                FROM    catids
                )
        ) q
GROUP BY
        ProductID
HAVING  COUNT(*) = 
        (
        SELECT  COUNT(*)
        FROM    catids
        ) + 
        (
        SELECT  COUNT(*)
        FROM    invoiceids
        )

请注意，在SQL Server 2008中，您可以将表值参数传递给存储过程。

雇砰

我将从这样的事情开始，利用参数中的表格ID值。临时表可以帮助子查询速度。

select p.*
from
(
    select pc.*
    from catids c
    inner join ProductsCategories pc
        on pc.CategoryID = c.value
) catMatch
inner join
(
    select pin.*
    from invoiceids i
    inner join ProductsInvoices pin
        on pin.InvoiceID = i.value
) invMatch
    on invMatch.ProductID = catMatch.ProductID
inner join Products p
    on p.ID = invMatch.ProductID

桑娠贯涤

ProductCategories应该有一个聚簇索引（CategoryId，ProductId），InvoiceProducts应该有一个on（InvoiceId，ProductId）最佳。这将允许通过仅使用聚簇索引中的数据来查找给定CategoryId和InvoiceId的产品ID。您可以使用函数返回给定字符串的整数表。 Google“CsvToInt”并点击SqlTeam的第一个链接查看代码。然后你可以：

SELECT *
FROM Products
WHERE ID IN (SELECT DISTINCT ProductId 
        FROM ProductCategories
        WHERE CategoryId in dbo.CsvToInt(@categories)
    ) AND ID IN (SELECT DISTINCT ProductId 
        FROM InvoiceProducts
        WHERE InvoiceId in dbo.CsvToInt(@invoices)
    )

搂腹时

递归CTE怎么样？首先将行号添加到条件表，然后添加一些伪SQL，如果您将：

;WITH cte AS(
Base case: Select productid, criteria from products left join criteria where row_number = 1 if it matches criteria from both row 1s or one is null.
UNION ALL
Recursive case: Select n+1 criteria row from products left join criteria where row_number = cte.row_number + 1 AND matches criteria from both row_number + 1 or one or the other (but not both) is null
)
SELECT *
WHERE criteria = maximum id from criteria table.

这将为您提供一种在多个条件上执行AND的方法，并且应该表现良好。这有什么意义吗？我最近用CTE做了一些非常酷的快速的东西，如果有必要可以详细说明。删除了cte代码，因为它错了，并没有真正值得修复那里有更好的解决方案。

磐去裸猜饲

将它们作为XML参数传递，将它们存储到临时表并加入。

要回复问题请先登录或注册

复杂的SQL查询 - 查找匹配多个不同外键的项目

5 个回复

发起人

relational_division

问题状态

复杂的SQL查询 - 查找匹配多个不同外键的项目

与内容相关的链接

5 个回复

发起人

relational_division

问题状态