执行查询时引发错误:Msg 8618

Question

我在基于不同选择的查询中使用了三次表：

Select *
from
(Select * from report where source = 'FA') FA
left join 
(Select * from report where source = 'PI') PI
on FA.account = PI.account and FA.Dealer = PI.Dealer
left join  
(Select * from report where source = 'PIM') PIM
on FA.account = PIM.account and FA.Dealer = PIM.Dealer

由于数据量很大，我得到了以下错误：

Msg 8618，Level 16，State 2，Line 3
查询处理器无法生成查询计划，因为需要工作表，并且其最小行大小超过了允许的最大8060字节。需要工作表的一个典型原因是查询中的GROUP BY或ORDER BY子句。如果查询包含GROUP BY或ORDER BY子句，请考虑减少子句中字段的数量和/或大小。考虑使用字段的前缀(LEFT(((或散列(CHECKSUM((进行分组，或使用前缀进行排序。但是请注意，这将更改查询的行为。

有什么解决方案吗？

Answer 1

如果没有列和模式，不确定您想要什么；但是

试试这样的东西：

SELECT 
RPT.<
... ListOfCommonColumns here
>
,   RFA.<
... RFA Columns here
>   
,   RPI.<
... RPI Columns here
>   
,   PIM.<
... PIM Columns here
>   
FROM        Report  RPT
LEFT JOIN   Report  RFA ON  RFA.<key>   =   RPT.<key>
AND RFA.Source  =   'FA'
LEFT JOIN   Report  RPI ON  RPI.<key>   =   RPT.<key>
AND RPI.Source  =   'PI'
LEFT JOIN   Report  PIM ON  PIM.<key>   =   RPT.<key>
AND PIM.Source  =   'PI'
AND RPT.Account =   PIM.Account 
AND RFA.Dealer  =   PIM.Dealer
-- I am not sure, but have the impresion that <key> is a combination of Account and Dealer

Answer 2

在我的研究中，我发现这个问题实际上更多地与UNION和SELECT DISTINCT有关，下面的例子中TABLE1和TABLE2的值匹配，但有不同的记录集。

WITH A AS (SELECT * FROM JOINTABLE)
SELECT *
FROM 
(
SELECT DISTINCT * 
FROM TABLE1 
LEFT JOIN JOINTABLE ON
JOINTABLE.COLUMN1 = TABLE1.COLUMN1
UNION
SELECT DISTINCT * FROM TABLE2
LEFT JOIN JOINTABLE ON
JOINTABLE.COLUMN1 = TABLE2.COLUMN1
)

将并集更改为UNIONALL并删除其他方法的DISTINCT对我有帮助。

WITH A AS (SELECT * FROM JOINTABLE)
SELECT *
FROM 
(
SELECT * 
FROM TABLE1 
LEFT JOIN JOINTABLE ON
JOINTABLE.COLUMN1 = TABLE1.COLUMN1
UNION ALL
SELECT *
FROM TABLE2
LEFT JOIN JOINTABLE ON
JOINTABLE.COLUMN1 = TABLE2.COLUMN1
)

编辑：最后我不得不重写整个查询以获得更好的语法。