상품번호 한개에 한줄

방법1

select order_no,
  case
  when cnt=1 then prod_name
  else prod_name || ' 외 ' || cnt-1 || '건'
  end as title
from
  (select x.order_no, z.prod_name, x.cnt
   from
     (select purchase.order_no as order_no, count(*) as cnt
      from purchase, request, prod
      where purchase.order_no=request.order_no
            and request.prod_no=prod.prod_no
      group by purchase.order_no
      order by order_no) x,
     (select distinct on (order_no) order_no as order_no, prod_no from request) y, prod z
   where x.order_no=y.order_no
         and y.prod_no=z.prod_no) a;

방법2

select v1.*, v2.*
from
  (select a.order_no,
     max(a.prod_no) as prod_no,
     (select b.prod_name from prod b where b.prod_no = max(a.prod_no)) as prod_name
   from request a
   group by a.order_no) v1,
  (select c.order_no, count(*)-1 as count
   from request c join prod d
       on c.order_no = d.prod_no
   group by c.order_no) v2
where v1.order_no = v2.order_no

방법3


select
  order_no,
  case when count=0 then prod_name else prod_name ||' 외' || count || ' 건' end as title
from (
       select v1.order_no,v1.prod_name,v2.count
       from
         (select a.order_no,
            max(a.prod_no) as prod_no,
            (select b.prod_name from prod b where b.prod_no = max(a.prod_no)) as prod_name
          from request a
          group by a.order_no) v1,
         (select c.order_no, count(*)-1 as count
          from request c join prod d
              on c.prod_no = d.prod_no
          group by c.order_no) v2
       where v1.order_no = v2.order_no
     ) as v3;

관련 :

상관관계서브쿼리