父级依赖于 SQL Server 中的子级时的嵌套情况

Question

我想检查当涉及到父级依赖于子项的嵌套情况时，是否有更好的方法可以在 from 子句中抛出查询。

请注意逻辑的细节，但这是我想要完成的蓝图，它有效。代码看起来很草率。有没有更好/更整洁的方法来实现这一点？

SELECT 
*,
CASE 
WHEN b.NewCalcColRate IS NULL
THEN 0 
ELSE b.NewCalcColRate * 1000 
END AS FinalCalcColRate
FROM
(SELECT
a.*,
CASE 
WHEN a.calcColRate IS NULL
THEN 0 
ELSE a.calcColRate * 100 
END AS NewCalcColRate
FROM
(SELECT
*,
CASE 
WHEN f.AnnualCost IS NULL
THEN 0 
ELSE f.AnnualCost / 12 
END AS calcColRate
FROM 
Rates) AS a
) AS b

Answer 1

首先，NewCalcColRate和calcColRate永远不会被NULL，因为在嵌套CASE中，当它被NULL(CASE WHEN f.AnnualCost IS NULL THEN 0)时，你将其设置为0，所以逻辑是没有意义的。

此外，您有此列f引用，尽管它没有在该子查询中的Rate表上设置别名。

据我所知，这可以简化为：

SELECT
*,
CASE 
WHEN AnnualCost IS NULL
THEN 0 
ELSE 
(AnnualCost / 12) * 1000000
END AS calcColRate
FROM 
Rate

或者，正如贾布斯指出的那样...

SELECT 
*,
(ISNULL(AnnualCost,0)/12) * 1000000 
from 
Rate

另外需要注意的是，根据AnnualCost的数据类型，您可能需要考虑除以小数，这样您就不会进行INTEGER除法并成为精度丢失的受害者。

(AnnualCost / 12.0) * 1000000.0

例

select 
(1 / 12)   * 1000000  --Returns 0
,(1 / 12.0) * 1000000  --Returns 83333.000000

将来，如果您的代码有效并且您只是在寻找改进，我会将其发布在代码审查上，因为它更适合此类请求。

Answer 2

您的问题似乎缺少一些重要信息。如果您真正想要的只是Rates表中每一行的FinalCalcColRate，那么您可以按照他/她的答案中建议的步骤@scsimon一步：

select
r.*,
FinalCalcColRate = case when r.AnnualCost is null then 0 else r.AnnualCost / 12 * 100000 end
from
dbo.Rates r;

或者使用coalesce(或 SQL Server 中的isnull;有关差异的详细信息，请参阅此问题的类似实现)：

select
r.*,
FinalCalcColRate = coalesce(r.AnnualCost / 12 * 100000, 0)
from
dbo.Rates r;

但是，@scsimon查询与原始查询之间的一个区别是，前者仅输出最终计算值，而后者还生成所有中间值。从您的问题中不清楚此查询的使用者是否需要这些值。如果它们愿意，那么您可以简单地包含它们：

select
r.*,
CalcColRate = coalesce(r.AnnualCost / 12, 0),
NewCalcColRate = coalesce(r.AnnualCost / 12 * 100, 0),
FinalCalcColRate = coalesce(r.AnnualCost / 12 * 100000, 0)
from
dbo.Rates r;

这里有一些重复的逻辑 - 例如，如果你要更改CalcColRate的定义，你还必须手动更改NewCalcColRate和FinalCalcColRate的表达式 - 但它足够小，我怀疑它值得担心。尽管如此，如果原始查询中的构造是出于避免此类重复的愿望，则可以重构查询以使用 CTE 而不是嵌套查询：

with CalcCTE as
(
select
r.*,
CalcColRate = coalesce(r.AnnualCost / 12, 0)
from
dbo.Rates r
),
NewCalcColCTE as
(
select
c.*,
NewCalcColRate = c.CalcColRate * 100
from
CalcCTE c
)
select
n.*,
FinalCalcColRate = n.NewCalcColRate * 1000
from
NewCalcColCTE n;

这显然比我之前独立定义所有值的查询更长，可以说更难理解，但它确实具有每个步骤都建立在最后一个步骤之上的优势，并且 CTE 公式往往比等效的嵌套查询集更具可读性，因为这些步骤是按照计算顺序编写的， 而对于嵌套查询，您必须找到最内层的点并向外工作，这可能会在匆忙中变得混乱。