这是3个独立的数据表。
表Adj_Orig
ID LINE_NUM DOLLAR_AMT
12 3 $3.41
表Adj_Reversal
ID LINE_NUM DOLLAR_AMT
12 3 ($3.41)
表Adj_Retro
ID LINE_NUM DOLLAR_AMT
12 3 $0.00
12 3 $1.90
12 3 $1.42
12 3 $0.09
下面的sql语句给出了如下结果:
SELECT orig.ID AS ID1,
orig.LINE_NUM AS ID1_LINE,
reversal.ID AS ID2,
reversal.LINE_NUM AS ID2_LINE,
retro.ID AS ID3,
retro.LINE_NUM AS ID3_LINE
FROM schema.Adj_Orig orig
LEFT OUTER JOIN schema.Adj_Reversal reversal ON (reversal.ID = orig.ID
AND reversal.LINE_NUM = orig.LINE_NUM)
LEFT OUTER JOIN schema.Adj_retro ON (retro.ID = orig.ID
AND retro.LINE_NUM = orig.LINE_NUM)
WHERE orig.ID = '12'
ID1 ID1_LINE ID2 ID2_LINE ID3 ID3_LINE ORIG_AMT REVERSAL_AMT RETRO_AMT
12 3 12 3 12 3 $3.41 ($3.41) $0.00
12 3 12 3 12 3 $3.41 ($3.41) $1.90
12 3 12 3 12 3 $3.41 ($3.41) $1.42
12 3 12 3 12 3 $3.41 ($3.41) $0.09
我有这个sql与sum函数给我下面的结果。
SELECT
orig.ID AS ID1,
orig.LINE_NUM AS ID1_LINE,
reversal.ID AS ID2,
reversal.LINE_NUM AS ID2_LINE,
retro.ID AS ID3,
retro.LINE_NUM AS ID3_LINE,
SUM(orig.DOLLAR_AMT) AS ORIG_AMT,
SUM(reversal.DOLLAR_AMT) AS REVERSAL_AMT,
SUM(retro.DOLLAR_AMT) AS RETRO_AMT -- Ok
FROM schema.Adj_Orig orig
LEFT OUTER JOIN schema.Adj_Reversal reversal ON (reversal.ID = orig.ID
AND reversal.LINE_NUM = orig.LINE_NUM)
LEFT OUTER JOIN schema.Adj_retro ON (retro.ID = orig.ID
AND retro.LINE_NUM = orig.LINE_NUM)
WHERE orig.ID = '12'
GROUP BY 1,2,3,4,5,6
;
ID1 ID1_LINE ID2 ID2_LINE ID3 ID3_LINE ORIG_AMT REVERSAL_AMT RETRO_AMT
12 3 12 3 12 3 $13.64 ($13.64) $3.41
我需要sql给我以下结果。
ID1 ID1_LINE ID2 ID2_LINE ID3 ID3_LINE ORIG_AMT REVERSAL_AMT RETRO_AMT
12 3 12 3 12 3 $3.41 ($3.41) $3.41
如何获得ORIG_AMT &reveral_amt只计算3.41值一次。
Thansk !
我尝试了OVER (PARTITION BY)，但它给了我相同的值。
从orig_amt和reversal_amt列中删除SUM():
SELECT
orig.ID AS ID1,
orig.LINE_NUM AS ID1_LINE,
reversal.ID AS ID2,
reversal.LINE_NUM AS ID2_LINE,
retro.ID AS ID3,
retro.LINE_NUM AS ID3_LINE,
orig.DOLLAR_AMT AS ORIG_AMT,
reversal.DOLLAR_AMT AS REVERSAL_AMT,
SUM(retro.DOLLAR_AMT) AS RETRO_AMT -- Ok
FROM schema.Adj_Orig orig
LEFT OUTER JOIN schema.Adj_Reversal reversal ON (reversal.ID = orig.ID
AND reversal.LINE_NUM = orig.LINE_NUM)
LEFT OUTER JOIN schema.Adj_retro ON (retro.ID = orig.ID
AND retro.LINE_NUM = orig.LINE_NUM)
WHERE orig.ID = '12'
GROUP BY 1,2,3,4,5,6,7,8
对于您的评论:只需将此放入子查询并再次求和:
SELECT ID1, ID1_LINE, ID2, ID2_LINE, ID3, ID3_LINE, SUM(ORIG_AMT) as ORIG_AMT, SUM(REVERSAL_AMT) as REVERSAL_AMT, SUM(RETRO_AMT) as RETRO_AMT
FROM
(
SELECT
orig.ID AS ID1,
orig.LINE_NUM AS ID1_LINE,
reversal.ID AS ID2,
reversal.LINE_NUM AS ID2_LINE,
retro.ID AS ID3,
retro.LINE_NUM AS ID3_LINE,
orig.DOLLAR_AMT AS ORIG_AMT,
reversal.DOLLAR_AMT AS REVERSAL_AMT,
SUM(retro.DOLLAR_AMT) AS RETRO_AMT -- Ok
FROM schema.Adj_Orig orig
LEFT OUTER JOIN schema.Adj_Reversal reversal ON (reversal.ID = orig.ID
AND reversal.LINE_NUM = orig.LINE_NUM)
LEFT OUTER JOIN schema.Adj_retro ON (retro.ID = orig.ID
AND retro.LINE_NUM = orig.LINE_NUM)
WHERE orig.ID = '12'
GROUP BY 1,2,3,4,5,6,7,8
) dt
GROUP BY 1,2,3,4,5