具有两个相反WHERE条件的左外部联接+子查询

Question

在这个查询中，我想从MySQL数据库中的3个表中获取数据，以Win形式c#在数据网格视图中显示它。我的问题是，我想在联接查询中使用子查询，并且这个子查询包含与主查询中的条件相反的WHERE条件。

以下代码是子查询：

" (SELECT SUM(submittal.priceCurrentWorks) FROM "+database+ "submittal WHERE submittal.addToSummary = 'false') AS 'سعر الحالية'," +
" (SELECT SUM(submittal.requiredQuantity) FROM " + database + "submittal WHERE submittal.addToSummary = 'false') AS 'الأعمال الحالية'," 

以下代码是完整查询(子和主(：

String LeftOuterQuery = "SELECT DISTINCT boq_table.itemNum AS 'رقم البند', boq_table.descriptionOfWork AS 'وصف البند', boq_table.unit AS 'الوحدة', boq_table.contractualQuantity AS 'كمية العقد', boq_table.priceNum AS 'فئة العقد'," + 
" IFNULL(SUM(summary.priceLastWorks), 0) AS 'سعر السابقة' ," +
" IFNULL(SUM(summary.executedQuantLastSummary), 0) AS 'الأعمال السابقة'," +
" (SELECT SUM(submittal.priceCurrentWorks) FROM "+database+ "submittal WHERE submittal.addToSummary = 'false') AS 'سعر الحالية'," +
" (SELECT SUM(submittal.requiredQuantity) FROM " + database + "submittal WHERE submittal.addToSummary = 'false') AS 'الأعمال الحالية'," +
" IFNULL(summary.percentage,0) AS 'النسبة'," +
" IFNULL(SUM(submittal.requiredQuantity),0) +" +
" IFNULL(SUM(summary.executedQuantLastSummary), 0) AS 'إجمالي الأعمال'," +
" IFNULL(SUM(summary.priceLastWorks), 0) +" +
" IFNULL(SUM(submittal.priceCurrentWorks), 0) AS 'إجمالي السعر'" +
" FROM " + database + "boq_table" +
" LEFT OUTER JOIN " + database + "summary ON " + database + "boq_table.itemNum = " + database + "summary.itemNum" +
" LEFT OUTER JOIN " + database + "submittal ON " + database + "boq_table.itemNum = " + database + "submittal.itemNum" +
" WHERE summary.summaryNumber = '"+lastSummaryNumber+ "' AND submittal.addToSummary = 'true'" +
" GROUP BY boq_table.itemNum"+ 
" ORDER BY boq_table.itemNum ";

完整查询返回其在完整/主查询中刚好达到Where条件的结果

" WHERE summary.summaryNumber = '"+lastSummaryNumber+ "' AND submittal.addToSummary = 'true'" 

并忽略子查询中的Where条件。

我需要在子查询中显示实现addToSummary = 'false'的数据AND同时在完整/主查询中实现addToSummary = 'true'

Answer 1

听起来您可能只需要通过删除WHERE submittal.addToSummary = 'false'

我看不到其他选项，原因如下：将首先评估外部查询的WHERE子句。这意味着返回的任何行的submittal.addToSummary都必须等于'true'。之后会对您的子查询进行求值，因此(如果我理解您的架构(子查询正在查找等于'false'的submittal.addToSummary，其中由于外部查询的WHERE子句，只有等于'true'