获取percentages与prices表合理关联结果的SQL查询需求

DevOps 小满

2026-6-16

解决百分比与价格表的逻辑关联问题

需求说明

现有两张数据表：

percentages：根据供应商、销售员、客户和数量阈值确定适用的百分比，qty为最小适用数量（即数量≥该值时使用对应百分比）
prices：根据客户、商品和数量阈值确定价格，qty同样为最小适用数量

需要关联两张表，仅保留逻辑有效的组合：比如当prices.qty=50（表示数量≥50时用该价格）时，percentages中只能匹配该客户下qty≤50的最大阈值对应的百分比（例如客户David的percentages里有qty=20，就不能再用qty=0的百分比）。

表结构与示例数据

percentages表

supplier	salesperson	customer	qty	percent
John	Anne	David	0	0.25
John	Anne	David	20	0.50
John	Anne	Mary	0	0.25
Paul	Andrew	David	0	0.25

prices表

Customer	article	qty	price
David	X	0	50
David	X	50	40
David	Y	0	50
Mary	X	0	50
Mary	Y	0	55

解决方案SQL

WITH price_with_valid_pct_threshold AS (
    -- 为每个价格条目，计算对应客户下允许的最大百分比阈值（≤当前价格阈值）
    SELECT 
        p.*,
        (SELECT MAX(pct.qty) 
         FROM percentages pct 
         WHERE pct.customer = p.Customer 
           AND pct.qty <= p.qty) AS max_pct_qty
    FROM prices p
)
SELECT 
    pct.supplier,
    pct.salesperson,
    pw.Customer,
    pw.article,
    pw.qty AS price_min_qty,
    pw.price,
    pct.qty AS pct_min_qty,
    pct.percent
FROM price_with_valid_pct_threshold pw
JOIN percentages pct 
    ON pw.Customer = pct.customer 
    AND pct.qty = pw.max_pct_qty
ORDER BY pw.Customer, pw.article, pw.price_min_qty, pct.supplier;

逻辑说明

子查询price_with_valid_pct_threshold：针对每个价格条目，找到同客户下所有百分比规则中，阈值≤当前价格阈值的最大值——这就是该价格条目能匹配的唯一有效百分比阈值。
最后将价格表与百分比表通过客户和计算出的有效阈值关联，得到所有逻辑合理的组合。

预期结果

supplier	salesperson	Customer	article	price_min_qty	price	pct_min_qty	percent
John	Anne	David	X	0	50	0	0.25
Paul	Andrew	David	X	0	50	0	0.25
John	Anne	David	X	50	40	20	0.50
Paul	Andrew	David	X	50	40	0	0.25
John	Anne	David	Y	0	50	0	0.25
Paul	Andrew	David	Y	0	50	0	0.25
John	Anne	Mary	X	0	50	0	0.25
John	Anne	Mary	Y	0	55	0	0.25