导出订单SQL语句【8】

select 'country_id' AS customers_country,
'delivery_name' AS delivery_name,
'delivery_street_address' AS delivery_street_address,
'delivery_city' AS delivery_city,
'delivery_postcode' AS delivery_postcode,
'delivery_phone' AS delivery_phone,
'delivery_state' AS delivery_state ,
'delivery_country' AS delivery_country,
'payment_method' AS payment_method,
'date_purchased' AS date_purchased,
'orders_status_name' AS orders_status_name,
'currency' AS currency,
'products_id' AS products_id,
'products_model' AS products_model,
'products_name' AS products_name,
'products_price' AS products_price,
'products_tax' AS products_tax,
'final_price' AS final_price,
'products_quantity' AS products_quantity,
'order_subtotal' AS order_subtotal,
'coupon_code' AS coupon_code,
'discount_coupon' AS discount_coupon,
'group_discount' AS group_discount,
'gift_certificates' AS gift_certificates,
'shipping_method' AS shipping_method,
'shipping' AS shipping,
'tax' AS tax,
'grand_total' AS grand_total
union all
SELECT DISTINCT
country_id AS customers_country,
CONCAT(customer_firstname,customer_lastname) AS delivery_name,
street AS delivery_street_address,
city AS delivery_city,
postcode AS delivery_postcode,
telephone AS delivery_phone,
state AS delivery_state ,
country_id AS delivery_country,
'' AS payment_method,
'' AS date_purchased,
STATUS AS orders_status_name,
store_currency_code AS currency,
product_id AS products_id,
sku AS products_model,
NAME AS products_name,
price AS products_price,
'' AS products_tax,
row_total AS final_price,
'1' AS products_quantity,
row_total AS order_subtotal,
coupon_code AS coupon_code,
'' AS discount_coupon,
'' AS group_discount,
'' AS gift_certificates,
shipping_method AS shipping_method,
shipping_invoiced AS shipping,
price_incl_tax AS tax,
row_total AS grand_total
FROM  
sales_flat_order AS o INNER JOIN sales_flat_order_address AS a INNER JOIN sales_flat_order_item AS i 
WHERE o.entity_id=i.order_id AND o.entity_id=a.parent_id AND address_type='billing'