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)
.
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.
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)
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…