maisikiuel (mySQL pa lo careta)

ee amigo tene cuidado con la subquerys q te rompen to! y por primera ve te vamo a mostra como se hacen la cosa cheta.

Eto tarda en correr 9 minutos….

SELECT p.sku AS "Product code",
 p.annotation AS "Product Description",
  (SELECT COUNT(orders_products.fk_product_id) AS "count"
   FROM orders_products
   INNER JOIN orders
     ON orders.id = orders_products.fk_order_id
     AND orders.order_created >= '2008-12-01 00:00:00'
     AND orders.order_created < = '2008-12-07 24:00:00'
   INNER JOIN products
     ON products.id = orders_products.fk_product_id
     AND products.sku = p.sku
   WHERE orders_products.fk_order_status_id >= 30) AS "Total number of units sold",
    (
      SELECT SUM((orders_products.price-orders_products.discount)/1.15) AS "sum"
      FROM orders_products
      INNER JOIN orders
        ON orders.id = orders_products.fk_order_id
        AND orders.order_created >= '2008-12-01 00:00:00'
        AND orders.order_created < = '2008-12-07 24:00:00'
      INNER JOIN products
        ON products.id = orders_products.fk_product_id
        AND products.sku = p.sku
      WHERE orders_products.fk_order_status_id >= 30
     ) AS "Total value"
     FROM products p
      WHERE (
        SELECT COUNT(orders_products.fk_product_id) AS "count"
        FROM orders_products
        INNER JOIN orders
          ON orders.id = orders_products.fk_order_id
          AND orders.order_created >= '2008-12-01 00:00:00'
          AND orders.order_created < = '2008-12-07 24:00:00'
        INNER JOIN products
          ON products.id = orders_products.fk_product_id
          AND products.sku = p.sku
        WHERE orders_products.fk_order_status_id >= 30
      ) > 0

y eto tarda en correr 400 milisegundos…y da lo mismo….

SELECT products.sku, products.annotation,
  COUNT(orders_products.fk_product_id) AS "count",
  SUM ((orders_products.price-orders_products.discount)/1.15) AS "sum"
  FROM orders_products
  INNER JOIN orders
    ON orders.id = orders_products.fk_order_id
    AND orders.order_created >= '2008-12-01 00:00:00'
    AND orders.order_created < = '2008-12-07 24:00:00'
  INNER JOIN products
    ON products.id = orders_products.fk_product_id
  WHERE orders_products.fk_order_status_id >= 30
  GROUP BY products.sku, products.annotation

una cosa es una cosa y otra cosa es otra cosa….

Social Share Toolbar

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *