柳暗花明笔记

    大SQL语句收藏

    所属分类:[ php&web ] 猫哲学家 发表于:2016-11-04 16:47 浏览:195

    select distinct
    orders.id_order as "OrderID",
    concat(orders.id_order, "-", orders.reference) as "OrderName",
    orders.current_state as "OrderState",
    order_detail.product_id as "SKU",
    order_detail.product_name as "ProductName",
    order_detail.product_quantity as "Quantity",
    order_detail.total_price_tax_incl as "UnitPrice",
    orders.total_shipping as "ShippingFee",
    orders.total_paid as "Total",
    concat(address.firstname, " ", address.lastname) as "ShippingName",
    address.city as "City",
    case
    when state.name is NULL then
    (case
    when country.iso_code in ("US","UA","CA","GB","FR","AU","IL","NO","SA","RU") then address.city
    else ""
    end
    )
    else state.name
    end as 'State',
    concat("'", address.postcode) as "Postcode",
    case
    when address.phone then concat("'", address.phone)
    when address.phone_mobile then concat("'", address.phone_mobile)
    else concat("'", address.phone_mobile)
    end as 'Telephone',
    country.iso_code as "Country",
    case
    when state.name is NULL then concat(address.address1,",",address.address2,",",address.city,",",address.postcode,",",country_lang.name,",",address.phone,",",address.phone_mobile)
    else concat(address.address1,",",address.address2,",",address.city,",",state.name,",",address.postcode,",",country_lang.name,",",address.phone,",",address.phone_mobile)
    end as 'Full_Address',
    case
    when carrier.name like '%ePacket%' then 'EUB'
    when country_lang.name like 'United States' then 'EUB'
    else 'YW'
    end as 'ShippingMethod',
    case
    when order_detail.product_name like '%mAh%' then 'battery'
    else 'safe'
    end as 'Battery',
    customer.firstname as "BFName",
    customer.lastname as "BLName",
    customer.email as "Email",
    orders.date_add as "OrderDate",
    concat("[", orders.current_state, "] ", carrier.name) as "Carrier",
    country_lang.name as "countryName",
    paypal_usa_transaction.id_transaction,
    address.address1,
    address.address2,
    orders.date_add as "PaidTime"

    from orders

    left join customer
    on orders.id_customer = customer.id_customer

    left join address
    on orders.id_address_delivery = address.id_address

    left join country
    on address.id_country = country.id_country

    left join country_lang
    on country.id_country = country_lang.id_country

    left join state
    on address.id_state = state.id_state

    left join order_carrier
    on orders.id_order = order_carrier.id_order

    left join carrier
    on order_carrier.id_carrier = carrier.id_carrier

    right join order_detail
    on orders.id_order = order_detail.id_order

    left join paypal_usa_transaction
    on orders.id_order = paypal_usa_transaction.id_order

    where orders.id_order > 100000

    order by orders.id_order asc

    标签: mysql 
    上一编文章 自写mysql 的SQL语句收藏case cast 下一编文章 mysql 在查大表时limit速度急剧下降
    最近文章
    发表评论