mysql - mysql 查询中的 SUM 在 JOIN 2 表中不明确

Question

我在查询中进行的 SUM 有问题，如下所示:

编辑我用英语发布我的查询，以便每个人都明白我想说的:

    $sql = "SELECT 
       c.stock,
       c.id as cid,
       cb.course as ccourse,
       cb.price_member as cbprice_member,
       cb.price_not_member as cbprice_not_member,
       cb.study as cbstudy,
       cb.studentid,


          (SELECT 
              SUM(CASE WHEN c.stock > 0 THEN price_member ELSE 0 END) AS subtotal_member,
              c.stock 
           FROM 
              courses_orders cb
           JOIN 
              courses c 
           ON 
              cb.course_id = c.id 
           WHERE 
              cb.date_removed IS NULL AND 
              cb.date_order_mail IS NOT NULL AND
              cb.date_pickup IS NULL AND 
              cb.date_pickup_mail IS NULL AND 
              cb.studentid = '$studentid' AND 
              cb.course_id = '$cid' AND 
              c.stock > 0
          ) as subtotal_member,


       (SELECT SUM(price_not_member) FROM courses_orders WHERE date_removed IS NULL AND date_order_mail IS NOT NULL AND date_pickup IS NULL AND date_pickup_mail IS NULL AND studentid = '$studentid' AND course_id = '$cid') as subtotal_not_member
     FROM 
       courses c
     JOIN
       courses_orders cb
     ON
       cb.course_id = c.id
     WHERE 
       c.id = '$cid' AND cb.date_removed IS NULL AND cb.date_pickup IS NOT NULL AND cb.date_pickup_mail IS NULL AND cb.studentid = '$studentid'
     ";

所以，问题是 SUM(CASE WHEN c.stock > 0 THEN Price_member ELSE 0 END) AS subtotal_member我有两次 subtotal_member 。我认为这也是不正确的。如果我设置 cb.price_member 我有错误:操作数应该包含 1 列，如果我设置 Price_member (之前没有 cb.)我有错误:price_member 不明确。我想要在这里做的是获得所有商品的总价，不包括库存低于 1 的商品。所以我在这里取 c.voorraad > 0 但结果始终是所有商品的总价，而不仅仅是那些库存低于 1 的商品的总价。库存高于 0。

这是原始查询(包含荷兰语项目)，与上面的查询相同，但字段不同。因此，如果您已阅读 EN 版本，请忽略此内容。

         $sql = "SELECT 
               c.voorraad,
               c.id as cid,
               cb.artikel as cbartikel,
               cb.prijs_lid as cbprijs_lid,
               cb.prijs_niet_lid as cbprijs_niet_lid,
               cb.studierichting as cbstudierichting,
               cb.studentid,


                  (SELECT 
                      SUM(CASE WHEN c.voorraad > 0 THEN prijs_lid ELSE 0 END) AS subtotaal_lid,
                      c.voorraad 
                   FROM 
                      cursusdienst_bestellingen cb
                   JOIN 
                      cursusdienst c 
                   ON 
                      cb.cursus_id = c.id 
                   WHERE 
                      datum_verwijderd IS NULL AND 
                      datum_reservatie_mail IS NOT NULL AND
                      datum_afhaling IS NULL AND 
                      datum_afhaling_mail IS NULL AND 
                      studentid = '$studentid' AND 
                      cursus_id = '$cid' AND 
                      c.voorraad > 0
                  ) as subtotaal_lid,


               (SELECT SUM(prijs_niet_lid) FROM cursusdienst_bestellingen WHERE datum_verwijderd IS NULL AND datum_reservatie_mail IS NOT NULL AND datum_afhaling IS NULL AND datum_afhaling_mail IS NULL AND studentid = '$studentid' AND cursus_id = '$cid') as subtotaal_niet_lid
             FROM 
               cursusdienst c
             JOIN
               cursusdienst_bestellingen cb
             ON
               cb.cursus_id = c.id
             WHERE 
               c.id = '$cid' AND cb.datum_verwijderd IS NULL AND cb.datum_afhaling IS NOT NULL AND cb.datum_afhaling_mail IS NULL AND cb.studentid = '$studentid'
             ";

问题是 SUM(CASE WHEN c.voorraad > 0 THEN prijs_lid ELSE 0 END) AS subtotaal_lid我有两次 subtotaal_lid 。我认为这也是不正确的。如果我设置 cb.prijs_lid 我有错误:操作数应该包含 1 列，如果我设置 prijs_lid (之前没有 cb.)我有错误:prijs_lid 不明确。

我想要在这里做的是获得所有商品的总价，不包括库存低于 1 的商品。所以我在这里取 c.voorraad > 0 但结果始终是所有商品的总价，而不仅仅是股票高于 0 的人。

编辑表 cursusdienst 包含以下字段(例如):

id   prijs_lid   prijs_niet_lid   artikel  voorraad
1    24.00       25.00            Course1  12
2    30.00       35.00            Course2  -10

表 cursusdienst_bestellingen 包含以下字段(例如):

id   cursus_id   prijs_lid   prijs_niet_lid   artikel  studentid
1    1           24.00       25.00            Course1  123456789
2    2           30.00       35.00            Course2  123456789

表中的输出(发票) - 主查询为我提供了正确的输出:

Artikel   Aantal   Prijs
Course1   1        24.00

在输出表下方是总价表 - 子查询是否出错:

Subtotal: 24.00
Tax: 1.20
Total: 25.20

所以这里的总和totalprice_member(=荷兰语的totaalprijs_lid)对于成员(member)来说是24.00(荷兰语的lid)，而不是成员(member)的54.00，因为course2的库存低于1。现在我的totaalprijs_lid是54.00错误...

总代码:

        <table cellpadding="0" cellspacing="0" width="600" class="w320">
            <tr>
              <td class="item-table">
                <table cellspacing="0" cellpadding="0" width="100%">
                  <tr>
                    <td class="title-dark" width="300">
                      Cursus
                    </td>
                    <td class="title-dark" width="163">
                      Aantal
                    </td>
                    <td class="title-dark" width="97">
                      Totaal
                    </td>
                  </tr>

<?php
    if (!empty($_POST['bachelor1'])) {
    foreach ($cursus as $cid) {

         $sql = "
select 
  c.voorraad,
  c.id as cid,
  cb.artikel as cbartikel,
  cb.prijs_lid as cbprijs_lid,
  cb.prijs_niet_lid as cbprijs_niet_lid,
  cb.studierichting as cbstudierichting,
  cb.studentid,
  case when c.voorraad > 0 then
    (
      select 
        sum(prijs_lid)
       from cursusdienst_bestellingen cbx
       where cbx.cursus_id = cb.cursus_id
         and cbx.studentid = cb.studentid
         and cbx.datum_afhaling is null
         and cbx.datum_afhaling_mail is null
         and cbx.datum_reservatie_mail is not null
         and cbx.datum_verwijderd is null
    )
  else 0 end as subtotaal_lid,
  case when c.voorraad > 0 then
    (
      select 
        sum(prijs_niet_lid)
       from cursusdienst_bestellingen cbx
       where cbx.cursus_id = cb.cursus_id
         and cbx.studentid = cb.studentid
         and cbx.datum_afhaling is null
         and cbx.datum_afhaling_mail is null
         and cbx.datum_reservatie_mail is not null
         and cbx.datum_verwijderd is null
    )
  else 0 end as subtotaal_niet_lid
from cursusdienst c
join cursusdienst_bestellingen cb on cb.cursus_id = c.id
where cb.datum_afhaling is not null 
  and cb.datum_afhaling_mail is null 
  and cb.datum_verwijderd is null 
  and cb.studentid = '$studentid'
  and c.id = '$cid'
                 ";
         $res = mysql_query($sql) or die (mysql_error());

         //$subtotaal1 = '';
         //$totaal1 = '';
         //$btw1 = '';

         while($row = mysql_fetch_assoc($res))
            { 
               $cursus_id1 = $row['cid'];
               $studierichting1 = $row['cbstudierichting'];
               $voorraad1 = $row['voorraad'];

               if ($num_rows_lid > 0) {
                   $prijs1 = round(number_format(($row['cbprijs_lid'] / 1.21), 2, '.', ''), 2);
               } else {
                   $prijs1 = round(number_format(($row['cbprijs_niet_lid'] / 1.21), 2, '.', ''), 2);
               }

               $artikel1 = $row['cbartikel'];
               $aantal1 = '1';

               $subtotaal_lid += number_format(round(($row['subtotaal_lid'] / 1.21), 2), 2, '.', '');
               $totaal_lid += number_format($row['subtotaal_lid'], 2, '.', '');
               $btw_lid = round(number_format(($totaal_lid - $subtotaal_lid), 2, '.', ''), 2);

               $subtotaal_niet_lid += number_format(round(($row['subtotaal_niet_lid'] / 1.21), 2), 2, '.', '');
               $totaal_niet_lid += number_format($row['subtotaal_niet_lid'], 2, '.', '');
               $btw_niet_lid = round(number_format(($totaal_niet_lid - $subtotaal_niet_lid), 2, '.', ''), 2);

?>
                  <tr>
                    <td class="item-col item">
                      <table cellspacing="0" cellpadding="0" width="100%">
                        <tr>
                          <td class="product">
                            <span style="color: #4d4d4d; font-weight:bold;"><?php echo wordwrap($artikel1, 20, "<br />\n"); ?></span>
                          </td>
                        </tr>
                      </table>
                    </td>
                    <td class="item-col quantity aantal">
                      <?php echo $aantal1; ?>
                    </td>
                    <td class="item-col">
                      <?php echo '€ '.($prijs1 * $aantal1); ?>
                    </td>
                  </tr>



<?php
         //$sql = "UPDATE cursusdienst_bestellingen SET datum_afhaling_mail = NOW() WHERE cursus_id = '$cursus_id1' AND datum_verwijderd IS NULL AND studentid = '$studentid'";
         //$res = mysql_query($sql) or die (mysql_error()); 

 } } } ?>


<!--
                  <tr>
                    <td class="item-col item">
                      <table cellspacing="0" cellpadding="0" width="100%">
                        <tr>
                          <td class="product">
                            <span style="color: #4d4d4d; font-weight: bold;">Pink Shoes</span> <br />
                            Newest styles
                          </td>
                        </tr>
                      </table>
                    </td>
                    <td class="item-col quantity aantal">
                      1
                    </td>
                    <td class="item-col price">
                      $10.50
                    </td>
                  </tr>
-->

                  <tr>
                    <td class="item-col item mobile-row-padding"></td>
                    <td class="item-col quantity"></td>
                    <td class="item-col price"></td>
                  </tr>

<?php
         if($num_rows_lid > 0) {
             $subtotaal = $subtotaal_lid;
             $btw = $btw_lid;
             $totaal = $totaal_lid;
         } else {
             $subtotaal = $subtotaal_niet_lid;
             $btw = $btw_niet_lid;
             $totaal = $totaal_niet_lid;
         }
?>
                  <tr>
                    <td class="item-col item">
                    </td>
                    <td class="item-col quantity" style="text-align:right; padding-right: 10px; border-top: 1px solid #cccccc;">
                      <span class="total-space">Subtotaal</span> <br />
                      <span class="total-space">BTW</span>  <br />
                      <span class="total-space" style="font-weight: bold; color: #4d4d4d">Totaal</span>
                    </td>
                    <td class="item-col price" style="text-align: left; border-top: 1px solid #cccccc;">
                      <span class="total-space"><?php echo '€ '.$subtotaal; ?></span> <br />
                      <span class="total-space"><?php echo '€ '.$btw; ?></span>  <br />
                      <span class="total-space" style="font-weight:bold; color: #4d4d4d"><?php echo '€ '.$totaal; ?></span>
                    </td>
                </table>
              </td>
            </tr>
        </table>

Answer 1

此代码返回不明确的列错误:

SUM(CASE WHEN c.voorraad > 0 THEN prijs_lid ELSE 0 END) AS subtotaal_lid,

此代码返回“操作数应包含一个值”:

SUM(CASE WHEN c.voorraad > 0 THEN cb.prijs_lid ELSE 0 END) AS subtotaal_lid,

此版本修复了之前的错误。操作数错误位于查询的其他位置。至少有一个问题是这个子查询:

(SELECT SUM(CASE WHEN c.voorraad > 0 THEN prijs_lid ELSE 0 END) AS subtotaal_lid,
        c.voorraad 
  . . . 
 )

您在预期具有单个值的上下文中使用子查询。这需要一个标量子查询，它返回一列和最多一行。您可能打算:

(SELECT SUM(CASE WHEN c.voorraad > 0 THEN prijs_lid ELSE 0 END) AS subtotaal_lid
  . . . 
 )