查询不存在分组值的列表

Question

寻找一种报告在SQL中“update_code”的列表中不存在“delivery_completed”的PRO_BILL的方法

-很抱歉，一个新手--

pro_bill    | update_code           | entry_timestamp
PRO0211475  | delivery_arrived      | 6/14/18 12:23
PRO0211475  | delivery_completed    | 6/14/18 12:23
PRO0211475  | pick_up_arrived       | 6/14/18 12:23
PRO0211475  | pick_up_completed     | 6/14/18 12:23
PRO0211510  | pick_up_completed     | 6/14/18 11:52
PRO0211510  | pick_up_arrived       | 6/14/18 11:52
PRO0211510  | delivery_arrived      | 6/14/18 11:52
PRO0211527  | delivery_arrived      | 6/14/18 11:27
PRO0211527  | pick_up_arrived       | 6/14/18 11:27
PRO0211527  | pick_up_completed     | 6/14/18 11:27

理想情况下，我希望查询返回(在一行中)，其中delivery_departed不存在于该pro_bill

Pro_bill | pick_up_arrived | pick_up_completed | delivery_arrived

Answer 1

您可能需要条件聚合。

 SELECT 
   pro_bill,
   MAX( CASE WHEN update_code = 'delivery_arrived' THEN entry_timestamp END) as delivery_arrived,
   MAX( CASE WHEN update_code = 'delivery_completed' THEN entry_timestamp END) as delivery_completed,
   MAX( CASE WHEN update_code = 'pick_up_arrived' THEN entry_timestamp END) as pick_up_arrived,
   MAX( CASE WHEN update_code = 'pick_up_completed' THEN entry_timestamp END) as pick_up_completed
FROM YourTable
GROUP BY pro_bill