向count()查询添加连接
我从以前的问题here中取得了以下代码并稍作了更改。向count()查询添加连接
SELECT *
FROM ES_TOOL
INNER JOIN ES_HARDWARE ON ES_HARDWARE.eshw_ID = ES_TOOL.ESTOOL_HARDWARE
INNER JOIN ES_PAYMENT on ES_payment.espay_id = es_TOOL.estool_payment
LEFT JOIN (
SELECT
tchap.estch_tool, tfacet.estfa_tool,
count(marks.esmrk_value) AmtMarks
FROM ES_MARK marks
left Join ES_TOOL_FACET tfacet ON marks.esmark_tool_facet = tfacet.estfa_id --line added
left Join ES_TOOL_CHAPTER tchap ON marks.esmark_tool_chapter = tchap.estch_id
GROUP BY tchap.estch_tool
) h ON ES_TOOL.estool_id = h.estch_tool
我试图添加一个额外的连接,试图从满足任一左连接“ON”条件的“标记”中获得标记计数。没有额外的行查询执行,但不计算符合“方面”条件的标记。随着它,我得到以下错误:
Msg 8120, Level 16, State 1, Line 1
Column 'ES_TOOL_FACET.estfa_tool' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
任何帮助,将不胜感激。
我的解决方案有两种变体。哪一种更适合你,可能取决于哪一种产品在试用数据时会产生更好的执行计划。
说明变#1:(。在这两种情况下,我所描述只有主SELECT左背后的逻辑JOIN子查询,即实际上变成取代的一部分,但脚本来作为完整的查询,相当于你的) :
- Pull and UNION ALL
tools
表中的项目。 - 相应地参加
marks
表的列表。 - 将由工具项目设置的结果分组并获取计数。
查询:
SELECT *
FROM ES_TOOL
INNER JOIN ES_HARDWARE ON ES_HARDWARE.eshw_ID = ES_TOOL.ESTOOL_HARDWARE
INNER JOIN ES_PAYMENT on ES_payment.espay_id = es_TOOL.estool_payment
LEFT JOIN (
SELECT
tools.tool,
COUNT(*) AS AmtMarks
FROM (
SELECT 'tchap' AS tbl, estch_id AS id, estch_tool AS tool
FROM ES_TOOL_CHAPTER
UNION ALL
SELECT 'tfacet' AS tbl, estfa_id AS id, estfa_tool AS tool
FROM ES_TOOL_FACET
) tools
INNER JOIN ES_MARK marks
ON tools.tbl = 'tchap' AND tools.id = marks.esmark_tool_chapter
OR tools.tbl = 'tfacet' AND tools.id = marks.esmark_tool_facet
GROUP BY tools.tool
) h ON ES_TOOL.estool_id = h.tool
变#2:
- 加入
ES_TOOL_CHAPTER
对marks
并得到所有的estch_tool
值,包括重复。 - 同样,加入
ES_TOOL_FACET
与marks
并得到所有estfa_tool
值,也有重复。 - UNION ALL两组。
- 按工具项对结果集进行分组并获取计数。
和查询:
SELECT *
FROM ES_TOOL
INNER JOIN ES_HARDWARE ON ES_HARDWARE.eshw_ID = ES_TOOL.ESTOOL_HARDWARE
INNER JOIN ES_PAYMENT on ES_payment.espay_id = es_TOOL.estool_payment
LEFT JOIN (
SELECT
tools.tool,
COUNT(*) AS AmtMarks
FROM (
SELECT estch_tool AS tool
FROM ES_TOOL_CHAPTER tools
INNER JOIN ES_MARK marks ON tools.estch_id = marks.esmark_tool_chapter
UNION ALL
SELECT estfa_tool AS tool
FROM ES_TOOL_FACET tools
INNER JOIN ES_MARK marks ON tools.estfa_id = marks.esmark_tool_facet
) tools
GROUP BY tools.tool
) h ON ES_TOOL.estool_id = h.tool
错误消息表示ES_TOOL_FACET.estfa_tool需要包含在Group By中。
当您使用分组依据时,所有非聚合列必须包含在分组中。
这应该是显而易见的,在你的内部查询:
SELECT tchap.estch_tool, tfacet.estfa_tool, count(marks.esmrk_value) AmtMarks
FROM ES_MARK marks
left Join ES_TOOL_FACET tfacet ON marks.esmark_tool_facet = tfacet.estfa_id --line added
left Join ES_TOOL_CHAPTER tchap ON marks.esmark_tool_chapter = tchap.estch_id
GROUP BY tchap.estch_tool
你有三个选择列,estch_tool
这是GROUP BY
子句中,esmrk_value
这是一个聚合函数,并且estfa_tool
既不是在GROUP BY
子句也不在集合函数中。
您的解决方案应该是:
GROUP BY tchap.estch_tool, tfacet.estfa_tool
-
AVG(tfacet.estfa_tool)
或任何聚合函数
我试过了:GROUP BY tchap.estch_tool,tfacet.estfa_tool,但是然后查询为每个工具返回2行,使用章节和刻面匹配,而我需要为每个工具使用一行,总计数用于章节和刻面匹配。想法? – Meir 2011-03-14 09:08:03
那么,你必须调试你的查询。也许某处你加入了一个扩展结果集2的表(在这种情况下,你应该缺少一个WHERE子句)?啊,调试和调整SQL的乐趣... – 2011-03-14 09:16:58
有在此查询语法错误 -
SELECT
tchap.estch_tool,
tfacet.estfa_tool,
count(marks.esmrk_value) AmtMarks
FROM ES_MARK marks
left Join ES_TOOL_FACET tfacet ON
marks.esmark_tool_facet = tfacet.estfa_id --line added
left Join ES_TOOL_CHAPTER tchap ON
marks.esmark_tool_chapter = tchap.estch_id
GROUP BY tchap.estch_tool
GROUP BY任务SELECT列表中出现任何列d要么被聚合,要么出现在GROUP BY子句中。
因此,在tfacet.estfa_tool上放置一个聚合函数 - MIN,MAX,SUM,AVG等,因为它不会出现在group by子句中或包含它。
噢,我..我打字很慢......已经有两个答案! – 2011-03-14 09:02:55
如果您可以在你的问题的一些例子输入和输出数据,它会更容易被具体介绍一下答案。 – 2011-03-14 08:55:39
'tchap.estch_tool'和'tfacet.estfa_tool'可以保持相同的值吗?如果可以的话,是否应将相同的数值匹配计算在一起? – 2011-03-14 09:45:59
@andry是的,绝对。 – Meir 2011-03-14 10:25:31