mysql - 如何获取表格中满足不同条件的产品和价格列表

Question

我有一个定价表如下，

定价表

id  productId  ContractId   ageGroup  ageFrom    ageTo  sellingPrice  specialPrice
1       1          1           1          0        2         0            0
2       1          1           1          3       13        20            0
3       1          1           2         18       55        80            0
4       1          1           3         56      119        60            0
5       1          1           1          0        2         0            0
6       1          2           2         18       55        85            0
7       2          2           3         55      119        90            0
8       2          2           2         18       55        90            0

我需要查找给定年龄组(1 名成人或 2 名 child 或 3 名老年人)的契约(Contract) ID 和 ID 列表。对于 child ，还需要考虑年龄范围(从 - 到)。

以下查询(1 名成人、2 名 2 岁和 4 岁的 child 以及 1 名老年人)似乎有效，但仅返回与年龄组 1 匹配的 ID。

SELECT contractId,id 
FROM tbl_contract_price cp1 
WHERE contractId IN 
(SELECT contractId FROM tbl_contract_price cp2 
WHERE contractId IN 
(SELECT contractId FROM tbl_contract_price cp3 
WHERE cp1.ageGroup = 1 AND (cp2.ageGroup = 2 AND cp2.ageFrom <= 2 AND 2 <= cp2.ageTo OR cp2.ageGroup = 2 AND cp2.ageFrom <= 4 AND 4 <= cp2.ageTo ) AND cp3.ageGroup = 3))

我有什么遗漏的吗？

Answer 1

基于一些假设，我创建了以下内容来帮助您入门。请注意，您需要强制执行数据完整性(即，确保每个产品的价格涵盖所有可能的年龄等)

我建议您使用临时报价表，这样您在输入数量上可以有更大的灵 active 。您可以看到下面的数据示例。或者，更好的是，在业务逻辑层中处理该逻辑。

如果两个合约产生相同的价格等，您将需要应用任何决胜局逻辑。

CREATE TABLE Pricing (
  ID int not null,
  productId int not null,
  ContractId int not null,
  ageGroup int not null,
  ageFrom int not null,
  ageTo int not null,
  sellingPrice int not null,
  PRIMARY KEY (ID)
);

INSERT INTO Pricing (ID, productId, ContractId, ageGroup, ageFrom, ageTo, sellingPrice) Values (1, 1, 1, 1, 0, 2, 0);
INSERT INTO Pricing (id, productId, ContractId, ageGroup, ageFrom, ageTo, sellingPrice) Values (2, 1, 1, 1, 3, 13, 20);
INSERT INTO Pricing (id, productId, ContractId, ageGroup, ageFrom, ageTo, sellingPrice) Values (3, 1, 1, 2, 18, 55, 80);
INSERT INTO Pricing (id, productId, ContractId, ageGroup, ageFrom, ageTo, sellingPrice) Values (4, 1, 1, 3, 56, 119, 60);
INSERT INTO Pricing (id, productId, ContractId, ageGroup, ageFrom, ageTo, sellingPrice) Values (5, 1, 2, 1, 3, 13, 0);
INSERT INTO Pricing (id, productId, ContractId, ageGroup, ageFrom, ageTo, sellingPrice) Values (6, 1, 2, 2, 18, 55, 85);
INSERT INTO Pricing (id, productId, ContractId, ageGroup, ageFrom, ageTo, sellingPrice) Values (7, 2, 2, 3, 55, 119, 90);
INSERT INTO Pricing (id, productId, ContractId, ageGroup, ageFrom, ageTo, sellingPrice) Values (8, 2, 2, 2, 18, 55, 90);

CREATE TABLE ValidDates (
  ID int not null,
  priceId int not null,
  fromDate date not null,
  toDate date not null,
  PRIMARY KEY (ID)
 );

 INSERT INTO ValidDates (id, priceId, fromDate, toDate) VALUES  (1, 1, '2018-06-01', '2018-06-30');
 INSERT INTO ValidDates (id, priceId, fromDate, toDate) VALUES  (2, 2, '2018-06-01', '2018-06-30');
 INSERT INTO ValidDates (id, priceId, fromDate, toDate) VALUES  (3, 2, '2018-07-01', '2018-07-31');
 INSERT INTO ValidDates (id, priceId, fromDate, toDate) VALUES  (4, 3, '2018-06-01', '2018-06-30');
 INSERT INTO ValidDates (id, priceId, fromDate, toDate) VALUES  (5, 3, '2018-07-01', '2018-07-31');
 INSERT INTO ValidDates (id, priceId, fromDate, toDate) VALUES  (6, 4, '2018-06-01', '2018-06-30');
 INSERT INTO ValidDates (id, priceId, fromDate, toDate) VALUES  (7, 5, '2018-06-01', '2018-06-30');
 INSERT INTO ValidDates (id, priceId, fromDate, toDate) VALUES  (8, 5, '2018-07-01', '2018-07-31');
 INSERT INTO ValidDates (id, priceId, fromDate, toDate) VALUES  (9, 6, '2018-06-01', '2018-06-30');
 INSERT INTO ValidDates (id, priceId, fromDate, toDate) VALUES  (10, 6, '2018-07-01', '2018-07-31');

 CREATE TABLE Products (
   ID int not null,
   PRIMARY KEY (ID)
 );

CREATE TABLE Quotes (
  ID int not null,
  age int
);

INSERT INTO Quotes (Id, age) VALUES (1, 70);
INSERT INTO Quotes (Id, age) VALUES (1, 25);
INSERT INTO Quotes (Id, age) VALUES (1, 1);
INSERT INTO Quotes (Id, age) VALUES (1, 4); 

然后，您可以使用以下查询根据产品 ID、所选日期和报价 ID(其中包含特定报价的所有年龄)计算总价

<小时/>

场景:巡演日期=2018年6月22日；产品 = 1，报价 = 1，年龄 = 1, 4, 25, 70

SELECT @tourdate := '2018-06-22', @productid := 1, @quoteid := 1;

第一个查询显示如何检索相关信息

SELECT productid, contractId, ageGroup, ageFrom, ageTo,
SUM(CASE WHEN age BETWEEN ageFrom AND ageTo THEN 1 ELSE 0 END) AS PAXCount, sellingPrice
FROM ValidDates
LEFT JOIN Pricing
  ON priceId = Pricing.ID
    LEFT JOIN Products
      ON productId = Products.ID
        LEFT JOIN Quotes
          ON Quotes.ID = @quoteid
WHERE (@tourdate BETWEEN fromDate AND toDate) AND productid = @productid
GROUP BY productid, contractid, ageGroup, ageFrom, ageTo, sellingPrice;

<小时/>

第二个查询基于第一个查询构建，汇总总数，以便您获得排名的总成本

SELECT contractId, SUM(sellingPrice * PAXCount) FROM (
SELECT productid, contractId, ageGroup,
SUM(CASE WHEN age BETWEEN ageFrom AND ageTo THEN 1 ELSE 0 END) AS PAXCount, sellingPrice
FROM ValidDates
LEFT JOIN Pricing
  ON priceId = Pricing.ID
    LEFT JOIN Products
      ON productId = Products.ID
        LEFT JOIN Quotes
          ON Quotes.ID = @quoteid
WHERE (@tourdate BETWEEN fromDate AND toDate) AND productid = @productid
GROUP BY productid, contractid, ageGroup, sellingPrice) P
GROUP BY contractid
ORDER BY SUM(sellingPrice * PAXCount)
#LIMIT 1;

1. 您可以取消注释 #Limit 1 以仅获取最便宜的套餐，但您需要了解该限制
2. 您需要确保数据完整性，即对于每个产品和日期范围，所有可能的年龄都需要涵盖

3. 请注意，由于 0 岁 child 和 70 岁老人不在契约(Contract) ID 2 的覆盖范围内，因此 85 美元的总额具有误导性。您可以添加逻辑来检查契约(Contract)是否可以满足所有年龄(如果输入计数为 4，则检查契约(Contract)是否确实包含四个人等)

4. 您可能需要根据需要清理报价表。这肯定不是最有效的方法(但它应该根据您的要求工作)。

例如，将查询更改为如下所示:

SELECT @PAXCount := COUNT(*) FROM Quotes WHERE id = @quoteid;

或者您可以相当轻松地从您的应用程序中传递它。然后，检查以确保计数匹配。

SELECT contractId, SUM(sellingPrice * PAXCount) AS TotalPrice, SUM(PAXCount) AS TotalPAXCOUNT
FROM (
  SELECT productid, contractId, ageGroup,
  SUM(CASE WHEN age BETWEEN ageFrom AND ageTo THEN 1 ELSE 0 END) AS PAXCount, sellingPrice
  FROM ValidDates
  LEFT JOIN Pricing
    ON priceId = Pricing.ID
      LEFT JOIN Products
        ON productId = Products.ID
          LEFT JOIN Quotes
            ON Quotes.ID = @quoteid
  WHERE (@tourdate BETWEEN fromDate AND toDate) AND productid = @productid 
  GROUP BY productid, contractid, ageGroup, sellingPrice) P
GROUP BY contractid
HAVING @PAXCount = SUM(PAXCount)
ORDER BY SUM(sellingPrice * PAXCount)
#LIMIT 1;

这样，只会显示涵盖所有乘客的契约(Contract) ID。

<小时/>

Try it in the DB Fiddler