基于两个计算列的计算列 [英] Calculated Column Based on Two Calculated Columns

问题描述

我正在尝试进行一个相当复杂的SELECT计算，我将对此进行概括:

I'm trying to do a rather complicated SELECT computation that I will generalize:

1. 主要查询是对表的通配符选择
2. 一个子查询根据条件对所有项目执行COUNT()(这很好)
3. 另一个子查询根据另一个条件在列中执行数字的SUM().这也可以正常工作，除非没有记录满足条件时，它返回NULL.

1. Main query is a wildcard select for a table
2. One subquery does a COUNT() of all items based on a condition (this works fine)
3. Another subquery does a SUM() of numbers in a column based on another condition. This also works correctly, except when no records meet the conditions, it returns NULL.

我最初想将两个子查询加起来，就像(subquery1)+(subquery2) AS total一样，除非subquery2为null，否则工作正常，在这种情况下，无论subquery1的结果如何，total都将为null.我的第二个想法是尝试创建第三列来计算两个子查询(即(subquery1) AS count1, (subquery2) AS count2, count1+count2 AS total)，但我认为不可能计算两个计算出的列，即使就像同样的问题一样.

I initially wanted to add up the two subqueries, something like (subquery1)+(subquery2) AS total which works fine unless subquery2 is null, in which case total becomes null, regardless of what the result of subquery1 is. My second thought was to try to create a third column that was to be a calculation of the two subqueries (ie, (subquery1) AS count1, (subquery2) AS count2, count1+count2 AS total) but I don't think it's possible to calculate two calculated columns, and even if it were, I feel like the same problem applies.

除了获取两个子查询值并将它们加到我的程序中之外，有人对这个问题有一个优雅的解决方案吗?

Does anyone have an elegant solution to this problem outside of just getting the two subquery values and totalling them in my program?

谢谢！

推荐答案

这里发生了两个问题:

• 您不能在同一SELECT列表的另一个表达式中使用一个列别名.

• You can't use one column alias in another expression in the same SELECT list.

但是，您可以在派生表子查询中建立别名，并在外部查询中使用它们.

However, you can establish aliases in a derived table subquery and use them in an outer query.

您不能使用NULL进行算术运算，因为 NULL不为零.

You can't do arithmetic with NULL, because NULL is not zero.

但是，您可以使用COALESCE()函数将NULL默认"为非NULL值.此函数返回其第一个非NULL参数.

However, you can "default" NULL to a non-NULL value using the COALESCE() function. This function returns its first non-NULL argument.

这是一个例子:

SELECT *, count1+count2 AS total
FROM (SELECT *, COALESCE((subquery1), 0) AS count1, 
                COALESCE((subquery2), 0) AS count2 
      FROM ... ) t;

(请记住，必须为派生表赋予表别名，在此示例中为"t")

(remember that a derived table must be given a table alias, "t" in this example)

这篇关于基于两个计算列的计算列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！