每个“产品”最多可以有10000个“细分”行。这些细分受众群具有一个针对每种产品(1、2、3、4、5,…)从1开始的排序列,以及一个值列,该列可以包含诸如(323.113、5423.231、873.42、422.64、763.1,…)。
我想确定给定细分子集的产品的潜在匹配。例如,如果我按正确的顺序有5个细分值,那么如何在细分表中某处有效地找到所有具有相同顺序的5个细分的所有产品?
假设这样的表:
CREATE TABLE Products ( ProductId int not null constraint PK_Products primary key ,Name varchar(100) not null ) CREATE TABLE Segments ( ProductId int not null constraint FK_Segments__Products foreign key references Products (ProductId) ,OrderBy int not null ,Value float not null ,constraint PK_Segments primary key (ProductId, OrderBy) )
接下来,在临时表中设置搜索数据:
CREATE TABLE #MatchThis ( Position int not null ,Value float not null )
对于N个搜索对象,必须像这样填充
First item 0 <value 1> Second item 1 <value 2> Third item 2 <value 3> ... Nth item N-1 <value N>
现在设置一些重要的值。(这可以塞入最终查询中,但是这种方式使它更易于阅读,并且可能会稍微提高性能。)
DECLARE @ItemCount int ,@FirstValue float -- How many items to be matched ("N", above) SELECT @ItemCount = count(*) from #MatchThis -- The value of the first item in the search set SELECT @FirstValue = Value from #MatchThis where Position = 0
然后,这只是一个查询:
SELECT pr.Name ,fv.OrderBy -- Required by the Group By, but otherwise can be ignored from #MatchThis mt cross join (-- All Segments that match the first value in the set select ProductId, OrderBy from Segment where Value = @FirstValue) fv inner join Product pr -- Just to get the Product name on pr.ProductId = fv.ProductId inner join Segment se on se.ProductId = fv.ProductId and se.OrderBy = fv.OrderBy + mt.Position -- Lines them up based on the first value and se.Value = mt.Value -- No join if the values don't match group by pr.Name ,fv.OrderBy having count(*) = @ItemCount -- Only include if as many segments pulled for this product/segment.OrderBy as are required
我坚信这是可行的,但是我现在没有时间详细测试它。为了优化性能,除了指示的主键之外,您还可以在Segment.Value上添加常规索引