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
Støv, fibre og metalliske partikler kan påvirke både uptime, levetid og driftssikkerhed. Derfor arbejder flere datacentre systematisk med contamination control.
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.
