时间戳

首页 > 解决方案 > 具有两个相反 WHERE 条件的左外连接 + 子查询

c# - 具有两个相反 WHERE 条件的左外连接 + 子查询

问题描述

在这个查询中，我想从 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'在子查询 中实现并addToSummary = 'true'在完整/主查询中实现的数据。

标签： c#mysql

解决方案

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

我没有看到其他选项，原因如下：WHERE将首先评估外部查询的子句。这意味着返回的任何行都必须submittal.addToSummary等于'true'。之后评估您的子查询，因此（如果我理解您的架构）子查询正在寻找submittal.addToSummary等于“假”的地方，由于外部查询的子句，只有submittal.addToSummary等于“真”的值。WHERE

推荐阅读