[工作日记]

写了个长sql

SELECT
    dm_order.order_num AS orderNum,
    dm_order.price,
    dm_order.tip AS tip,
    dm_order.payway,
    dm_order.fetchcode,
    dm_goods.descrip AS remarks,
    dm_goods.weight,
    dm_goods.isfood,
    dm_service.id AS serverId,
    date_format( dm_trip.arrival_time, '%Y年%m月%d日' ) AS endDate,
    date_format( dm_trip.arrival_time, '%H:%i' ) AS endTime,
    dm_trip.arrival_city AS endCity,
    dm_trip.arrival_country AS endCountry,
    date_format( dm_trip.departure_time, '%Y年%m月%d日' ) AS startDate,
    date_format( dm_trip.departure_time, '%H:%i' ) AS startTime,
    dm_trip.departure_city AS startCity,
    dm_trip.departure_country AS startCountry,
    dm_trip.vehicle_type AS toolType,
    count( dm_record.record_no ) AS countRecord,
    dm_peer.peer_headpic AS headpic,
    dm_peer.peer_nickname AS nickname,
    dm_peer.peer_contacttel AS tel,
    startaddress.detailaddress as startAddressDetail,
    startaddress.receivertel as startTel,
    startaddress.receivername as startName,
    endaddress.detailaddress as reciveAddressDetail,
    endaddress.receivertel as receiverTel,
    endaddress.receivername as receiverName
FROM
    dm_service,
    dm_trip,
    dm_record,
    dm_peer,
    dm_goods,
    dm_order,
    (
SELECT
    dm_address.receivername,
    dm_address.receivertel,
    dm_address.detailaddress 
FROM
    dm_address,
    dm_order 
WHERE
    dm_address.id = dm_order.startaddress 
    AND dm_order.order_num = "参数1" 
    ) AS startaddress,
(SELECT
    dm_address.receivername,
    dm_address.receivertel,
    dm_address.detailaddress 
FROM
    dm_address,
    dm_order 
WHERE
    dm_address.id = dm_order.address_id 
    AND dm_order.order_num = "参数1" 
    ) AS endaddress 
WHERE
    dm_order.order_num = "参数1"
    and dm_order.sender_peerid="参数2"
    AND dm_service.id = dm_order.trip_id 
    AND dm_service.deletetime IS NULL 
    AND dm_service.trip_id = dm_trip.id 
    AND dm_trip.peerid = dm_peer.peer_id 
    AND dm_goods.id = dm_order.goods_id 
    AND dm_record.peer_id = dm_service.peerid

 

如上,不是很复杂的查询,写下来就这么长。看起来很难受

再读一遍，看到用的全是最简单逻辑关联，这样虽然逻辑清楚，但是查询效率必然低下。

下面是使用leftjoin和rightjoin重构的sql：