Digamos que tengo una tabla de precios;

+------------+----------+---+-----+----+-------+-----+
| 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 |
+------------+----------+---+-----+----+-------+-----+

En realidad, esta tabla tendría cientos de productos y cada una de las columnas a, byc podría tomar alrededor de 10 valores y habría un precio por cada combinación de estas columnas para cada producto.

Solo quiero mostrar 1 precio por producto, por lo que tengo un GROUP BY en product_id.

Digamos que inicialmente quiero mostrar el precio más bajo para cada producto, puedo lograr esto en SELECT min (precio), no hay problema. Ahora, cuando quiero mostrar la tarifa relacionada con el precio mínimo, no puedo mostrar solo la tarifa mínima porque los precios y las tarifas no se correlacionan y el precio mínimo no necesariamente tiene la tarifa más baja. Entonces me uno a una subconsulta, así;

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;

Pero como habrás adivinado por las líneas que he comentado, mi problema surge cuando los usuarios han agregado filtros y ahora hay una cláusula where en juego. No puedo hacer que esto funcione sin poner también la cláusula where en la subconsulta (si no lo hago, no me devuelven filas, lo que creo que entiendo) y mi pregunta es, ¿hay alguna manera de hacerlo? que solo tengo que tener la cláusula where una vez?

Gracias por su consejo, avíseme si debo incluir alguna otra información. Intentar destilar un MCVE del código real con el que estoy trabajando fue complicado, por lo que es posible que haya olvidado algo obvio.

EDITAR como la versión de MySQL que es 5.5.56

EDITAR 2

Usando la sugerencia de @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;

Todavía recibo 0 filas devueltas cuando agrego la condición b = 300 a la cláusula where en la última línea.

EDITAR 3

Para tratar de aclarar lo que estoy tratando de hacer: antes de agregar filtros, para el producto 1, quiero mostrar el precio mínimo (500) y la tarifa (60) de ese registro (price_id = 1). Si un usuario agrega un filtro que estipula c = 20, entonces quiero mostrar el precio mínimo que tiene un valor c de 20 (505) y la tarifa (50) de ese registro (price_id = 2). No creo que pueda usar min(price) y min(fee) porque terminaré con precios y tarifas de diferentes registros y deben ser del mismo registro. Por lo tanto, necesito encontrar el precio mínimo que satisfaga todos los criterios ingresados por el usuario (que terminan como parte de la cláusula where principal) y luego encontrar la tarifa asociada con ese precio.

0
sauntimo 15 ene. 2018 a las 16:56

3 respuestas

La mejor respuesta

Tomando la respuesta de @ GordonLinoff y ampliando el requisito de incluir la minimización de la cantidad de repetición de código, para simplificar la generación dinámica del SQL ...

Cambiar la subconsulta correlacionada para devolver un identificador de fila en lugar de un precio mínimo tiene dos consecuencias

  1. Solo necesita poner el filtro en la subconsulta
  2. Nunca devolverá varias filas en caso de empate


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
       )

Con tal estructura, probablemente se beneficiaría de comenzar con una tabla product para minimizar el trabajo realizado por la subconsulta correlacionada.

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
                      )
1
MatBailie 17 ene. 2018 a las 11:18

Como mencioné en los comentarios, hay soluciones que utilizan la función de ventana y CTE, sin embargo, no están disponibles en versiones inferiores de MySQL. La única solución que puedo proponer donde la constante no se repite es la siguiente:

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
    )
1
Radim Bača 15 ene. 2018 a las 14:35

No uses group by. Desea filtrar todas las demás filas, así que use where:

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

Si necesita filtrar en b, debe tener esto en cuenta tanto en la subconsulta como en la consulta externa:

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
                );
1
Gordon Linoff 15 ene. 2018 a las 17:38
48264466