您可以：

select
  t.receipt,
  case when count(case when f.fruit_color = 'YELLOW' then 1 end) > 0
      then 'yes' else 'no' end as has_yellow,
  case when count(case when f.fruit_color = 'GREEN' then 1 end) > 0
      then 'yes' else 'no' end as has_green,
  sum(t.amount) as amount  
from fruit_transport t
join fruit f on f.fruit_id = t.fruit_id
group by t.receipt

与前面的答案略有不同，您可以在case when表达式中生成'yes'字符串，取组中的最大值，并使用coalesce给予默认值'no'：

select receipt,
       coalesce(max(case fruit_color when 'YELLOW' then 'yes' end), 'no') has_yellow,
       coalesce(max(case fruit_color when 'GREEN'  then 'yes' end), 'no') has_green,
       sum(amount) amount
from   fruit f
inner join fruit_transport ft on f.fruit_id = ft.fruit_id
group by receipt;