因此,假设您有一个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)
解决方法
如果您在(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中,您可以将表值参数传递到存储过程中.
