select c.item_id,
c.type as item_type, c.item_name , floor(c.item_price) as item_price, c.item_unit,
c.state as item_state_code,
case c.state when 0 then '停用' when 1 then '启用' end as item_state_name,
b.order_id,
b.state,
case b.state when 0 then '已终止' when 1 then '待付款' when 2 then '预约中' when 3 then '待咨询' when 4 then '咨询中' when 5 then '待评价'
when 7 then '待退款' when 8 then '退款_售后' when 9 then '已完成' end as order_state_name,
b.appointment_time ,
case dayofweek(b.appointment_time) when 1 then '周日' when 2 then '周一' when 3 then '周二'
when 4 then '周三' when 5 then '周四' when 6 then '周五' when 7 then '周六' end as week,
DATE_FORMAT(b.appointment_time, '%H:%i') as time
from consultation_item c left join
( select a.order_id, a.state, a.doctor_user_id, a.category, d.appointment_time from consultation_order a , consultation_rec d
where a.doctor_user_id=1 and a.patient_id=2 and a.order_id = d.order_id
group by a.category order by pay_time desc
) b
on c.user_id = b.doctor_user_id and c.type=b.category
where c.user_id = 1
order by c.type asc;
