复杂的SQL查询--查找匹配多个不同外键的项目 [英] Complicated SQL Query--finding items matching multiple different foreign keys

问题描述

假设您有一个 Products (ID int, Name nvarchar(200)) 表和另外两个表，ProductsCategories (ProductID int, CategoryID int)和 InvoiceProducts (InvoiceID int, ProductID int).

So imagine that you have a table of Products (ID int, Name nvarchar(200)), and two other tables, ProductsCategories (ProductID int, CategoryID int) and InvoiceProducts (InvoiceID int, ProductID int).

我需要编写一个查询来生成一组产品，这些产品与一组给定的发票 ID 和类别 ID 相匹配，以便产品列表与所有指定的类别和所有指定的发票相匹配，而不会退回到动态 SQL.假设我需要查找属于类别 1 和类别 2 以及发票 3 和 4 的产品列表.

I need to write a query to produce a set of products that match a given set of invoice ids and category ids such that the list of products match all the specified categories and all the specified invoices, without falling back to dynamic SQL. Imagine I need to find a list of products that are in both categories 1 and 2 and in invoices 3 and 4.

首先，我编写了一个存储过程，它接受类别 ID 和发票 ID 作为字符串，并将它们解析为表格:

As a start, I've written a stored-procedure that accept the category ids and invoice ids as strings, and parse them into tables:

 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

我提出的不同解决方案看起来很糟糕，而且表现更差.我发现的最好的事情是生成一个由所有条件的左连接组成的视图，但这似乎非常昂贵，并且不能解决匹配所有指定的不同键的问题.

The different solutions I've come up with look awful, and perform worse. The best thing I've found is to generate a view comprised of left joins of all the criteria, but that seems very expensive and doesn't solve the issue of matching all of the different keys specified.

更新:这是我编写的一个示例查询，它产生了预期的结果.我是否错过了任何优化机会?像忍者神奇的独角兽矩阵运算?

Update: This is an example query I wrote that yields the expected results. Am I missing any optimization opportunities? Like magical unicorn matrix operations by ninjas?

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)   

推荐答案

前提是您在 (ProductID, CategoryID) 和 (ProductID, InvoiceID) 上都有唯一索引:

Provided that you have unique indices on both (ProductID, CategoryID) and (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 字符串形式传递:

or, if your values are passed in CSV strings:

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 中，您可以将表值参数传递给存储过程.

Note that in SQL Server 2008 you can pass table-valued parameters into the stored procedures.

这篇关于复杂的SQL查询--查找匹配多个不同外键的项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！