mysql - 使用 mysql 按三个条件排序

Question

我有一个连接查询，我需要用三个条件记录 order by 1. purch_order_workflow_code_nr = '3' 的记录排在第一位 2.那些记录状态=空其次 3.之后是所有铰孔记录

请给我一个正确的解决方案，我应该如何写这个我已经尝试过这段代码。在我的查询中，但我没有得到我想要的

SELECT purch_order.user_code_tx as user_code_tx ,purch_order.date_creation 
    as date_creation ,purch_order.purch_order_code_nr as purch_order_code_nr 
    ,purch_order.status as status ,purch_order.purch_order_workflow_code_nr as purch_order_workflow_code_nr ,ppod.vehicle_plate_tx as vehicle_plate_tx ,(Select pd.purch_order_detail_desc_tx from purch_order_detail pd where pd.purch_order_code_nr = purch_order.purch_order_code_nr limit 1 ) as purch_order_detail_desc_tx ,(Select pd.purch_order_workflow_desc_tx from purch_order_workflow pd where pd.purch_order_workflow_code_nr = purch_order. purch_order_workflow_code_nr limit 1 ) as purch_order_workflow_desc_tx ,vehicle.op_base_code_nr as op_base_code_nr ,company_division.div_name_tx as div_name_tx ,company_division_op_bases.op_base_name as op_base_name 
FROM purch_order inner join (Select distinct pod.purch_order_code_nr, pod.vehicle_plate_tx from purch_order_detail pod) ppod on ppod.purch_order_code_nr = purch_order.purch_order_code_nr inner join vehicle on vehicle.vehicle_plate_tx = ppod.vehicle_plate_tx inner join company_division on company_division.div_code_nr= purch_order.div_code_nr inner join company_division_op_bases on company_division_op_bases.op_base_code_nr = vehicle.op_base_code_nr 
WHERE purch_order.company_code_nr='1000' LIMIT 7; 

Answer 1

这应该适合你:

SELECT purch_order.user_code_tx as user_code_tx ,purch_order.date_creation 
    as date_creation ,purch_order.purch_order_code_nr as purch_order_code_nr ,purch_order.status as status ,purch_order.purch_order_workflow_code_nr as purch_order_workflow_code_nr ,ppod.vehicle_plate_tx as vehicle_plate_tx ,(Select pd.purch_order_detail_desc_tx from purch_order_detail pd where pd.purch_order_code_nr = purch_order.purch_order_code_nr limit 1 ) as purch_order_detail_desc_tx ,(Select pd.purch_order_workflow_desc_tx from purch_order_workflow pd where pd.purch_order_workflow_code_nr = purch_order. purch_order_workflow_code_nr limit 1 ) as purch_order_workflow_desc_tx ,vehicle.op_base_code_nr as op_base_code_nr ,company_division.div_name_tx as div_name_tx ,company_division_op_bases.op_base_name as op_base_name, 
    CASE 
    WHEN purch_order_workflow_code_nr = '3' THEN 1
    WHEN status = '' THEN 2
    ELSE 3
    END
    AS order_bit
FROM purch_order inner join (Select distinct pod.purch_order_code_nr, pod.vehicle_plate_tx from purch_order_detail pod) ppod on ppod.purch_order_code_nr = purch_order.purch_order_code_nr inner join vehicle on vehicle.vehicle_plate_tx = ppod.vehicle_plate_tx inner join company_division on company_division.div_code_nr= purch_order.div_code_nr inner join company_division_op_bases on company_division_op_bases.op_base_code_nr = vehicle.op_base_code_nr 
WHERE purch_order.company_code_nr='1000' 
ORDER BY order_bit
LIMIT 7;