代码之家 › 专栏 › 技术社区 › OVO

如何使用联合显示汇总值

union db2 sql

-3

OVO · 技术社区 · 8 年前

我正在使用DB2语法,希望使用 UNION 创建一个附加行以显示汇总值。

而不是这样做,联合的第一部分只是命名列,而来自第二个联合的数据显示出来。

这是第一部分

SELECT 'DRIVER ID' AS DRIVER_ID, 'NAME' AS NAME, 'PUNIT' AS PUNIT, 'PHONE' AS PHONE, 1767 AS TERMINAL_NUMBER, 'DRIVER TYPE' AS DRIVER_TYPE, 
COALESCE((SELECT ROUND(DEC(SUM(CASE WHEN UPDATED_BY IN ('VISTAR','TM4WIN') THEN 1.00 ELSE 0.00 END))/COUNT(UPDATED_BY),2) FROM ODRSTAT, TLORDER L, DRIVER D WHERE DETAIL_LINE_ID = ORDER_ID AND STATUS_CODE IN ('ARRV@SHIP', 'ARRV@CONS', 'DEPT@SHIP', 'DEPT@CONS') 
AND CURRENT_STATUS IN ('EDIBILLED','BILLD') AND L.PICK_UP_DRIVER = D.DRIVER_ID AND 
D.TERMINAL_NUMBER IN (SELECT UNIQUE FIRST_FIELD_INSERT FROM SITE WHERE FAX_PHONE_NUMBER = :DIVISION) AND BILL_DATE BETWEEN CURRENT DATE - 1 MONTH AND CURRENT DATE AND ACTUAL_DELIVERY > ACTUAL_PICKUP +30 SECONDS),0) AVG_STATUS_UPDATE,
ROUND(((SELECT MAX(ODOMETER)-MIN(ODOMETER) FROM ODOHIST O 
WHERE READINGDATE >= CURRENT DATE - 30 DAYS AND O.UNIT_ID IN (SELECT UNIT_ID FROM PUNIT WHERE ACTIVE_WHERE = 'D' AND FLEET_ID IN (SELECT UNIQUE USER5 FROM SITE WHERE FAX_PHONE_NUMBER = :DIVISION)))
/(SELECT NULLIF(SUM(T2.VOL_PFUEL),0) FROM FC_POS T2 INNER JOIN DRIVER D ON T2.DRIVER_ID = D.DRIVER_ID 
WHERE D.TERMINAL_NUMBER IN (SELECT UNIQUE FIRST_FIELD_INSERT FROM SITE WHERE FAX_PHONE_NUMBER = :DIVISION)  AND POS_DATE >= CURRENT DATE - 30 DAYS)),2)  AVG_MPG,
(SELECT ROUND(AVG(TIMES),2) FROM (SELECT DELIVERY_DRIVER, ROUND(AVG(DEC((DAYS(CHECKIN_DATE) - DAYS(ACTUAL_DELIVERY)) *24 + (HOUR(CHECKIN_DATE) - HOUR(ACTUAL_DELIVERY)))/24),2) TIMES
FROM LIST_CHECKIN_AUDIT, LYNX.TLORDER WHERE LYNX.TLORDER.BILL_NUMBER = LIST_CHECKIN_AUDIT.BILL_NUMBER AND BILL_DATE >= CURRENT TIMESTAMP - 1 MONTH
AND LYNX.TLORDER.DOCUMENT_TYPE = 'INVOICE' AND ACTUAL_DELIVERY < CHECKIN_DATE AND ACTUAL_DELIVERY BETWEEN CURRENT TIMESTAMP - 6 MONTHS AND CURRENT TIMESTAMP + 3 DAYS
GROUP BY LYNX.TLORDER.BILL_NUMBER, DELIVERY_DRIVER) AVERAGES WHERE AVERAGES.DELIVERY_DRIVER IN (SELECT DRIVER_ID FROM DRIVER WHERE ACTIVE_IN_DISP = 'True' AND TERMINAL_NUMBER IN (SELECT UNIQUE FIRST_FIELD_INSERT FROM SITE WHERE FAX_PHONE_NUMBER = :DIVISION))) AS AVG_DAYS_TO_SCAN,
CAST(SUM(CASE WHEN VARCHAR(COALESCE((SELECT DATA FROM CUSTOM_DATA WHERE SRC_TABLE_KEY = DRIVER_ID AND CUSTDEF_ID = '50'),'False' ),10) = 'True' THEN 1 ELSE 0 END) AS VARCHAR(10)) AS DRIVE_AXLE,
(SELECT COUNT(UNIQUE A.BILL_NUMBER) FROM LIST_CHECKIN_AUDIT A INNER JOIN TLORDER T ON T.BILL_NUMBER = A.BILL_NUMBER 
WHERE CURRENT_STATUS IN ('EDIBILLED','BILLD') AND COALESCE(PICK_UP_DRIVER,DELIVERY_DRIVER) IN (SELECT DRIVER_ID FROM DRIVER WHERE ACTIVE_IN_DISP = 'True' 
AND TERMINAL_NUMBER IN (SELECT UNIQUE FIRST_FIELD_INSERT FROM SITE WHERE FAX_PHONE_NUMBER = :DIVISION)) AND BILL_DATE >= CURRENT DATE - 1 MONTH AND A.DOCUMENT_TYPE = 'DABL') AS DRIVERAXLE_SUBMISSIONS
FROM DRIVER WHERE ACTIVE_IN_DISP = 'True' 
AND TERMINAL_NUMBER IN (SELECT UNIQUE FIRST_FIELD_INSERT FROM SITE WHERE FAX_PHONE_NUMBER = :DIVISION) 

UNION ALL

SELECT DRIVER_ID, NAME, DEFAULT_PUNIT AS PUNIT, VARCHAR(USER9,12) AS PHONE, TERMINAL_NUMBER, DRIVER_TYPE,
COALESCE((SELECT ROUND(DEC(SUM(CASE WHEN UPDATED_BY IN ('VISTAR','TM4WIN') THEN 1.00 ELSE 0.00 END))/COUNT(UPDATED_BY),2) FROM ODRSTAT, TLORDER L WHERE DETAIL_LINE_ID = ORDER_ID AND STATUS_CODE IN ('ARRV@SHIP', 'ARRV@CONS', 'DEPT@SHIP', 'DEPT@CONS') 
AND CURRENT_STATUS IN ('EDIBILLED','BILLD') AND L.PICK_UP_DRIVER = DRIVER_ID AND BILL_DATE BETWEEN CURRENT DATE - 1 MONTH AND CURRENT DATE AND ACTUAL_DELIVERY > ACTUAL_PICKUP +30 SECONDS),0) AS STATUS_UPDATE_PERCENT,
ROUND(((SELECT MAX(ODOMETER)-MIN(ODOMETER) FROM ODOHIST O 
WHERE READINGDATE >= CURRENT DATE - 30 DAYS AND O.UNIT_ID = DEFAULT_PUNIT)/(SELECT NULLIF(SUM(T2.VOL_PFUEL),0) FROM FC_POS T2 WHERE T2.DRIVER_ID = DRIVER.DRIVER_ID AND POS_DATE >= CURRENT DATE - 30 DAYS)),2) AS MPG_30DAYS,
(SELECT ROUND(AVG(TIMES),2) FROM (SELECT DELIVERY_DRIVER, ROUND(AVG(DEC((DAYS(CHECKIN_DATE) - DAYS(ACTUAL_DELIVERY)) *24 + (HOUR(CHECKIN_DATE) - HOUR(ACTUAL_DELIVERY)))/24),2) TIMES
FROM LIST_CHECKIN_AUDIT, LYNX.TLORDER WHERE LYNX.TLORDER.BILL_NUMBER = LIST_CHECKIN_AUDIT.BILL_NUMBER AND BILL_DATE >= CURRENT TIMESTAMP - 1 MONTH
AND LYNX.TLORDER.DOCUMENT_TYPE = 'INVOICE' AND ACTUAL_DELIVERY < CHECKIN_DATE AND ACTUAL_DELIVERY BETWEEN CURRENT TIMESTAMP - 6 MONTHS AND CURRENT TIMESTAMP + 3 DAYS
GROUP BY LYNX.TLORDER.BILL_NUMBER, DELIVERY_DRIVER) AVERAGES WHERE AVERAGES.DELIVERY_DRIVER = DRIVER.DRIVER_ID)
 AVG_DAYS_TO_SCAN,
VARCHAR(COALESCE((SELECT DATA FROM CUSTOM_DATA WHERE SRC_TABLE_KEY = DRIVER_ID AND CUSTDEF_ID = '50'),'False' ),10) AS DRIVEAXLE, 
(SELECT COUNT(UNIQUE A.BILL_NUMBER) FROM LIST_CHECKIN_AUDIT A INNER JOIN TLORDER T ON T.BILL_NUMBER = A.BILL_NUMBER 
WHERE CURRENT_STATUS IN ('EDIBILLED','BILLD') AND COALESCE(PICK_UP_DRIVER,DELIVERY_DRIVER) = DRIVER_ID AND BILL_DATE >= CURRENT DATE - 1 MONTH AND A.DOCUMENT_TYPE = 'DABL') AS DRIVERAXLE_SUBMISSIONS_1_MONTH
FROM DRIVER WHERE ACTIVE_IN_DISP = 'True' 
AND TERMINAL_NUMBER IN (SELECT UNIQUE FIRST_FIELD_INSERT FROM SITE WHERE FAX_PHONE_NUMBER = :DIVISION)

1 回复 | 直到 8 年前

Charles 8 年前

然而,当我过去使用UNION来提供汇总值时,我添加了额外的列,这些列为我提供了区分细节和总计的方法。这些额外的列也用作 ORDER BY

例子:

select                       
  digits(HCUSNR) as Cust_Num,
  CCUSNM, 
  char(HINVNR),
  hinamt,
-- Control fields                  
  ' ' concat digits(HCMPCL),       
  case                             
    when HTRCDE in ('A', 'E', 'R') 
         then ' LVL3'              
    else  ' LVL1'                  
  end,                             
  HCUSNR, DDVDSC, HDIVSN           
UNION ALL 

select    
    ' ',  
    'Daily Invoice Total',
    ' ',
    sum(hinamt),
-- Control Fields                                     
    ' ' concat digits(HCMPCL), ' LVL2', 0, ' ', HDIVSN
group by            
    HDIVSN, HCMPCL  
--Following order by applies to entire results set  
order by 5,6,7,8,9

但上面是近20年前写的。。。

如果我今天需要这样做,我会使用IBM添加到DB的分组集、汇总和多维数据集功能。

您使用的DB2平台和版本是什么?分组集、汇总和/或多维数据集是选项吗?