代码之家 › 专栏 › 技术社区 › sauntimo

查找与具有where子句的另一列的最小值关联的值

correlated-subquery greatest-n-per-group sql mysql

sauntimo · 技术社区 · 7 年前

假设我有一张价格表;

+------------+----------+---+-----+----+-------+-----+
| product_id | price_id | a |  b  | c  | price | fee |
+------------+----------+---+-----+----+-------+-----+
|          1 |        1 | 1 | 100 | 10 |   500 |  60 |
|          1 |        2 | 1 | 100 | 20 |   505 |  50 |
|          1 |        3 | 1 | 200 | 10 |   510 |  30 |
|          1 |        4 | 1 | 200 | 20 |   515 |  25 |
|          1 |        5 | 1 | 300 | 10 |   520 |  15 |
|          1 |        6 | 1 | 300 | 20 |   525 |  50 |
|          1 |        7 | 2 | 100 | 10 |   530 |  40 |
|          1 |        8 | 2 | 100 | 20 |   535 |  35 |
|          1 |        9 | 2 | 200 | 10 |   540 |  60 |
+------------+----------+---+-----+----+-------+-----+

实际上,此表将包含数百种产品,a、b和c列中的每一列都可能占据大约10个值,并且每个产品的每一列组合都有一个价格。

我只想显示每个产品的1个价格,所以我有一个 GROUP BY 在product\u id上。

SELECT 最小(价格),没问题。现在,当我想显示与最低价格相关的费用时,我不能只显示最低价格,因为价格和费用不相关,最低价格不一定有最低费用。所以我加入了一个子查询,就像这样;

SELECT
    t.product_id,
    t.price_id,
    t.a,
    t.b, 
    t.c, 
    min(t.price) as `min_price`,
    t.fee,
    t2.fee AS `min_price_fee`
FROM
    prices as t
JOIN so_q as t2 on t.product_id = t2.product_id
    AND t.a = t2.a
    AND t.b = t2.b
    AND t.c = t2.c
    AND t2.price = (
        SELECT min(price)
        FROM so_q as t3 
        WHERE t3.product_id = t.product_id
--          AND t3.b = 300
    )
-- WHERE
--  t.b = 300
GROUP BY
    t.product_id;

但正如你可能已经从我注释掉的行中猜到的那样,当用户添加了过滤器并且现在有了where子句时,我的问题就来了。如果不将where子句放入子查询中,我就无法完成这项工作,(如果不这样做,我就不会返回任何行,我想我可以理解),我的问题是,有没有办法做到这一点,这样我只需要使用where子句一次?

谢谢你的建议-如果我应该包括任何其他信息,请告诉我。试图从我正在使用的实际代码中提取MCVE很复杂,所以我可能忘记了一些明显的东西。

编辑比如MySQL版本5.5.56

编辑2

使用@Gordon Linoff的建议;

SELECT
    p.* 
FROM
    prices p 
WHERE
    p.price = ( 
        SELECT min( p2.price )
        FROM prices p2
        WHERE p2.product_id = p.product_id
    )
AND b = 300;

添加时仍返回0行 b = 300 最后一行where子句的条件。

编辑3

为了尝试并澄清我要做的事情:在添加任何过滤器之前,对于产品1,我想显示该记录中的最低价格(500)和费用(60)(price\u id=1)。如果用户添加过滤器规定 c = 20 ,然后我想显示具有 c 价值20(505)和该记录中的费用(50)(price\u id=2)。我想我不能用 min(price) 和 min(fee) 因为我最终会得到来自不同记录的价格和费用,它们必须来自同一个记录。因此,我需要找到满足所有用户输入标准(最终作为主where子句的一部分)的最低价格,然后找到与该价格相关的费用。

3 回复 | 直到 7 年前

MatBailie 7 年前

采用@GordonLinoff的答案,并扩展需求以包括最小化代码重复量,从而简化SQL的动态生成。。。

将相关子查询更改为返回行标识符而不是最小价格会产生两种后果

您只需要在子查询中放置过滤器
如果出现平局,它将永远不会返回多行

p.id = (SELECT id
          FROM prices p2
         WHERE p2.product_id = p.product_id
           AND <filters>
      ORDER BY price DESC,
               some_tie_break_field(s)
         LIMIT 1
       )

有了这样的结构,您可能会从 product 表以最小化相关子查询所做的工作。

SELECT
  prices.*
FROM
  product
INNER JOIN
  prices
      ON prices.id = (SELECT id
                        FROM prices p
                       WHERE p.product_id = product.id
                         AND <filters>
                    ORDER BY price DESC,
                             some_tie_break_field(s)
                       LIMIT 1
                      )

Radim BaÄa 7 年前

正如我在评论中提到的,有使用窗口函数和CTE的解决方案,但是,这些在MySQL的较低版本中不可用。在常数不重复的情况下,我可以提出的唯一解决方案是:

SELECT
    t.product_id,
    t.price_id,
    t.a,
    t.b, 
    t.c, 
    min(t.price) as `min_price`,
    t.fee,
    t2.fee AS `min_price_fee`
FROM
    prices as t
JOIN so_q as t2 on t.product_id = t2.product_id
    AND t.a = t2.a
    AND t.b = t2.b
    AND t.c = t2.c
    AND t.b = 300
    AND t2.price = (
        SELECT min(price)
        FROM so_q as t3 
        WHERE t3.product_id = t.product_id  AND 
              t3.b = t.b
    )

Gordon Linoff 7 年前

不使用 group by . 要过滤掉所有其他行,请使用 where :

select p.*
from prices p
where p.price = (select min(p2.price)
                 from prices p2
                 where p2.product_id = p.product_id
                );

如果需要筛选 b ,那么您需要在子查询和外部查询中都考虑到这一点:

select p.*
from prices p
where p.b = 300 and
      p.price = (select min(p2.price)
                 from prices p2
                 where p2.product_id = p.product_id and p2.b = p.b
                );