经典SQL

select po.pid as PID,
max(po.ordercode) as ORDERCODE,
max(po.supplierid) as SUPPLIERID,
max(s.name) as SUPPLIERNAME,
max(po.ordertype) as ORDERTYPE,
max(po.orderstatus) as ORDERSTATUS,
max(po.orderdate) as ORDERDATE,
max(po.orderuser) as ORDERUSER,
max(u.name) as ORDERUSERNAME,
(case when count(od.pid)=count(case when od.isdispose=1 then 1 else null end) then 1 else 0 end) as ISFINISHDISPOSE
from t_purchaseorder po
left join t_orderdiff erence od on od.orderid=po.pid
left join t_supplier s on s.pid = po.supplierid
LEFT JOIN T_USER U ON U.PID = PO.ORDERUSER group by po.pid having count(od.pid)>0

 

游标
declare
   appcount int := 0;
   cursor cursor_customer
   is
   select t.clientsid from m_appclientinstalltime t group by t.clientsid having count(1) > 1;
   cursor_row  cursor_customer%rowtype;
begin  
      open cursor_customer;
      fetch cursor_customer into cursor_row;
          while cursor_customer%found loop  
                    
           declare cursor appclient is select * from m_appclientinstalltime t where t.clientsid = cursor_row.clientsid ;
           app_row appclient%rowtype;
             begin
               for app_row in appclient loop
                 select count(*) into appcount from m_appclientinstalltime a where a.clientsid = cursor_row.clientsid;
                 if(appcount = 2 and app_row.INSTALLTYPE is null) then
                   delete m_appclientinstalltime b where b.pid = app_row.pid;
                 end if;
               end loop;
             end;
              
          fetch cursor_customer into cursor_row;
      end loop;    
      close cursor_customer;
end;