公司内部SQL联合查询

最新推荐文章于 2024-04-20 03:33:44 发布

ole_triangle_java

最新推荐文章于 2024-04-20 03:33:44 发布

阅读量641

点赞数

分类专栏：公司内部用

本文链接：https://blog.csdn.net/ole_triangle_java/article/details/53810417

版权

公司内部用专栏收录该内容

28 篇文章 0 订阅

订阅专栏

String prodTableId = assignWorkTB.get(0).getCellBeanValue("CR_PROD_TABLE_ID");
ClassPOJO classPOJO = new ClassPOJO("TN_PROD_ASSIGN_WORK a INNER JOIN TN_PROD_ASSIGN_WORK_OPERATION b ON a.CN_ID = b.CR_PROD_ASSIGN_WORK_ID " +
	"INNER JOIN TN_ROUTE_TREE_OPERATION c ON b.CR_ROUTE_TREE_OPERATION_ID = c.CN_ID INNER JOIN TN_RECEIPTS_LIST_LINE d ON b.CN_ID = d.CR_ORDER_LINE_ID " +
	"INNER JOIN TN_IQC_LINE e ON d.CN_ID = e.CR_SOURCE_LINE_ID INNER JOIN TN_INSTOCK_LINE f ON e.CN_ID = f.CR_IQC_LINE_ID");
classPOJO.addAttribute(new ClassAttributePOJO("c.CN_ID AS wipId, f.CN_INSTOCK_QTY AS instockQty"));
CondSetBean condSetBean = new CondSetBeanJustAnd();
condSetBean.addCondBean(new CondBeanEqual("a.CR_PROD_TABLE_ID", prodTableId));
CondBeanIn cbi = new CondBeanIn("b.CR_ROUTE_TREE_OPERATION_ID");
condSetBean.addCondBean(cbi);
for (int i = 0; i < wipTB.size(); i++) {
	cbi.addValue(wipTB.get(i).getCellBeanValue(TriangleDefinition.COLUMN_NAME_CN_ID));
}
TableBean newWipTB = this.baseDAO.queryForTableBean(classPOJO, condSetBean);

另外一种

if (matrixTableBean != null && matrixTableBean.size() > 0) {
					dataBean.addFormBean(matrixDataBean.getFormBean(TriangleDefinition.FORM_BEAN_KEY_SPLIT_PAGE_INFO));
					// 读取交易记录的定性|Domain|Relation|Exception
					CondSetBean csbAttribute = new CondSetBeanJustAnd();
					CondBeanIn cbiMatrixIdForAttr = new CondBeanIn("CR_TRADING_RECORD_MATRIX_ID");
					csbAttribute.addCondBean(cbiMatrixIdForAttr);
					StringBuffer inMatrixSQL = new StringBuffer();
					for (int i = 0; i < matrixTableBean.size(); i++) {
						RowBean rowBean = matrixTableBean.get(i);
						String matrixId = rowBean.getCellBeanValue(TriangleDefinition.COLUMN_NAME_CN_ID);
						cbiMatrixIdForAttr.addValue(matrixId);
						if (i > 0) {
							inMatrixSQL.append(",");
						}
						inMatrixSQL.append("'" + matrixId + "'");
					}
					// 读取属性要加入排序.以便后续生成数据Table的时候有固定的顺序
					OrderBy obAttribute = new OrderBy();
					obAttribute.addSort(new Sort("CR_ATTRIBUTE_TYPE_ID"));
					obAttribute.addSort(new Sort("CN_ATTRIBUTE_ID"));
					// Domain
					TableBean domainTableBean = this.baseDAO.queryForTableBean(new ClassPOJO(domainTableName), csbAttribute, obAttribute);
					// 定性
					TableBean qualitativeTableBean = this.baseDAO.queryForTableBean(new ClassPOJO(qualitativeTableName), csbAttribute, obAttribute);
					// 异常数量

					ClassPOJO countExceptionClassPOJO = new ClassPOJO(hmAccountInfo.get("public_RecordDBTableName"));
					countExceptionClassPOJO.addAttribute(new ClassAttributePOJO("CR_TRADING_RECORD_MATRIX_ID"));
					countExceptionClassPOJO.addAttribute(new ClassAttributePOJO("COUNT(CN_ID)"));
					StringBuffer countExceptionSQL = new StringBuffer();
					countExceptionSQL.append("SELECT CR_TRADING_RECORD_MATRIX_ID AS matrixId,COUNT(CN_ID) AS number FROM ");
					countExceptionSQL.append(hmAccountInfo.get("public_RecordDBTableName"));
					countExceptionSQL.append(" WHERE CR_TRADING_RECORD_MATRIX_ID IN (");
					countExceptionSQL.append(inMatrixSQL);
					countExceptionSQL.append(") GROUP BY CR_TRADING_RECORD_MATRIX_ID");
					TableBean exceptionTableBean = this.baseDAO.queryForTableBeanOnSQL(countExceptionSQL.toString());