合并SQL查询遇#1054错误：trelsucompon.sunum列不存在

错误原因 #

你遇到的#1054 - Unknown column 'trelsucompon.sunum' in 'field list'错误，本质是合并后的查询里没有引入trelsucompon表，但你在SELECT列表和tblcompon的JOIN条件里都用到了这个表的字段，数据库找不到该表的定义，因此报错。

解决方案 #

根据你的需求，提供两种可行的合并方案：

方案1：使用UNION ALL合并两个独立查询 #

这种方式保留两个原查询的逻辑，通过UNION ALL合并结果集。注意要让两个查询的SELECT字段数量、顺序、数据类型一致，第二个查询没有tblStruct相关字段，用NULL填充：

-- 第一个查询：来自tblStruct相关逻辑
SELECT 
  tblStruct.Site, tblStruct.SUNum, tlkpSUDesc.SUDesc, 
  tblStruct.LenActl, tblStruct.LenInf, tblStruct.LenBasis, 
  tblStruct.WidActl, tblStruct.WidInf, tblStruct.WidBasis, 
  tblStruct.HtActl, tblStruct.HtInf, tblStruct.HtBasis, 
  tblStruct.DepActll, tblStruct.DepInf, tblStruct.DepBasis, 
  tblStruct.Constr, tblStruct.RoofSupport, tblStruct.RoofComm, tblStruct.Axis,
  tblcompon.site AS compon_site, tblcompon.datebegin, tblcompon.dateend, 
  trelsucompon.sunum, trelsucompon.componid
FROM tlkpSUDesc
RIGHT JOIN (tblSU 
 RIGHT JOIN tblStruct ON (tblSU.SUNum = tblStruct.SUNum) 
  AND (tblSU.Site = tblStruct.Site)) ON tlkpSUDesc.SUDescCode = tblSU.SUDescCode
-- 先关联trelsucompon表，再关联tblcompon
LEFT JOIN trelsucompon ON trelsucompon.site = tblStruct.Site AND trelsucompon.sunum = tblStruct.SUNum
LEFT JOIN tblcompon on tblcompon.site = trelsucompon.site AND tblcompon.componid = trelsucompon.componid
WHERE tblStruct.Site IN ('5MT123') AND tblcompon.datebegin >= 540 AND tblcompon.dateend <= 650

UNION ALL

-- 第二个查询：来自trelsucompon逻辑，用NULL填充tblStruct相关字段
SELECT 
  NULL AS Site, NULL AS SUNum, NULL AS SUDesc, 
  NULL AS LenActl, NULL AS LenInf, NULL AS LenBasis, 
  NULL AS WidActl, NULL AS WidInf, NULL AS WidBasis, 
  NULL AS HtActl, NULL AS HtInf, NULL AS HtBasis, 
  NULL AS DepActll, NULL AS DepInf, NULL AS DepBasis, 
  NULL AS Constr, NULL AS RoofSupport, NULL AS RoofComm, NULL AS Axis,
  tblcompon.site AS compon_site, tblcompon.datebegin, tblcompon.dateend, 
  trelsucompon.sunum, trelsucompon.componid
FROM trelsucompon   
LEFT JOIN tblcompon on tblcompon.site = trelsucompon.site AND tblcompon.componid = trelsucompon.componid       
WHERE tblcompon.site = '5MT10684' AND tblcompon.datebegin >= 540 AND tblcompon.dateend <= 650

ORDER By Site, SUNum ASC;

方案2：调整JOIN逻辑，整合所有表关联 #

如果希望把两个查询的逻辑整合到一个JOIN链中，需要明确关联trelsucompon表，并合并过滤条件：

SELECT 
  tblStruct.Site, tblStruct.SUNum, tlkpSUDesc.SUDesc, 
  tblStruct.LenActl, tblStruct.LenInf, tblStruct.LenBasis, 
  tblStruct.WidActl, tblStruct.WidInf, tblStruct.WidBasis, 
  tblStruct.HtActl, tblStruct.HtInf, tblStruct.HtBasis, 
  tblStruct.DepActll, tblStruct.DepInf, tblStruct.DepBasis, 
  tblStruct.Constr, tblStruct.RoofSupport, tblStruct.RoofComm, tblStruct.Axis,
  tblcompon.site AS compon_site, tblcompon.datebegin, tblcompon.dateend, 
  trelsucompon.sunum, trelsucompon.componid
FROM tlkpSUDesc
RIGHT JOIN (tblSU 
 RIGHT JOIN tblStruct ON (tblSU.SUNum = tblStruct.SUNum) 
  AND (tblSU.Site = tblStruct.Site)) ON tlkpSUDesc.SUDescCode = tblSU.SUDescCode
-- 关联trelsucompon表（根据业务逻辑调整关联条件）
LEFT JOIN trelsucompon ON trelsucompon.site = tblStruct.Site AND trelsucompon.sunum = tblStruct.SUNum
LEFT JOIN tblcompon on tblcompon.site = trelsucompon.site AND tblcompon.componid = trelsucompon.componid
-- 合并两个Site的过滤条件
WHERE 
  (tblStruct.Site IN ('5MT123') OR tblcompon.site = '5MT10684')
  AND tblcompon.datebegin >= 540 
  AND tblcompon.dateend <= 650
ORDER By tblStruct.Site, tblStruct.SUNum ASC;

关键提示 #

• 必须在查询的FROM/JOIN部分明确引入trelsucompon表，才能使用它的字段，这是解决#1054错误的核心。
• 使用UNION ALL时，两个查询的SELECT字段必须严格匹配，缺失字段用NULL填充。
• 关联trelsucompon的条件需要符合你的业务逻辑，如果默认的site+sunum关联不对，请根据实际表关系调整。

内容的提问来源于stack exchange，提问作者stanhook