sql - 如何根据前两列的值计算第三列？

Question

我的样本数据如下:

表 T1:

+------+-------+
| Item | Order |
+------+-------+
| A    |    30 |
| B    |     3 |
| C    |    15 |
| A    |    10 |
| B    |     2 |
| C    |    15 |
+------+-------+

表T2:

+------+-------+----------+--------+
| Item | Stock | Released | Packed |
+------+-------+----------+--------+
| A    |    30 |       10 |      0 |
| B    |    20 |        0 |      5 |
| C    |    10 |        5 |      5 |
+------+-------+----------+--------+

现在，我的要求是获取以下形式的数据:

+------+-------+-----------+----------------+
| Item | Order | Available | Availability % |
+------+-------+-----------+----------------+
| A    |    40 |        20 |          50.00 |
| B    |     5 |        15 |         100.00 |
| C    |    30 |         0 |           0.00 |
+------+-------+-----------+----------------+

我可以使用以下方法获取前三列的数据:

SELECT
T1.Item AS Item, SUM(T1.Order) AS Order, T2.Stock - T2.Released - T2.Packed AS Available
FROM T1 INNER JOIN T2 ON T1.Item = T2.Item
GROUP BY T1.Item, T2.Stock, T2.Released, T2.Packed

我的问题是:有没有办法使用第 2 列和第 3 列的计算值来计算第三列，而不是写下用于计算这两列的整个公式？原因是第三列的计算公式不小，多次使用了2和3的值。

有没有办法做这样的事情:

(CASE WHEN Available = 0 THEN 0
ELSE (CASE WHEN Available > Order THEN 100 ELSE Available/Order END) END) AS [Availability %]

你有什么建议？

注意:请忽略上面使用的 CASE 表达式中使用的语法，我使用它只是为了解释公式。

Answer 1

通过使用子查询，您可以做到这一点

 with cte as

    (
    SELECT
    T1.Item AS Item, 
    SUM(T1.Order) AS Order,
   T2.Stock - T2.Released, T2.Packed AS Available
    FROM T1 INNER JOIN T2 ON T1.Item = T2.Item
    GROUP BY T1.Item, T2.Stock, T2.Released, T2.Packed
    ) select cte.*,
   (
    CASE WHEN Available = 0 THEN 0
    ELSE (CASE WHEN Available > Order THEN 100 ELSE 
    100/(Order/Available)*1.00 END
    ) END) AS [Availability %] from cte