Select 多字段统计子查询

最新推荐文章于 2024-05-11 15:43:46 发布

原创最新推荐文章于 2024-05-11 15:43:46 发布 · 1.8k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#hashmap #string #list #sql #c

DataBase 专栏收录该内容

2 篇文章

订阅专栏

本文介绍了一个用于查询特定客户在不同业务流程中状态数量的方法。通过构造复杂的SQL查询语句，该方法能够从多个表中获取并汇总数据，包括报关、开票、退运等业务流程的状态统计。

public static Map searchOtherInfoByCustomId(GenericDelegator delegator,String customId,StringBuffer sqlCommletter){ SQLProcessor sqlP=new SQLProcessor(delegator.getEntityHelperName("CustomsRelationList")); StringBuffer sql=new StringBuffer(); sql.append(" SELECT ("); sql.append(" SELECT count(*) FROM GW_PDE_HEAD wph,GW_PDE_LIST_ORG gplo WHERE wph.ID=gplo.HEAD_ID "); sql.append(" AND wph.head_Status='customsStatusNew' AND gplo.COMM_LETTER_ID IN "); sql.append(sqlCommletter); sql.append(" AND dc.CUSTOMER_ID='"+customId+"') "); sql.append(") as bg,");//报关 sql.append(" (SELECT count(distinct fi.COMM_LETTER_ID) FROM FEE_item fi ,FEE_CONFIRM_ITEM fci,Work_Invoice_Info wii "); sql.append(" WHERE fi.FEE_C_ITEM_ID=fci.ID AND wii.FEE_ID= fci.ID "); sql.append(" and fi.COMM_LETTER_ID in "); sql.append(sqlCommletter); sql.append(" AND dc.CUSTOMER_ID='"+customId+"') "); sql.append(") as kp,");//开票 sql.append(" (SELECT count(*) FROM Gw_Back_Bill gbl WHERE gbl.COMM_LETTER_ID IN"); sql.append(sqlCommletter); sql.append(" AND dc.CUSTOMER_ID='"+customId+"') "); sql.append(" ) AS ty,");//退运 sql.append(" (SELECT count(*) FROM GW_XZONE_MOVE WHERE COMM_LETTER_ID IN"); sql.append(sqlCommletter); sql.append(" AND dc.CUSTOMER_ID='"+customId+"') "); sql.append(" ) AS qj,");//区间 sql.append(" (SELECT count(*) FROM GW_Move_Bill WHERE COMM_LETTER_ID IN"); sql.append(sqlCommletter); sql.append(" AND dc.CUSTOMER_ID='"+customId+"') "); sql.append(" ) AS qn,");//区内 sql.append(" (SELECT count(*) FROM GW_Delivery_Out WHERE COMM_LETTER_ID IN"); sql.append(sqlCommletter); sql.append(" AND dc.CUSTOMER_ID='"+customId+"') "); sql.append(" ) AS cq,");//出区 sql.append(" (SELECT count(*) FROM GW_Remove_Bill WHERE COMM_LETTER_ID IN"); sql.append(sqlCommletter); sql.append(" AND dc.CUSTOMER_ID='"+customId+"') "); sql.append(" ) AS fd");//反调拨单 Map resultsMap=new HashMap(); try { sqlP.prepareStatement(sql.toString()); System.out.println("searchOtherInfoByCustomId"+sql.toString()); ResultSet rs=sqlP.executeQuery(); if(rs.next()){ resultsMap.put("bg", rs.getString("bg")); resultsMap.put("ty", rs.getString("ty")); resultsMap.put("qj", rs.getString("qj")); resultsMap.put("qn", rs.getString("qn")); resultsMap.put("cq", rs.getString("cq")); resultsMap.put("fd", rs.getString("fd")); resultsMap.put("kp", rs.getString("kp")); } } catch (GenericDataSourceException e) { e.printStackTrace(); } catch (GenericEntityException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } return resultsMap; }