MYSQL沒有針對另一個表的不同id獲得總和零值（沒有該id的條目）-有解無憂

SELECT d.millid, d.bookno, d.partyname, d.count, 
       d.counttype, d.orderqty, d.dispqty, d.pending 
from ( SELECT b.millid, b.count, b.counttype, b.bookno, b.partyname, 
              COALESCE(b.bags,0) as orderqty, 
              COALESCE(sum(y.bags),0) as dispqty, 
              COALESCE((b.bags-sum(y.bags)),0) as pending 
       from yarnDispatch y 
       JOIN yarnbook b ON b.bookno=y.bookno 
       GROUP by b.bookno) d 
WHERE d.pending>0 
ORDER BY d.millid, d.bookno

在此查詢中，表 yarnbook 是主表，具有從 1 到 2600（及以上）的唯一 bookno（Booking Nos），而其他表 yarndispatch 對每個相同的 bookno（yarnbook）有多個或零個調度（包）條目。

當我運行此查詢時，在針對 bookno 至少有單個調度條目的情況下給出正確的結果，但在 yarndispatch 表中沒有調度條目的情況下不給出 (sum(bags) as 0)。

請分享您的觀點/解決方案。

uj5u.com熱心網友回復：

您需要執行外連接，它在一個表有資料而另一個表沒有資料的情況下保留資料，而不是只在兩個表中都存在資料時才回傳行的內連接。

說JOIN相當于INNER JOIN。替換JOIN為LEFT OUTER JOIN，你的結果應該更符合你想要的。

uj5u.com熱心網友回復：

您需要LEFT JOINorRIGHT JOIN 來獲取“空”行

SELECT d.millid, d.bookno, d.partyname, d.count, 
       d.counttype, d.orderqty, d.dispqty, d.pending 
from ( SELECT b.millid, b.count, b.counttype, b.bookno, b.partyname, 
              COALESCE(b.bags,0) as orderqty, 
              COALESCE(sum(IFNULL(y.bags,)),0) as dispqty, 
              COALESCE((b.bags-sum(y.bags)),0) as pending 
       from yarnDispatch y 
       RIGHT JOIN yarnbook b ON b.bookno=y.bookno 
       GROUP by b.bookno) d 
WHERE d.pending>0 
ORDER BY d.millid, d.bookno

兩個連接都可以互換，但有些人更喜歡LEFT JOIN

SELECT d.millid, d.bookno, d.partyname, d.count, 
       d.counttype, d.orderqty, d.dispqty, d.pending 
from ( SELECT b.millid, b.count, b.counttype, b.bookno, b.partyname, 
              COALESCE(b.bags,0) as orderqty, 
              COALESCE(sum(IFNULL(y.bags,0)),0) as dispqty, 
              COALESCE((b.bags-sum(y.bags)),0) as pending 
       from
       yarnbook b 
        LEFT JOIN yarnDispatch y ON b.bookno=y.bookno 
       GROUP by b.bookno) d 
WHERE d.pending>0 
ORDER BY d.millid, d.bookno

uj5u.com熱心網友回復：

感謝您的寶貴回復；

我試過以下查詢，它正在作業；

    SELECT
    d.millid,
    d.bookno,
    d.partyname,
    d.count,
    d.counttype,
    d.orderqty,
    d.dispqty,
    (d.orderqty - d.dispqty) AS pending
FROM
    (
    SELECT
        b.millid,
        b.count,
        b.counttype,
        b.bookno,
        b.partyname,
        COALESCE(b.bags, 0) AS orderqty,
        Y.bags,
        COALESCE(SUM(Y.bags),
        0) AS dispqty
    FROM
        yarnDispatch Y
    RIGHT JOIN yarnbook b ON
        b.bookno = Y.bookno
    GROUP BY
        b.bookno
    ORDER BY
        b.millid,
        b.bookno
) d
WHERE
    (d.orderqty - d.dispqty) > 0 
ORDER BY
    d.millid,
    d.bookno

轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/474664.html

標籤：mysql sql 加入

上一篇：PostgreSQL嵌套回圈連接性能

下一篇：使用SQL查詢從多個表中洗掉記錄