Sql Haystack针相交在元素最多的Haystack中查找集合

我需要找出某项功能的有效价格

• 一个功能可以有一个或多个价格规则(规则数&lt；10；通常为&lt；4)
• 该功能的价格可能取决于订购的其他功能
• 根据订购的其他功能(低于F2、F3)，价格可以降低或提高.
• 有些功能对F1(低于F6、F8、F9)的价格没有影响.

Sqlfiddle demo with sample records个个

从大海捞针(顺序)来看，每个特征(这里是F1)必须与一个价格规则相匹配.有效价格是功能交叉点最多的价格.

这张图显示了价格与不同订单之间的映射.与特性f1相关的价格基于价格规则表中的特性与订单中的特性之间的交集.

示例

• F1美元的价格取决于(F1, F2, F3)的功能
• 特征F1的价格可以通过 Select 在其与草堆(订单)的交集中具有最多元素的F1的价格规则来确定.

本例针对功能F1有三个价格规则和三个不同的订单.

• 订单1只有F1个与F1的定价规则相匹配；正确的价格是6
• 订单2在大海捞针中有两个匹配的功能(F1+F2)，与F1的价格规则匹配；正确的价格是4
• 订单3有三个匹配(F1+F2+F3)的F1；正确的价格是2

此查询是我try 连接记录，但存在许多重复项和错误的连接记录

SELECT op.OrderId, op.Id as OrderPositionId, 
       op.FeatureId, f.Featurename, pr.Price 
FROM OrderPositions op 
INNER JOIN FeatureCombinations fc ON op.FeatureId = fc.FeatureId
INNER JOIN Features f on op.FeatureId = f.Id
INNER JOIN PriceRules pr ON fc.PriceRuleId = pr.Id
ORDER BY op.OrderId, op.Id, f.Id;

Sqlfiddle demo with sample records个个

从当前结果来看，带有(X)的所有记录都是错误的

问题

如何确定某个功能的哪个价格规则最匹配(elements from order ∩ elements from pricerule)？

SQL Statemens用于创建包含记录的表

创建表

CREATE TABLE Features(
  Id    INTEGER PRIMARY KEY, 
  Featurename  TEXT
);
CREATE TABLE PriceRules(
  Id     INTEGER PRIMARY KEY, 
  Price  REAL   
);
CREATE TABLE FeaturesPriceRules(
  FeatureId   INTEGER, 
  PriceRuleId INTEGER,
  FOREIGN KEY(FeatureId) REFERENCES Features(Id),
  FOREIGN KEY(PriceRuleId) REFERENCES PriceRules(Id)
);

CREATE TABLE FeatureCombinations(
  PriceRuleId INTEGER,
  FeatureId   INTEGER,   
  FOREIGN KEY(PriceRuleId) REFERENCES PriceRules(Id),
  FOREIGN KEY(FeatureId) REFERENCES Features(Id)  
);

CREATE TABLE OrderPositions(
  Id INTEGER  PRIMARY KEY, 
  OrderId   INTEGER,   
  FeatureId   INTEGER,     
  FOREIGN KEY(FeatureId) REFERENCES Features(Id)  
);

创建记录

INSERT INTO Features (Id, Featurename)
VALUES (1, 'F1'), (2, 'F2'), (3, 'F3'),
       (4, 'F6'), (5, 'F7'), (6, 'F9'),
       (7, 'E1'), (8, 'ZA2');

INSERT INTO PriceRules (Id, Price)
VALUES (101, '6.00'), (102, '4.00'), (103, '2.00'),
       (105, '24.00'), (106, '22.00');  
    
INSERT INTO FeaturesPriceRules (FeatureId, PriceRuleId)
VALUES (1, 101), (1, 102), (1, 103),(7, 105),(7, 106);  
               
INSERT INTO FeatureCombinations (PriceRuleId, FeatureId)
VALUES (101, 1), (102, 1), (102, 2),
       (103, 1), (103, 2),(103, 3), 
       (105, 7), (106, 7), (106, 8); 
       
INSERT INTO OrderPositions(Id, OrderId, FeatureId)
VALUES (211, 401, 1), (221, 402, 1),(222, 402, 2),
(231, 403, 1),(232, 403, 2),(233, 403, 3);         
   

另请参阅此Sqlfiddle demo with sample records个个