상품번호 한개에 한줄

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;

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

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;