Group Wise数据填充

Question

我在报表中工作，希望在集团内不只显示一次订单。

我尝试过使用循环并将值赋给temp变量，然后比较

declare
l_cnt number;
cursor c1 is select * from zz_employees;
begin
dbms_output.put_line ('Order |-' || 'Material | -' || 'Salesperson |');
for i in c1
loop
  l_cnt := i.ordernumber;
  if l_cnt = i.ordernumber then 
        --dbms_output.put_line (l_cnt);
         dbms_output.put_line (l_cnt || '-' || i.materialno || '-' || i.salesperson);
  else
  dbms_output.put_line (i.ordernumber || '-' || i.materialno || '-' || i.salesperson);
  end if;
end loop;
end;

当前表数据:-

1001    9000001 James
1001    9000002 Tom
1001    9000003 Harry
1002    9000004 Voret
1002    9000005 Kzahunar
1003    9000006 Zari
1004    9000007 Neblas
1004    9000008 Anthony

预期结果

1001    9000001 James
        9000002 Tom
        9000003 Harry
1002    9000004 Voret
        9000005 Kzahunar
1003    9000006 Zari
1004    9000007 Neblas
        9000008 Anthony

Answer 1

无论如何，当你打印这个值时，你必须稍微改变一下你的循环：

loop
  if l_cnt = i.ordernumber then 
    dbms_output.put_line ('    -' || i.materialno || '-' || i.salesperson);
  else
    dbms_output.put_line (i.ordernumber || '-' || i.materialno || '-' || i.salesperson);
    l_cnt := i.ordernumber;
  end if;
end loop;

或者，您可以在查询中直接省略这些值：

SELECT CASE WHEN ordernumber  = MAX(ordernumber) over (ORDER BY ROWNUM 
                                                 ROWS BETWEEN 1 preceding AND 1 preceding) 
            THEN NULL 
            ELSE ordernumber  
       END ordernumber 
     , materialno 
     , salesperson
  FROM zz_employees