我正在嘗試連接兩個表以顯示與 where 子句匹配的所有行，而不僅僅是與連接匹配的行。這是兩個賬單表，我正在嘗試合并它們，以便兩個表中匹配年份和月份的每一行都有幾個欄位。
SELECT
tblhydrobill.billyear, tblhydrobill.billmonth, tblhydrobill.KWH as elecconsumption, tblhydrobill.CurrentCharges as eleccost,
tblgasdata.cubicft as gasconsumption, tblgasdata.total as gascost
FROM tblhydrobill
FULL JOIN tblgasdata ON tblhydrobill.billyear = tblgasdata.billyear and tblhydrobill.billmonth = tblgasdata.billmonth
WHERE tblhydrobill.meteridnumber = 19 and tblgasdata.buildingid = 19
ORDER BY tblhydrobill.billyear asc, tblhydrobill.billmonth asc
LEFT JOIN 正確執行，但僅顯示兩個表上存在月/年資料的結果。我試圖讓它回傳所有行，如果連接的一側有空欄位，如果另一側沒有匹配項，則可以，反之亦然。
使用 FULL JOIN 我得到以下 MySQL 錯誤：
Error Code: 1054. Unknown column 'tblhydrobill.billyear' in 'field list'
更新：
正如@jarlh 所指出的，MySQL 還不支持 FULL JOIN，謝謝。
uj5u.com熱心網友回復：
由于 MySql 不支持FULL JOIN，它被FULL用作 tblhydrobill 的別名。
但是可以模擬 FULL JOIN。
SELECT h.billyear, h.billmonth
, h.KWH as elecconsumption
, h.CurrentCharges as eleccost
, g.cubicft as gasconsumption
, g.total as gascost
FROM tblhydrobill h
LEFT JOIN tblgasdata g
ON g.billyear = h.billyear
AND g.billmonth = h.billmonth
AND g.buildingid = 19
WHERE h.meteridnumber = 19
UNION ALL
SELECT g.billyear, g.billmonth
, h.KWH as elecconsumption
, h.CurrentCharges as eleccost
, g.cubicft as gasconsumption
, g.total as gascost
FROM tblgasdata g
LEFT JOIN tblhydrobill h
ON h.billyear = g.billyear
AND h.billmonth = g.billmonth
AND h.meteridnumber = 19
WHERE g.buildingid = 19
AND h.billyear IS NULL
ORDER BY billyear, billmonth
uj5u.com熱心網友回復：
您可以使用 生成一個或兩個表中存在的年月對串列union，然后使用該結果左連接兩個表：
select *
from (
select billyear, billmonth from tblhydrobill where meteridnumber = 19
union
select billyear, billmonth from tblgasdata where buildingid = 19
) as ym
left join tblhydrobill on tblhydrobill.billyear = ym.billyear and tblhydrobill.billmonth = ym.billmonth and tblhydrobill.meteridnumber = 19
left join tblgasdata on tblgasdata.billyear = ym.billyear and tblgasdata.billmonth = ym.billmonth and tblgasdata.buildingid = 19
order by ym.billyear, ym.billmonth
請注意，可以ym手動構建串列，例如：
from (
select 2022, 1 union
select 2021, 12 union
select 2021, 11
) as ym
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/417308.html
標籤：