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

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,

我想为每个订单添加Line_Number行以给我这个：

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

我使用的是IBM DB2/SQL，所以你花哨的MS-SQL和Oracle语法在这里不起作用:(

Answer 1

ROW_NUMBER（） OLAP 函数是你的朋友...添加到 DB2 for IBM i 的 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

Answer 2

我想出了类似的答案...除了子选择，我认为它大致相同..

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) 

请注意，字段名称略有不同。