SQL查询在选择列表中无效
问题描述:
我不知道为什么这是无效的,我无法弄清楚。当我的主管离开时,我得到了一个遗留数据库,我负责直到有人来替换他。我试图运行此查询...SQL查询在选择列表中无效
SELECT tblM.guidRId, SUM(dbo.tblCH.curTotalCost) AS curValue
FROM tblCH INNER JOIN
tblM ON tblCH.guidMId = tblM.guidMId INNER JOIN
ViewLM ON tblM.strNumber = ViewLM.strNumber
WHERE (tblM.guidRId = '4d832bc8-1827-4054-9896-6111844b0f26')
我不断收到的错误...消息8120,级别16,状态1,行 列“tblM.guidRId”在选择列表中无效因为它不包含在聚合函数或GROUP BY子句中。
为什么会发生此错误?
答
你忘了组guidRId。 (您汇总数据)
SELECT
tblM.guidRId,
SUM(dbo.tblCH.curTotalCost) AS curValue
FROM
tblCH
INNER JOIN tblM ON tblCH.guidMId = tblM.guidMId
INNER JOIN ViewLM ON tblM.strNumber = ViewLM.strNumber
WHERE
tblM.guidRId = '4d832bc8-1827-4054-9896-6111844b0f26'
GROUP BY tblM.guidRId
+0
谢谢,我在这里挠我的头哈哈 – EvanGWatkins 2010-10-06 13:19:53
答
因为你需要一个GROUP BY,如果你要使用聚合functon像SUM()或COUNT,AVG(),等...]与其他非聚合列:
SELECT tblM.guidRId, SUM(dbo.tblCH.curTotalCost) AS curValue
FROM tblCH
INNER JOIN tblM
ON tblCH.guidMId = tblM.guidMId
INNER JOIN ViewLM
ON tblM.strNumber = ViewLM.strNumber
WHERE tblM.guidRId = '4d832bc8-1827-4054-9896-6111844b0f26'
GROUP BY tblM.guidRId;
答
尝试:
SELECT
tblM.guidRId, SUM(dbo.tblCH.curTotalCost) AS curValue
FROM
tblCH
INNER JOIN tblM
ON tblCH.guidMId = tblM.guidMId
INNER JOIN ViewLM
ON tblM.strNumber = ViewLM.strNumber
WHERE (tblM.guidRId = '4d832bc8-1827-4054-9896-6111844b0f26')
GROUP BY tblM.guidRId
你缺少“由tblM.guidRId组”末 – 2010-10-06 13:15:45
格式化SQL因此这将是更具可读性会很好。 .. – Amirshk 2010-10-06 13:15:57