21. februar 2013 - 11:33Der er
1 kommentar og 1 løsning
JOIN 2 selects
Hej Eksperter
Hvordan "JOINER" jeg to komplette selects ala følgende.
Ordre indgang - Grupperet efter uger ------------------------------- SELECT YEAR(so.order_date) AS year, WEEK(so.order_date) AS week, SUM( si.line_total ) AS indgang FROM sales_order_items AS si LEFT JOIN sales_order AS so ON so.sales_order_id = si.sales_order_id WHERE so.deleted !=1 AND so.order_date IS NOT NULL AND so.deliverydate_wanted >= "2010-01-01" AND so.deliverydate_wanted <= "2013-12-31" GROUP BY YEARWEEK(so.order_date) ORDER by year, week LIMIT 0,200
Ordre udgang - Grupperet efter uger ------------------------------- SELECT YEAR(so.delivery_date) AS year, WEEK(so.delivery_date) AS week, SUM( si.line_total ) AS udgang FROM sales_order_items AS si LEFT JOIN sales_order AS so ON so.sales_order_id = si.sales_order_id WHERE so.deleted !=1 AND so.order_date IS NOT NULL AND so.deliverydate_wanted >= "2010-01-01" AND so.deliverydate_wanted <= "2013-12-31" GROUP BY YEARWEEK(so.delivery_date) ORDER by year, week LIMIT 0,200
Denne virker, men er langsom () (Tid: 1.1595 sek) SELECT YEAR( so.order_date ) AS YEAR, WEEK( so.order_date ) AS week, SUM( si.line_total ) AS indgang, (
SELECT SUM( si.line_total ) AS udgang FROM sales_order_items AS si LEFT JOIN sales_order AS so ON so.sales_order_id = si.sales_order_id WHERE so.deleted !=1 AND so.order_date IS NOT NULL AND YEAR( so.delivery_date ) = YEAR AND WEEK( so.delivery_date ) = week ) AS udgang FROM sales_order_items AS si LEFT JOIN sales_order AS so ON so.sales_order_id = si.sales_order_id WHERE so.deleted !=1 AND so.order_date IS NOT NULL AND so.deliverydate_wanted >= "2010-01-01" AND so.deliverydate_wanted <= "2013-12-31" GROUP BY YEARWEEK( so.order_date ) ORDER BY YEAR, week
Dette er en test med UNION, kan man ikke lave noget ala UNION MERGE eller lign. (Tid: 0.0451 sek) SELECT YEAR( so.order_date ) AS YEAR, WEEK( so.order_date ) AS week, SUM( si.line_total ) AS sum FROM sales_order_items AS si LEFT JOIN sales_order AS so ON so.sales_order_id = si.sales_order_id WHERE so.deleted !=1 AND so.order_date IS NOT NULL AND so.deliverydate_wanted >= "2010-01-01" AND so.deliverydate_wanted <= "2013-12-31" GROUP BY YEARWEEK( so.order_date ) UNION SELECT YEAR( so.delivery_date ) AS YEAR, WEEK( so.delivery_date ) AS week, SUM( si.line_total ) AS sum FROM sales_order_items AS si LEFT JOIN sales_order AS so ON so.sales_order_id = si.sales_order_id WHERE so.deleted !=1 AND so.order_date IS NOT NULL AND so.delivery_date >= "2010-01-01" AND so.delivery_date <= "2013-12-31" GROUP BY YEARWEEK( so.delivery_date ) ORDER BY YEAR, week
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
