DB2SQL-添加订单行号和每个订单的总行数

Question

我有一个销售订单标题和详细信息文件，它们结合在一起，为我提供了以下信息：

Order_No  Item 
99998     AA 
99998     AB
99998     AC
99998     AD
99999     AA
99999     AD

使用此SQL，我可以显示每个订单的总行数：

SELECT 
HDR_ORDERNO as OrderNo
DET_ITEM
  (SELECT COUNT(*) 
   FROM HEADER
   WHERE HDR_ORDERNO = DET_ORDERNO) as Total_Lines
FROM
DETAIL LEFT JOIN HEADER
ON (HRD_ORDER = DET_ORDER)
ORDER BY 
HDR_ORDERNO,

我想为每个订单的行_编号添加行，以提供以下信息：

Order_No  Line_Number  Total_Lines  Item 

99998    1             4           AA 
99998    2             4           AB
99998    3             4           AC
99998    4             4           AD
99999    1             2           AA
99999    2             2           AD

我使用IBMDB2/SQL，所以你喜欢的MS-SQL和Oracle语法在这里不起作用：（

Answer 1

我想出了类似的答案...除了子选择，我认为它是一样的...

SELECT    
   ORDER_HED.ORDER_NUM, 
   (SELECT COUNT(*)      
       FROM ORDER_DET  
       WHERE ORDER_HED.ORDER_NUM = ORDER_DET.DETAIL_NUM) as 
   LINE_COUNT,  
       ROW_NUMBER() 
       OVER (PARTITION BY DETAIL_NUM ORDER BY DETAIL_NUM ASC) as 
   ROW_NUMBER,  
   ORDER_DET.DETAIL_SKU,  
   ORDER_DET.DETAIL_QTY, ORDER_DET.DETAIL_PRICE 
FROM ORDER_HED JOIN ORDER_DET  
ON(ORDER_HED.ORDER_NUM = ORDER_DET.DETAIL_NUM) 

注意，字段名有点不同。

Answer 2

行数（）OLAP函数是您的朋友。。。添加到DB2forIBMi的v5r4。。。

with dtl_cnt as (select det_orderno
                        , count(*) as tot_lines 
                 from detail
                 group by det_ordno)
select 
 hdr_orderno
 , row_number() over (partition by hdr_orderno) as Line_no
 , dc.tot_lines
 , d.item
from
  header 
    join dtl_count dc on hdr_orderno = dc.det_orderno
    join detail d on hdr_orderno = d.det_orderno