sql-server - SQL Server：Left Join Select 语句收到错误“查询优化期间查询处理器用尽堆栈空间”

Question

我正在尝试创建一个包含 2000 多列的 View 表，并且 select 语句中声明的属性的 95% 也是 select 语句子查询。

这就是我想要做的：表 A = 100 列的主表 表 C = 10 列的子表

表 C 将加入表 A。但不是来自 A 的 100 列 + 来自 C 的 10 列，而是来自 C 的 A + 的 100 列（10 * 表中 fk = pk 最多为 200 的每一行） .

这是我的示例代码：

select
A.*
, 

(select C.property where C.propertyNumber = 1 and C.propertyFk = A.propertyIdPk) as property1,
(select C.property where C.propertyNumber = 2 and C.propertyFk = A.propertyIdPk) as property2,
(select C.property where C.propertyNumber = 3 and C.propertyFk = A.propertyIdPk) as property3,
(select C.property where C.propertyNumber = 4 and C.propertyFk = A.propertyIdPk) as property4,
(select C.property where C.propertyNumber = 5 and C.propertyFk = A.propertyIdPk) as property5,
(select C.property where C.propertyNumber = 6 and C.propertyFk = A.propertyIdPk) as property6,
(select C.property where C.propertyNumber = 7 and C.propertyFk = A.propertyIdPk) as property7,
(select C.property where C.propertyNumber = 8 and C.propertyFk = A.propertyIdPk) as property8,
(select C.property where C.propertyNumber = 9 and C.propertyFk = A.propertyIdPk) as property9,
(select C.property where C.propertyNumber = 10 and C.propertyFk = A.propertyIdPk) as property10

from [schema].table A
left join [schema].table C on A.propertyIdPk = C.propertyFk
where C.propertyFk = A.propertyIdPk

还有其他方法可以做到这一点还是我做错了？

标签： sql-serverssmssql-server-2016

Answer 1

抱歉，但听起来您确实需要重新考虑您的架构设计。当人们有太多的 FK 时，我已经看到过这个错误，但总是会回到架构设计问题。