连表查询+状态判断

set @rownum=0;
set @rownum1=0;

select u2.ID,

u2.create_time as 创建时间,
u2.base_check_num as 基础验布卷数,
u2.play_roll_num as 打卷验布卷数,
u2.weight_num as 称重验布卷数,
u2.auto_num as 自检验布卷数,
u2.current_times as 当前时间,
u2.TIME1 as 订单所用时间,
case when u2.`status`=2 then '已入库' else '已上机'  end as 订单状态,
(case  when u2.cloth_source=0 then '未知'
WHEN u2.cloth_source=1 THEN '精诚'
WHEN u2.cloth_source=2 THEN '淘工厂'
WHEN u2.cloth_source=3 THEN '散客'
WHEN u2.cloth_source=4 THEN '未下单'
WHEN u2.cloth_source=5 THEN 'LS平台' END
) as 订单来源,
u2.store_name,
sum(u1.TIME1) as 累计时间长,
u2.order_no as 订单号,
date_format(DATE_ADD(date_format(now(),'%Y%m%d%H%i%S'), Interval sum(u1.TIME1) minute),'%Y-%m-%d  %H:%i:%S') as 订单预计完成时间
from (
SELECT @rownum:= @rownum+1 as ID,co.order_no, co.create_time,pcs.store_name, U.base_check_num, U.play_roll_num, U.weight_num, U.auto_num, co.status, co.cloth_source,
date_format(now(),'%Y-%m-%d  %H:%i:%S') as current_times,
format((U.base_check_num*15 + U.play_roll_num*10 + U.weight_num*5 + U.auto_num*15)/pcs.machine_num, 0) as TIME1
FROM (
select lp.order_no,(case when lp.cloth_source = 3 then 0 else 1 end) as plan_cloth_source
,sum(case when pcoi.order_type=1 then pcoi.need_check_roll_quantity else 0 end) as base_check_num
,sum(case when pcoi.order_type=2 then pcoi.need_check_roll_quantity else 0  end) as play_roll_num
,sum(case when pcoi.order_type=3 then pcoi.need_check_roll_quantity else 0 end) as weight_num
,sum(case when pcoi.order_type=4 then pcoi.need_check_roll_quantity else 0 end) as auto_num
from lianshang.pc_cloth_order lp
left join lianshang.pc_cloth_order_item pcoi
on pcoi.cloth_order_no=lp.order_no
where lp.status in (2, 3)
group by lp.order_no
order by lp.`status` desc, plan_cloth_source desc, lp.create_time
) U, pc_cloth_order co, pc_cloth_store pcs
where U.order_no = co.order_no
and co.cloth_store_id = pcs.id
and co.cloth_store_id = 1) u1,
(
SELECT @rownum1:= @rownum1+1 as ID,co.order_no, co.create_time,pcs.store_name, U.base_check_num, U.play_roll_num, U.weight_num, U.auto_num, co.status, co.cloth_source,
date_format(now(),'%Y-%m-%d  %H:%i:%S') as current_times,
format((U.base_check_num*15 + U.play_roll_num*10 + U.weight_num*5 + U.auto_num*15)/pcs.machine_num, 0) as TIME1
FROM (
select lp.order_no,(case when lp.cloth_source = 3 then 0 else 1 end) as plan_cloth_source
,sum(case when pcoi.order_type=1 then pcoi.need_check_roll_quantity else 0 end) as base_check_num
,sum(case when pcoi.order_type=2 then pcoi.need_check_roll_quantity else 0  end) as play_roll_num
,sum(case when pcoi.order_type=3 then pcoi.need_check_roll_quantity else 0 end) as weight_num
,sum(case when pcoi.order_type=4 then pcoi.need_check_roll_quantity else 0 end) as auto_num
from lianshang.pc_cloth_order lp
left join lianshang.pc_cloth_order_item pcoi
on pcoi.cloth_order_no=lp.order_no
where lp.status in (2, 3)
group by lp.order_no
order by lp.`status` desc, plan_cloth_source desc, lp.create_time
) U, pc_cloth_order co, pc_cloth_store pcs
where U.order_no = co.order_no
and co.cloth_store_id = pcs.id
and co.cloth_store_id = 1) u2
WHERE u1.ID<=u2.ID
group by 1

select
(case when pco.`status`=1 then '待送验'
when pco.`status`=2 then '已收货'
when pco.`status`=3 then '验布中'
when pco.`status`=4 then '待确认'
when pco.`status`=5 then '验布关闭'
when pco.`status`=6 then '验布取消'
when pco.`status`=7 then '验布完成'  end)AS 验布状态

,pco.order_no as 验布号,pco.third_order_no as 订单号, pct.store_name as 验布坊名称,
pco.delivery_short_barge_fee as 送检短驳费,
pco.cloth_delivery_short_barge_fee as验布坊后台填写的入库短驳费
from pc_cloth_order pco , pc_cloth_store pct
where pct.id=pco.cloth_store_id
and pco.order_no=1707192011041
and pco.validity=1
;