两个List集合判断取出大集合中不包含小集合的数据， sql加入 in（）条件查询

最新推荐文章于 2023-08-16 01:08:57 发布

艳阳天_.

最新推荐文章于 2023-08-16 01:08:57 发布

阅读量1.2k

点赞数

分类专栏： # sqlServer # EAS和BOS开发专栏

本文链接：https://blog.csdn.net/MZ199290/article/details/105514009

版权

EAS和BOS开发专栏同时被 2 个专栏收录

159 篇文章 17 订阅

订阅专栏

sqlServer

21 篇文章 0 订阅

订阅专栏

	//记录结算单中没在付款单中出现的合同号（完全没有承担的亏损）--------------------mz---------------------
		String x2="SELECT a.CFCONTRACTNUMBER 合同号,sum(a.FACTUALAMT) 已承担亏损 FROM T_CAS_ReceivingBillEntry a left join T_CAS_ReceivingBill b ON A.FReceivingBillID = B.FID left join T_BD_Customer c on b.FPAYERID=c.fid where c.fname_l2 ='"+this.prmtcust.getValue().toString()+"' and a.FCONTRACTENTRYSEQ=1 group by a.CFCONTRACTNUMBER";
		IRowSet rsx2 = SQLExecutorFactory.getRemoteInstance(x2).executeSQL();   
		ArrayList<String> yfcontractList = new  ArrayList<String>();
		while(rsx2.next()){
			yfcontractList.add(rsx2.getString("合同号"))	; //应付单里面承担的亏损的合同号
		}
		//循环遍历取出不在付款单集合中的该养户的结算单中的合同号
		jscontractList.removeAll(yfcontractList); //差异集合， 
	 
		StringBuffer  contacts=new StringBuffer();
        for (int j = 0; j < jscontractList.size(); j++) {
        	contacts.append("'" + jscontractList.get(j) + "',");
		}
          String result = contacts.toString();
          if(!result.equals("")){
        	  String tp = result.substring(result.length() - 1, result.length());
    		  result=result.substring(0, result.length() - 1);
            String mz="select d.fnumber 合同号,a.CFCURRENTBALANCE 亏损金额,a.CFCUSTINFO 信息 from CT_DUC_CalculateStatement a left join T_DUC_BreedingContract d on a.CFCONTRACTNOID=d.fid   left join T_BD_Customer c on a.CFCUSTID=c.fid where c.fname_l2 ='"+this.prmtcust.getValue().toString()+"' and a.CFCURRENTBALANCE<0 and d.fnumber  in (  "+result+"  )  ";
    		IRowSet rsmz = SQLExecutorFactory.getRemoteInstance(mz).executeSQL();
            while(rsmz.next()){
            	IRow irow = this.kdtE2.addRow();
    			irow.getCell("conNo").setValue(rsmz.getString("合同号"));
    			irow.getCell("materiel").setValue("亏损");
    			irow.getCell("qty").setValue("1");
    			irow.getCell("measurementUnit").setValue("笔");
    			irow.getCell("amount").setValue(rsmz.getDouble("亏损金额")*(-1));
            } 
          }