sql

SELECT
t1.order_id,
t1.order_no,
t1.user_id,
t1.user_name,
t1.user_mobile,
t1.order_state,
t1.biz_type,
t1.pay_no,
t1.pay_way,
t1.app_state,
t1.create_time,
t1.pay_time,
t1.refund_time,
t1.coupon_id,
t1.total_fee,
t1.discount_fee,
t1.express_fee,
t1.pay_fee,
t1.source,
t1.cancel_time,
t1.activity_id,
t1.parent_order,
t1.biz_id,
t1.merchant_id,
t1.merchant_name,
t1.merchant_mobile,
t1.delivery_type,
t1.goods_id,
t1.goods_name,
t1.purchase_price,
t1.sale_price,
t1.goods_thumb,
t1.brand_id,
t1.order_detail_id,
t1.usage_state,
t1.refund_state,
t1.remark,
t1.reduce_money,
t1.redbag_money,
t1.balance_money,
t1.coupon_money,
t1.express_type,
t1.postage_type,
t1.trade_money,
t1.trade_rate,
t1.union_order_id,
t1.complete_time,
t1.payment_type,
t1.cate_id,
t1.attach_goods_id,
t1.cash_amount,
t1.redbag_ids,
t1.perpay_fee,
t1.cash_money,
t1.refundable_deposit,
t1.damage_fee,
t1.lease_time,
t1.lease_days,
t1.lease_begin_time,
t1.lease_end_time,
t1.lease_pay_deposit,
t1.lease_deposit,
t1.damage_deposit,
t1.lease_goods_no,
t1.delay_days,
t1.delay_fee,
t1.score,
t1.deal_state,
t1.operator_id,
t1.operator_name,
t1.order_type,
DATEDIFF( t2.lease_end_time, t1.create_time ) AS diff_day 
FROM
(
SELECT
tem1.order_id,
tem1.order_no,
tem1.user_id,
tem1.user_name,
tem1.user_mobile,
tem1.order_state,
tem1.biz_type,
tem1.pay_no,
tem1.pay_way,
tem1.app_state,
tem1.create_time,
tem1.pay_time,
tem1.refund_time,
tem1.coupon_id,
tem1.total_fee,
tem1.discount_fee,
tem1.express_fee,
tem1.pay_fee,
tem1.source,
tem1.cancel_time,
tem1.activity_id,
tem1.parent_order,
tem1.biz_id,
tem1.merchant_id,
tem1.merchant_name,
tem1.merchant_mobile,
tem1.delivery_type,
tem1.goods_id,
tem1.goods_name,
tem1.purchase_price,
tem1.sale_price,
tem1.goods_thumb,
tem1.brand_id,
tem1.order_detail_id,
tem1.usage_state,
tem1.refund_state,
tem1.remark,
tem1.reduce_money,
tem1.redbag_money,
tem1.balance_money,
tem1.coupon_money,
tem1.express_type,
tem1.postage_type,
tem1.trade_money,
tem1.trade_rate,
tem1.union_order_id,
tem1.complete_time,
tem1.payment_type,
tem1.cate_id,
tem1.attach_goods_id,
tem1.cash_amount,
tem1.redbag_ids,
tem1.perpay_fee,
tem1.cash_money,
tem1.refundable_deposit,
tem1.damage_fee,
tem1.lease_time,
tem1.lease_days,
tem1.lease_begin_time,
tem1.lease_end_time,
tem1.lease_pay_deposit,
tem1.lease_deposit,
tem1.damage_deposit,
tem1.lease_goods_no,
tem1.delay_days,
tem1.delay_fee,
tem1.score,
tem1.deal_state,
tem1.operator_id,
tem1.operator_name,
tem1.order_type,
( @rownums := @rownums + 1 ) AS rownum 
FROM
(
SELECT
o.order_id,
o.order_no,
o.user_id,
o.user_name,
o.user_mobile,
d.order_state,
d.biz_type,
o.pay_no,
o.pay_way,
o.app_state,
o.create_time,
o.pay_time,
o.refund_time,
o.coupon_id,
o.total_fee,
o.discount_fee,
o.express_fee,
o.pay_fee,
o.source,
o.cancel_time,
o.activity_id,
o.parent_order,
o.biz_id,
o.merchant_id,
o.merchant_name,
o.merchant_mobile,
o.delivery_type,
d.goods_id,
d.goods_name,
d.purchase_price,
d.sale_price,
d.goods_thumb,
d.brand_id,
d.order_detail_id,
d.usage_state,
d.refund_state,
d.remark,
o.reduce_money,
o.redbag_money,
o.balance_money,
o.coupon_money,
o.express_type,
o.postage_type,
o.trade_money,
o.trade_rate,
o.union_order_id,
o.complete_time,
d.payment_type,
d.cate_id,
d.attach_goods_id,
o.cash_amount,
o.redbag_ids,
o.perpay_fee,
o.cash_money,
o.refundable_deposit,
o.damage_fee,
o.lease_time,
d.lease_days,
d.lease_begin_time,
d.lease_end_time,
d.lease_pay_deposit,
d.lease_deposit,
d.damage_deposit,
d.lease_goods_no,
d.delay_days,
d.delay_fee,
o.score,
o.deal_state,
o.operator_id,
o.operator_name,
o.order_type 
FROM
t_order o
INNER JOIN t_order_detail d ON o.order_id = d.order_id 
WHERE
o.parent_order = 9943479812 
AND o.order_type IN ( 1, 2 ) 
ORDER BY
o.create_time DESC 
) tem1,
( SELECT @rownums := 0 ) r 
) t1
INNER JOIN (
SELECT
tem2.order_id,
tem2.order_no,
tem2.user_id,
tem2.user_name,
tem2.user_mobile,
tem2.order_state,
tem2.biz_type,
tem2.pay_no,
tem2.pay_way,
tem2.app_state,
tem2.create_time,
tem2.pay_time,
tem2.refund_time,
tem2.coupon_id,
tem2.total_fee,
tem2.discount_fee,
tem2.express_fee,
tem2.pay_fee,
tem2.source,
tem2.cancel_time,
tem2.activity_id,
tem2.parent_order,
tem2.biz_id,
tem2.merchant_id,
tem2.merchant_name,
tem2.merchant_mobile,
tem2.delivery_type,
tem2.goods_id,
tem2.goods_name,
tem2.purchase_price,
tem2.sale_price,
tem2.goods_thumb,
tem2.brand_id,
tem2.order_detail_id,
tem2.usage_state,
tem2.refund_state,
tem2.remark,
tem2.reduce_money,
tem2.redbag_money,
tem2.balance_money,
tem2.coupon_money,
tem2.express_type,
tem2.postage_type,
tem2.trade_money,
tem2.trade_rate,
tem2.union_order_id,
tem2.complete_time,
tem2.payment_type,
tem2.cate_id,
tem2.attach_goods_id,
tem2.cash_amount,
tem2.redbag_ids,
tem2.perpay_fee,
tem2.cash_money,
tem2.refundable_deposit,
tem2.damage_fee,
tem2.lease_time,
tem2.lease_days,
tem2.lease_begin_time,
tem2.lease_end_time,
tem2.lease_pay_deposit,
tem2.lease_deposit,
tem2.damage_deposit,
tem2.lease_goods_no,
tem2.delay_days,
tem2.delay_fee,
tem2.score,
tem2.deal_state,
tem2.operator_id,
tem2.operator_name,
tem2.order_type,
( @rownum := @rownum + 1 ) AS rownum 
FROM
(
SELECT
o.order_id,
o.order_no,
o.user_id,
o.user_name,
o.user_mobile,
d.order_state,
d.biz_type,
o.pay_no,
o.pay_way,
o.app_state,
o.create_time,
o.pay_time,
o.refund_time,
o.coupon_id,
o.total_fee,
o.discount_fee,
o.express_fee,
o.pay_fee,
o.source,
o.cancel_time,
o.activity_id,
o.parent_order,
o.biz_id,
o.merchant_id,
o.merchant_name,
o.merchant_mobile,
o.delivery_type,
d.goods_id,
d.goods_name,
d.purchase_price,
d.sale_price,
d.goods_thumb,
d.brand_id,
d.order_detail_id,
d.usage_state,
d.refund_state,
d.remark,
o.reduce_money,
o.redbag_money,
o.balance_money,
o.coupon_money,
o.express_type,
o.postage_type,
o.trade_money,
o.trade_rate,
o.union_order_id,
o.complete_time,
d.payment_type,
d.cate_id,
d.attach_goods_id,
o.cash_amount,
o.redbag_ids,
o.perpay_fee,
o.cash_money,
o.refundable_deposit,
o.damage_fee,
o.lease_time,
d.lease_days,
d.lease_begin_time,
d.lease_end_time,
d.lease_pay_deposit,
d.lease_deposit,
d.damage_deposit,
d.lease_goods_no,
d.delay_days,
d.delay_fee,
o.score,
o.deal_state,
o.operator_id,
o.operator_name,
o.order_type 
FROM
t_order o
INNER JOIN t_order_detail d ON o.order_id = d.order_id 
WHERE
o.parent_order = 9943479812 
AND o.order_type IN ( 1, 2 ) 
ORDER BY
o.create_time DESC 
) tem2,
( SELECT @rownum := 0 ) r 
) t2 ON t1.rownum = t2.rownum - 1