jquery - 连接表为空时超时错误sql
问题描述
我坚持以下 WITH 部分。当 startdate 和 enddate 条件不满足时没有结果时,查询会花费很长时间并最终返回超时错误。当有记录匹配期间,结果是正常的,没有错误。
ILY as (
select
A.[EANcode] as 'EAN',
FL.[ContractId] as 'FacContractId',
SUM(case when FL.[Tp] = 'Dal' AND FL.[LijnType] = 'Statement' then FL.[Hoeveelheid] else 0 end) as 'Dal_Statement',
SUM(case when FL.[Tp] = 'Dal' AND FL.[Lt] = 'Account' then FL.[Hoeveelheid] else 0 end) as 'Dal_Account',
SUM(case when FL.[Tp] = 'Dal' AND FL.[Lt] = 'InvoicedAccount' then FL.[Hoeveelheid] else 0 end) as 'Dal_InvoicedAccount',
SUM(case when FL.[Tp] = 'Piek' AND FL.[Lt] = 'Statement' then FL.[Hoeveelheid] else 0 end) as 'Piek_Statement',
SUM(case when FL.[Tp] = 'Piek' AND FL.[Lt] = 'Account' then FL.[Hoeveelheid] else 0 end) as 'Piek_Account',
SUM(case when FL.[Tp] = 'Piek' AND FL.[Lt] = 'InvoicedAccount' then FL.[Hoeveelheid] else 0 end) as 'Piek_InvoicedAccount',
SUM(case when FL.[Tp] = 'Enkel' AND FL.[Lt] = 'Statement' then FL.[Hoeveelheid] else 0 end) as 'Enkel_Statement',
SUM(case when FL.[Tp] = 'Enkel' AND FL.[Lt] = 'Account' then FL.[Hoeveelheid] else 0 end) as 'Enkel_Account',
SUM(case when FL.[Tp] = 'Enkel' AND FL.[Lt] = 'InvoicedAccount' then FL.[Hoeveelheid] else 0 end) as 'Enkel_InvoicedAccount',
SUM(case when FL.[Tp] = 'TL Dal' AND FL.[Lt] = 'Statement' then FL.[Hoeveelheid] else 0 end) as 'TLDal_Statement',
SUM(case when FL.[Tp] = 'TL Dal' AND FL.[Lt] = 'Account' then FL.[Hoeveelheid] else 0 end) as 'TLDal_Account',
SUM(case when FL.[Tp] = 'TL Dal' AND FL.[Lt] = 'InvoicedAccount' then FL.[Hoeveelheid] else 0 end) as 'TLDal_InvoicedAccount',
SUM(case when FL.[Tp] = 'TL Piek' AND FL.[Lt] = 'Statement' then FL.[Hoeveelheid] else 0 end) as 'TLPiek_Statement',
SUM(case when FL.[Tp] = 'TL Piek' AND FL.[Lt] = 'Account' then FL.[Hoeveelheid] else 0 end) as 'TLPiek_Account',
SUM(case when FL.[Tp] = 'TL Piek' AND FL.[Lt] = 'InvoicedAccount' then FL.[Hoeveelheid] else 0 end) as 'TLPiek_InvoicedAccount'
from {FactuurLijn} FL
inner join {Factuur} FAC on FAC.[Id] = FL.[FactuurId]
inner join {FactuurContract} FC on FC.[FactuurId] = FAC.[Id]
inner join {Aansluiting} A on A.[Id] = FC.[AansluitingId]
inner join {PriceComponent} PC on PC.[Id] = FL.[PrijscomponentId]
where A.[EANcode] in (select getContracts.[EANCode] from getContracts)
and FC.[ContractId] in (select getContracts.[ContractId] from getContracts where getContracts.[ContractNummer] <> '')
and (FL.[StartDate] >= @StartDate and FL.[EndDate] <=@EndDate)
and PC.[PriceType] = 'VP'
and PC.[PriceComp] = 'Levering'
and FAC.[Show] = 1
and FL.[ContractId] <> 0
group by A.[EANcode], FL.[ContractId], FL.[Tp], FL.[Lt]
)
select .....
我已将联接更改为左联接,并将 sum 子句更改为 ISNULL(SUM(...),0) 但这没有效果。(我更改了一些列名以提高这篇文章的可读性)。
解决方案
我已将查询更改为:
InvoicesCheck as (
SELECT
FL.[FactuurId],
CASE WHEN EXISTS (select {FactuurLijn}.[Id]
from {FactuurLijn} FL
inner join {Factuur} FAC on FAC.[Id] = FL.[FactuurId]
inner join {FactuurContract} FC on FC.[FactuurId] = FAC.[Id]
inner join {Aansluiting} A on A.[Id] = FC.[AansluitingId]
inner join {PrijsComponent} PC on PC.[Id] = FL.[PrijscomponentId]
where A.[EANcode] in (select getContracts.[EANCode] from getContracts)
and FC.[ContractId] in (select getContracts.[ContractId] from getContracts)
and (FL.[StartDatumFactuurLijnDetail] >= @StartDate and FL.[EindDatumFactuurLijnDetail] <=@EndDate)
and PC.[PrijsType] = 'VP'
and PC.[PrijsComponentType] = 'Levering'
and FAC.[Tonen] = 1)
then 'TRUE'
else 'FALSE'
end as 'Found'
FROM {FactuurLijn}
),
InvoicesLastYear as (
select
A.[EANcode] as 'EAN',
FL.[ContractId] as 'FacContractId',
case when InvoicesCheck.[Found] = 'TRUE'
then
SUM(case when FL.[Tariefperiode] = 'Dal' AND FL.[LijnType] = 'Statement' then FL.[Hoeveelheid] else 0 end) as 'Dal_Statement',
SUM(case when FL.[Tariefperiode] = 'Dal' AND FL.[LijnType] = 'Account' then FL.[Hoeveelheid] else 0 end) as 'Dal_Account',
SUM(case when FL.[Tariefperiode] = 'Dal' AND FL.[LijnType] = 'InvoicedAccount' then FL.[Hoeveelheid] else 0 end) as 'Dal_InvoicedAccount',
SUM(case when FL.[Tariefperiode] = 'Piek' AND FL.[LijnType] = 'Statement' then FL.[Hoeveelheid] else 0 end) as 'Piek_Statement',
SUM(case when FL.[Tariefperiode] = 'Piek' AND FL.[LijnType] = 'Account' then FL.[Hoeveelheid] else 0 end) as 'Piek_Account',
SUM(case when FL.[Tariefperiode] = 'Piek' AND FL.[LijnType] = 'InvoicedAccount' then FL.[Hoeveelheid] else 0 end) as 'Piek_InvoicedAccount',
SUM(case when FL.[Tariefperiode] = 'Enkel' AND FL.[LijnType] = 'Statement' then FL.[Hoeveelheid] else 0 end) as 'Enkel_Statement',
SUM(case when FL.[Tariefperiode] = 'Enkel' AND FL.[LijnType] = 'Account' then FL.[Hoeveelheid] else 0 end) as 'Enkel_Account',
SUM(case when FL.[Tariefperiode] = 'Enkel' AND FL.[LijnType] = 'InvoicedAccount' then FL.[Hoeveelheid] else 0 end) as 'Enkel_InvoicedAccount',
SUM(case when FL.[Tariefperiode] = 'TL Dal' AND FL.[LijnType] = 'Statement' then FL.[Hoeveelheid] else 0 end) as 'TLDal_Statement',
SUM(case when FL.[Tariefperiode] = 'TL Dal' AND FL.[LijnType] = 'Account' then FL.[Hoeveelheid] else 0 end) as 'TLDal_Account',
SUM(case when FL.[Tariefperiode] = 'TL Dal' AND FL.[LijnType] = 'InvoicedAccount' then FL.[Hoeveelheid] else 0 end) as 'TLDal_InvoicedAccount',
SUM(case when FL.[Tariefperiode] = 'TL Piek' AND FL.[LijnType] = 'Statement' then FL.[Hoeveelheid] else 0 end) as 'TLPiek_Statement',
SUM(case when FL.[Tariefperiode] = 'TL Piek' AND FL.[LijnType] = 'Account' then FL.[Hoeveelheid] else 0 end) as 'TLPiek_Account',
SUM(case when FL.[Tariefperiode] = 'TL Piek' AND FL.[LijnType] = 'InvoicedAccount' then FL.[Hoeveelheid] else 0 end) as 'TLPiek_InvoicedAccount'
else
0 as 'Dal_Statement',
0 as 'Dal_Account',
0 as 'Dal_InvoicedAccount',
0 as 'Piek_Statement',
0 as 'Piek_Account',
0 as 'Piek_InvoicedAccount',
0 as 'Enkel_Statement',
0 as 'Enkel_Account',
0 as 'Enkel_InvoicedAccount',
0 as 'TLDal_Statement',
0 as 'TLDal_Account',
0 as 'TLDal_InvoicedAccount',
0 as 'TLPiek_Statement',
0 as 'TLPiek_Account',
0 as 'TLPiek_InvoicedAccount'
end
from {FactuurLijn} FL
inner join {Factuur} FAC on FAC.[Id] = FL.[FactuurId]
inner join {FactuurContract} FC on FC.[FactuurId] = FAC.[Id]
inner join {Aansluiting} A on A.[Id] = FC.[AansluitingId]
inner join {PrijsComponent} PC on PC.[Id] = FL.[PrijscomponentId]
where A.[EANcode] in (select getContracts.[EANCode] from getContracts)
and FL.[ContractId] in (select getContracts.[ContractId] from getContracts)
and (FL.[StartDatumFactuurLijnDetail] >= @StartDate and FL.[EindDatumFactuurLijnDetail] <=@EndDate)
and PC.[PrijsType] = 'VP'
and PC.[PrijsComponentType] = 'Levering'
and FAC.[Tonen] = 1
group by A.[EANcode], FL.[ContractId]
)
但是,我收到的消息是“as”和“and”附近的语法不正确。我们可以在 WITH 子句中执行 EXISTS 吗?
推荐阅读
- html - Angular Html + Css = PDF,生成正确 PDF 的问题
- kotlin - Mockk - 模拟 javax.mail.Message - 获取 java.lang.VerifyError
- python-3.x - requirements.txt 中的库会安装自己的依赖项吗?
- android - RxJava 主题和可完成
- php - 为什么 array() 和 [] 在此示例中的行为不同
- node.js - 如何将带有-T的curl命令转换为节点js中的axios以将文件上传到azure datalake
- c++ - 为什么在使用可变参数时调用 copy-ctor 而不是隐式转换运算符
- sql - 从左表中获取所有记录并从右表中匹配记录的查询是什么?
- android - Kotlin 协程 - 尝试从挂起的函数返回 ArrayList 时类型不匹配
- php - PHP:从单行私钥获取私钥