1.1原sql查询的时候left join的 某张表的数据为null
SELECT
t.bill_no billNo,
t.item_code itemCode,
t.status pickTicketStatus,
a.status pickTicketActStatus,
i.status outinvStatus,
t.flowline flowline,
b.crossing crossing,
b.rcv_man rcvMan,
b.due_date dueDate,
o.create_date createTime,
o.process_date processTime
FROM
wms_pick_batch b
LEFT JOIN wms_pick_ticket t ON b.id = t.pick_batch_id
LEFT JOIN wms_pick_ticket_act a ON t.id = a.pick_ticket_id
LEFT JOIN wms_inf_ship_order o ON o.bill_no = t.bill_no
LEFT JOIN wms_outinv_detail d ON d.pick_batch_code = t.pick_batch_code
LEFT JOIN wms_outinv i ON i.load_no = d.load_no
WHERE
o.process_status = 1
AND a.status != 2
AND t.bill_no = 'LD000000001024103' ;
1.2 原sql查询不到数据,因为 a.status 为null !=2 不成立,修改
将 AND a.status != 2
修改为
AND IFNULL(a.status,'') !=2 ;
1.3 对null 排序
select * from test order by if(isnull(num),0,1),num;
//说明 如果是null 就赋值 为0 ,否再就是1