Java DB和jasper报告嵌套选择

Question

我正在创建要在 JAVA DB 的 Jasper Netbeans 报告中运行的嵌套选择 View ，并且需要根据以下相同的语句通过在选择内注入(inject) CASE 或使用 来区分分组>if 在 JAVA DB 或任何其他方式中可行，最后我想使用记录集作为源查询来运行 jasper 报告我的代码，如下所示

private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {
 try {
   String host1= "jdbc:derby://localhost:1527//home/mohamed/java- 
   progs/SPaccounting/accountsdb/accountsdb;create=true";
   String uName1="rootuser";
   String uPass1="1234a1234b";
   con1=DriverManager.getConnection(host1,uName1 ,uPass1);
   String sql1="select sum(journal_amount) AS DR ,0 as CR, journal_crname  AS 
   ACCNAME FROM JOURNAL
   GROUP BY journal_CRNAME UNION select 0 AS DR,sum(journal_amount) AS   CR,  
   journal_dbname AS ACCNAME FROM JOURNAL
   GROUP BY journal_DRNAME order by ACCNAME
   ";
   JasperFillManager.fillReport(jasperReport,param1,connect); //add param    
   if   required    
   String jrxmlFileName = "/home/mohamed/java-progs/accounting syst em/     
   JavaAaccountingsys/src/report1.jrxml";
   JasperDesign jasperDesign = JRXmlLoader.load(jrxmlFileName);


   JasperReport jasperReport = JasperCompileManager.compileReport(jasperDesign);
   JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, null,    con1);
   JasperViewer.viewReport(jasperPrint); 
   } catch (JRException e) {
        e.printStackTrace();
   } catch (ClassNotFoundException e) {
        e.printStackTrace();
   } catch (SQLException e) {
    e.printStackTrace();
 }
}

输出为:

ACCNAME     DR       CR
--------    ----    -----
CASH         1,200   0
AMRO BANK    0       500
CASH         0       600
AMRO BANK    700     0

我想对上面的内容进行区分和求和，并从信用 CR 中削减债务 DR 并得到如下报告所示的结果:

ACCNAME     DR       CR
--------    ----    -----
CASH         600     0
AMRO BANK    200     0

请协助更正select语句

Answer 1

是的，我在SO中得到了正确的选择语句，将输出分组到一个嵌套包中，在选择语句下面添加报告工作正常然后只需创建一个子查询即可完成最后一步

SELECT ACCNAME,
CASE WHEN SUM(CREDIT - DEBT) < 0 THEN - SUM(CREDIT - DEBT)
ELSE 0
 END DEBT,
   CASE WHEN SUM(CREDIT - DEBT) > 0 THEN   SUM(CREDIT - DEBT)
        ELSE 0
   END CREDIT
FROM ( ...... ) myTable
GROUP BY ACCNAME ;

因此我找到了正确的道路。