DB2 SQL — добавить номер строки заказа и общее количество строк в заказе

У меня есть заголовок заказа на продажу и файл сведений, которые объединяются, чтобы дать мне это:

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 здесь не работает :(


sql join db2 db2-400
person Mustapha George    schedule 07.03.2016    source источник

Ответы (2)


arrow_upward
2
arrow_downward

OLAP-функция ROW_NUMBER() — ваш друг... добавлена ​​в DB2 для 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
person Charles    schedule 07.03.2016
comment
Синтаксическая ошибка, потому что CTE не имеет GROUP BY для агрегатной функции. - person Clockwork-Muse; 07.03.2016
comment
Блин... вот что я получаю за попытку ответить на вопрос перед сном. Ответ отсутствует, FROM также является CTE. Я добавил оба. - person Charles; 07.03.2016
comment
Блин... вот что я получаю за попытку проверить ответ на вопрос перед сном. Не могу поверить, что я пропустил упоминание FROM. - person Clockwork-Muse; 07.03.2016

arrow_upward
0
arrow_downward

Я придумал аналогичный ответ... Кроме подвыборки, я думаю, что это примерно то же самое..

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)

Обратите внимание, имена полей немного отличаются.

person Mustapha George    schedule 07.03.2016
comment
Единственная проблема заключается в том, что подвыборка, вероятно, выполняется для каждой строки, что может быть проблемой производительности. Выполнение этого как CTE заставит его запуститься один раз. О, разделение и упорядочение по одному и тому же столбцу не будут иметь никакого эффекта: ROW_NUMBER перезапускается каждый раз при изменении раздела, поэтому каждый другой DETAIL_NUM в любом случае начинается с 1. - person Clockwork-Muse; 07.03.2016