sql的寫法

浏览数：20 / 时间：2015年06月12日

說明:logistics_detail表中每個deliver_id可能對應多個loginticsType,但是我們只要獲取最大的那一個.

SELECT dm.delivery_code,dm.deliver_id,om.order_id,DATE(FROM_UNIXTIME(om.order_createdate)) as order_date,om.order_status,om.order_payment,dm.delivery_store,
ld.logisticsTypes,dm.delivery_status,vd.vendor_name_simple,dm.freight_set,dm.delivery_freight_cost,dm.delivery_date,dm.arrival_date,dm.estimated_delivery_date
,dm.estimated_arrival_date,dm.estimated_arrival_period,dm.delivery_name,pt.product_name,odt.buy_num
                FROM deliver_master dm
                LEFT JOIN vendor vd on vd.vendor_id=dm.export_id
                LEFT JOIN order_master om on om.order_id=dm.order_id
                LEFT JOIN (SELECT max(logisticsType) as logisticsTypes, deliver_id fROM logistics_detail GROUP BY deliver_id ) as ld
on ld.deliver_id=dm.deliver_id
                LEFT JOIN order_slave os on os.order_id=om.order_id
                LEFT JOIN order_detail odt on odt.slave_id=os.slave_id
                LEFT JOIN product_item pii on pii.item_id =odt.item_id
                LEFT JOIN product pt on pii.product_id=pt.product_id where 1=1   and om.order_createdate >=‘1430409600‘ and om.order_createdate <=‘1432656000‘ limit 0,25;

SELECT dm.delivery_code,dm.deliver_id,om.order_id,DATE(FROM_UNIXTIME(om.order_createdate)) as order_date,om.order_status,om.order_payment,dm.delivery_store
,dm.delivery_status,vd.vendor_name_simple,dm.freight_set,dm.delivery_freight_cost,dm.delivery_date,dm.arrival_date,dm.estimated_delivery_date
,dm.estimated_arrival_date,dm.estimated_arrival_period,dm.delivery_name,pt.product_name,odt.buy_num,
(SELECT logisticsType FROM logistics_detail ld where dm.deliver_id = ld.deliver_id ORDER BY ld.rid DESC LIMIT 1) as logisticsTypes
                FROM deliver_master dm
                LEFT JOIN vendor vd on vd.vendor_id=dm.export_id
                LEFT JOIN order_master om on om.order_id=dm.order_id
                LEFT JOIN order_slave os on os.order_id=om.order_id
                LEFT JOIN order_detail odt on odt.slave_id=os.slave_id
                LEFT JOIN product_item pii on pii.item_id =odt.item_id
                LEFT JOIN product pt on pii.product_id=pt.product_id where 1=1   and om.order_createdate >=‘1430409600‘ and om.order_createdate <=‘1432656000‘ limit 0,25;