sql - SQL Server:带有内部 SELECT 和子 SELECT 的 SUM()。每次都出错
问题描述
问题改写
我在带有自己的 SQL 语句中定义了以下列GROUP BY
...
SUM((SELECT a.CONT_TOT
FROM (SELECT gl2.VisitID, gl2.MessageID, gl2.BillOfLading, COUNT(gl2.ContainerID) AS CONT_TOT
FROM dbo.tblEDIGoodsLines AS gl2
WHERE gl2.VisitID = gl.VisitID AND gl2.MessageID = gl.MessageID AND gl2.BillOfLading = gl.BillOfLading
GROUP BY gl2.VisitID, gl2.MessageID, gl2.BillOfLading) AS a)) as TotalContainers,
...etc
我不断收到这个错误。
无法对包含聚合或子查询的表达式执行聚合函数。
我正在尝试获取外部/更大 SELECT 中的总行数,以及 TOTUCONT 中的 UNIQUE 容器总数。
我究竟做错了什么?
这是更大的 SQL 查询,以说明我对 GROUP BY 和 SUM() 等聚合函数中的子查询的观点:
SELECT
gl.MessageID,
gl.BillOfLading,
gl.[Description],
CASE WHEN e.PortID = 9 THEN 'Export'
WHEN e.PortID = 11 THEN 'Import'
ELSE 'ERROR'
END AS Direction,
CASE WHEN ctypes.ID IS NOT NULL
THEN ctypes.ContainerSizeType
ELSE 'OTH'
END AS CSizeType,
ctypes.Length_ft + 'ft ' + ctypes.Height_ft + 'ft - ' + ctypes.Characteristics + ' (' + COALESCE(ctypes.Codes1995, ctypes.Codes1984) + ')' AS ContainerType,
COUNT(gl.ContainerID) AS TOTCONT,
SUM(a.CTOTAL) AS TOTUCONT
FROM tblEDIGoodsLines AS gl
INNER JOIN tblEDIEquipmentLines AS el
ON el.MessageID = gl.MessageID AND
el.ContainerID = gl.ContainerID
INNER JOIN tblEDI AS e
ON CHARINDEX(e.MessageID, gl.MessageID) > 0 AND
e.VisitID = gl.VisitID AND
CHARINDEX('EXCEL', e.MessageRelease) = 0 AND
e.Status = 1
LEFT JOIN tblContainerTypesISO6346 AS ctypes
ON ctypes.Codes1984 = el.SizeAndType OR
ctypes.Codes1995 = el.SizeAndType
LEFT JOIN (SELECT gl2.MessageID, gl2.VisitID, gl2.BillOfLading, gl2.description, COUNT(DISTINCT gl2.ContainerID) AS CTOTAL
FROM tblEDIGoodsLines AS gl2
WHERE gl2.MessageID = gl.MessageID
AND gl2.VisitID = gl.VisitID
and gl2.BillOfLading = gl.billoflading
and gl2.description = gl.description
GROUP BY gl2.MessageID, gl2.VisitID, gl2.BillOfLading, gl2.description) AS a
ON a.MessageID = gl.MessageID AND a.VisitID = gl.VisitID AND a.BillOfLading = gl.billoflading AND a.description = gl.description
WHERE gl.Status = 1
AND gl.VisitID = 22987
GROUP BY
gl.MessageID,
gl.BillOfLading,
gl.[Description],
CASE WHEN e.PortID = 9 THEN 'Export'
WHEN e.PortID = 11 THEN 'Import'
ELSE 'ERROR'
END,
CASE WHEN ctypes.ID IS NOT NULL
THEN ctypes.ContainerSizeType
ELSE 'OTH'
END,
ctypes.Length_ft + 'ft ' + ctypes.Height_ft + 'ft - ' + ctypes.Characteristics + ' (' + COALESCE(ctypes.Codes1995, ctypes.Codes1984) + ')'
以上无论如何都不起作用,因为我试图通过将“列” SELECT 分成它自己的 JOIN 查询来解决这个问题,但现在我明白了:
无法绑定多部分标识符“gl.MessageID”。
所以这意味着 LEFT JOIN (SELECT...) 是无效的?
再次感谢
更新 2
这是一个数据示例,可以进一步解释:
所以你可以看到我想要的结果,TOTCONT 加起来是 '4'。这很简单——只计算行数,但 TOTUCONT 只计算一次容器 ID。
解决方案
尝试这个,
(SELECT SUM(a.CONT_TOT)
FROM (SELECT gl2.VisitID, gl2.MessageID, gl2.BillOfLading, COUNT(gl2.ContainerID) AS CONT_TOT
FROM dbo.tblEDIGoodsLines AS gl2
WHERE gl2.VisitID = gl.VisitID AND gl2.MessageID = gl.MessageID AND gl2.BillOfLading = gl.BillOfLading
GROUP BY gl2.VisitID, gl2.MessageID, gl2.BillOfLading) AS a) as TotalContainers,