Autor | Zpráva | ||
---|---|---|---|
igid26 Profil |
#1 · Zasláno: 14. 3. 2021, 08:14:07
Zdravím,
potreboval by som poradiť s mojim SQL. Vopred upozorňujem, že SQL moc neovládam a uvedený kód som si stiahol a upravil podľa seba. Potreboval by som však spojiť riadky s rovnakým ID. Konkrétne sa jedná o stĺpec order_id. Hodnoty Qty, lineTotal, subtotal, sú číselné by sa mali navzájom spočítať. Hodnoty order_item_name sú názvy produktov a mali by navzájom spojiť, respektíve oddeliť čiarkou. select p.ID as order_id, p.post_date, p.post_status, i.order_item_name, i.order_id, i.order_item_id, i.order_item_type, max( CASE WHEN im.meta_key = '_product_id' and i.order_item_id = im.order_item_id THEN im.meta_value END ) as productID, max( CASE WHEN im.meta_key = '_qty' and i.order_item_id = im.order_item_id THEN im.meta_value END ) as Qty, max( CASE WHEN im.meta_key = '_line_total' and i.order_item_id = im.order_item_id THEN im.meta_value END ) as lineTotal, max( CASE WHEN im.meta_key = '_line_subtotal' and i.order_item_id = im.order_item_id THEN im.meta_value END ) as subtotal, max( CASE WHEN pm.meta_key = '_billing_email' and p.ID = pm.post_id THEN pm.meta_value END ) as billing_email, max( CASE WHEN pm.meta_key = '_billing_first_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_first_name, max( CASE WHEN pm.meta_key = '_billing_last_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_last_name, max( CASE WHEN pm.meta_key = '_billing_address_1' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_address_1, max( CASE WHEN pm.meta_key = '_billing_address_2' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_address_2, max( CASE WHEN pm.meta_key = '_billing_city' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_city, max( CASE WHEN pm.meta_key = '_billing_state' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_state, max( CASE WHEN pm.meta_key = '_billing_postcode' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_postcode, max( CASE WHEN pm.meta_key = '_billing_phone' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_phone, max( CASE WHEN pm.meta_key = '_billing_email' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_email, max( CASE WHEN pm.meta_key = '_billing_country' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_country, max( CASE WHEN pm.meta_key = '_order_total' and p.ID = pm.post_id THEN pm.meta_value END ) as order_total, max( CASE WHEN pm.meta_key = '_order_tax' and p.ID = pm.post_id THEN pm.meta_value END ) as order_tax, max( CASE WHEN pm.meta_key = '_paid_date' and p.ID = pm.post_id THEN pm.meta_value END ) as paid_date from wp_posts as p, wp_postmeta as pm, wp_woocommerce_order_items as i, wp_woocommerce_order_itemmeta as im where p.post_type = 'shop_order' and p.ID = pm.post_id and p.ID = i.order_id and order_item_type = 'line_item' group by i.order_item_id HAVING _billing_email = 'jankohrasko@mojemail.sk' ORDER BY `Qty` ASC Samozrejme ak by niekto prišiel s nejakým elegantnejším riešením budem rád :) Vopred ďakujem za pomoc. |
||
Keeehi Profil |
#2 · Zasláno: 15. 3. 2021, 00:54:55
Měl jsi tam obrovský cross join. Na tom by se ti ten dotaz hodně brzo utavil. Ono by asi šlo něco nedělat jako subquery ale upřímně, nechce se mi o tom moc přemýšlet. Už takto musím v hlavě držet 4 tabulky a když nemám možnost si to vyzkoušet, tak se do takového mentáního cvičení pouštět nebudu.
To že si to nemůžu otestovat oproti databázi má ještě jeden důsledek - můžou v tom být chyby. Pokud ano, tak to snad zvládneš opravit, přehledné to snad je, takže pokud jsem někde zaměnil jeden typ id za jiný, nebo tak podobně, tak snad na to přijdeš. SELECT * FROM ( SELECT ID AS order_id, post_date, post_status FROM wp_posts WHERE post_type = 'shop_order' ) AS orders LEFT JOIN ( SELECT post_id AS order_id, MAX(CASE WHEN meta_key = '_billing_first_name' THEN meta_value END) AS _billing_first_name, MAX(CASE WHEN meta_key = '_billing_last_name' THEN meta_value END) AS _billing_last_name, MAX(CASE WHEN meta_key = '_billing_address_1' THEN meta_value END) AS _billing_address_1, MAX(CASE WHEN meta_key = '_billing_address_2' THEN meta_value END) AS _billing_address_2, MAX(CASE WHEN meta_key = '_billing_city' THEN meta_value END) AS _billing_city, MAX(CASE WHEN meta_key = '_billing_state' THEN meta_value END) AS _billing_state, MAX(CASE WHEN meta_key = '_billing_postcode' THEN meta_value END) AS _billing_postcode, MAX(CASE WHEN meta_key = '_billing_phone' THEN meta_value END) AS _billing_phone, MAX(CASE WHEN meta_key = '_billing_email' THEN meta_value END) AS _billing_email, MAX(CASE WHEN meta_key = '_billing_country' THEN meta_value END) AS _billing_country, MAX(CASE WHEN meta_key = '_order_total' THEN meta_value END) AS order_total, MAX(CASE WHEN meta_key = '_order_tax' THEN meta_value END) AS order_tax, MAX(CASE WHEN meta_key = '_paid_date' THEN meta_value END) AS paid_date FROM wp_postmeta GROUP BY post_id ) AS order_details ON orders.order_id = order_details.order_id LEFT JOIN ( SELECT order_id, GROUP_CONCAT(order_item_name SEPARATOR ', ') AS order_item_names, GROUP_CONCAT(order_items.order_item_id SEPARATOR ', ') AS order_item_ids, GROUP_CONCAT(order_item_type SEPARATOR ', ') AS order_item_types, GROUP_CONCAT(productID SEPARATOR ', ') AS productIDs, -- asi je stejné jako order_item_ids, ale v query to máš, tak jsem to nechal i tady SUM(Qty) AS Qty, SUM(lineTotal) AS lineTotal, SUM(subtotal) AS subtotal FROM wp_woocommerce_order_items AS order_items LEFT JOIN ( SELECT order_item_id, MAX(CASE WHEN meta_key = '_product_id' THEN meta_value END) AS productID, MAX(CASE WHEN meta_key = '_qty' THEN meta_value END) AS Qty, MAX(CASE WHEN meta_key = '_line_total' THEN meta_value END) AS lineTotal, MAX(CASE WHEN meta_key = '_line_subtotal' THEN meta_value END) AS subtotal FROM wp_woocommerce_order_itemmeta GROUP BY order_item_id ) AS order_items_details ON order_items.order_item_id = order_items_details.order_item_id WHERE order_item_type = 'line_item' GROUP BY order_id ) AS order_items_sumary ON orders.order_id = order_items_sumary.order_id WHERE _billing_email = 'jankohrasko@mojemail.sk' ORDER BY `Qty`; |
||
igid26 Profil |
#3 · Zasláno: 15. 3. 2021, 10:08:29
Keeehi:
ďakujem moc, funguje skvele. |
||
Časová prodleva: 3 roky
|
0