我正在与Crystal Report一起工作,以拉出与这些费用相关的费用和交易。一个费用可能附带许多事务,一个事务可能包含许多费用。当我把这个报告放在一起时,如果在收入代码组中有许多收费,那么交易将重复自己。如果一次收费有很多交易,收费就会自我复制。我还必须为这些费用和交易做小计和总计,所以如果数据中有重复,总数也会增加如何删除这些重复项?
下面是这个报告的SQL查询:
SELECT DISTINCT
A123Test_ChargeOnly."enc_nbr",
A123Test_ChargeOnly."revenue_code_id",A123Test_ChargeOnly."Charge",
A123Test_TransOnly."Tran_Description", A123Test_TransOnly."tran_amt"
FROM
{ oj "NGProd"."dbo"."123Test_ChargeOnly" A123Test_ChargeOnly
INNER JOIN "NGProd"."dbo"."123Test_TransOnly" A123Test_TransOnly ON
A123Test_ChargeOnly."enc_nbr" = A123Test_TransOnly."enc_nbr"}
ORDER BY
A123Test_ChargeOnly."enc_nbr" ASC,
A123Test_ChargeOnly."revenue_code_id" ASC
与其将两个表连接起来,不如将它们联合起来——类似于:
SELECT 'Charge' record_type, enc_nbr, revenue_code_id, Charge, '' Tran_Description, 0 tran_amt
FROM "NGProd"."dbo"."123Test_ChargeOnly"
UNION ALL
SELECT 'Transaction' record_type, enc_nbr, '' revenue_code_id, 0 Charge, Tran_Description, tran_amt
FROM "NGProd"."dbo"."123Test_TransOnly"
ORDER BY 2, 1, 3
据我所知,在Crystal Reports的专家....的'sections'中有一个特定的'suppress if duplicate'选项