我正在发布一些尝试将三个表连接在一起时输出中出现多个重复值的信息。我的数据结构如下:
T1(交易记录表)
Trans_ID acc_No Trans_Code Trans_Amt
6243 11111111 0690 35.0
4534 11111111 0876 10.0
5987 22222222 0690 -10.0
T2(翻译表)
acc_No Prod_Code
11111111 45
22222222 65
T3(产品表)
Prod_Code Prod_Desc
45 Current account
65 Credit Card
翻译表(T2)允许通过交易(T1)进行组合,以查找该帐户是哪种产品(T3)。
我当前的尝试如下所示。该查询试图获取所有事务(T1),并计算按产品分组的不同类型(Trans_Type)中所有事务的总和和计数。 CASE WHEN将正值和负值分开。
SELECT T1.TRANS_CODE,T3.PROD_DESC,SUM (CASE WHEN T1.TRANS_AMT < 0 THEN T1.TRANS_AMT ELSE 0 END) AS SUM_NEGATIVE,COUNT(CASE WHEN T1.TRANS_AMT < 0 THEN T1.TRANS_AMT END) AS COUNT_NEGATIVE,SUM (CASE WHEN T1.TRANS_AMT > 0 THEN T1.TRANS_AMT ELSE 0 END) AS SUM_POSTIIVE,COUNT(CASE WHEN T1.TRANS_AMT > 0 THEN T1.TRANS_AMT END) AS COUNT_POSITIVE
FROM T1
LEFT JOIN T2
ON T1.acc_NO = T2.acc_NO
LEFT JOIN T3
ON T2.PROD_CODE = T3.PROD_CODE
GROUP BY T1.TRANS_CODE,T3.PROD_DESC
此处的预期输出为:
Trans_Type Prod_Desc Sum_Positive Count_Positive Sum_Negative Count_Negative
0690 Current account 35.0 1 - -
0690 Credit Card - - -10.0 1
0876 Current account 10.0 1 - -
但是我的总和计数超出了我所知道的真实范围。
任何帮助都将不胜感激。
更新代码
SELECT T1.TRANS_CDE,COUNT(CASE WHEN T1.TRANS_AMT > 0 THEN T1.TRANS_AMT END) AS COUNT_POSITIVE
FROM T1
LEFT JOIN (SELECT T2.acc_NO,MIN(PROD_CODE) AS PROD_CODE
FROM T2
GROUP BY acc_NO) T2
ON T1.acc_NO = T2.acc_NO
LEFT JOIN T3
ON T2.PROD_CODE = T3.PROD_CODE
GROUP BY T1.TRAN_CDE,T3.PROD_DESC