如何根据前两列的值计算第三列?

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 列的整个公式？原因是计算第三列的公式不小，多次使用 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

Answer 2

如果你不打算使用 CTE，你可以这样做

declare @t1 table (item varchar(1), orderqty int)
declare @t2 table (item varchar(1), stock int, released int, packed int)
insert into @t1 values ('A', 30), ('B', 3), ('C', 15), ('A', 10), ('B', 2), ('C', 15)
insert into @t2 values ('A', 30, 10, 0), ('B', 20, 0, 5), ('C', 10, 5, 5)
select q.Item,
q.orderqty, 
q.available,
case when q.available = 0 then 0
when q.available > q.orderqty then 100
else 100 / (q.orderqty / q.available) -- or whatever formula you need
end as [availability %] 
from   ( select t1.Item,
sum(t1.orderqty) as orderqty,     
t2.Stock - t2.Released - t2.Packed as available
from   @t1 t1
left outer join @t2 t2 on t1.Item = t2.Item
group by t1.Item, t2.Stock, t2.Released, t2.Packed
) q

结果是

Item    orderqty    available   availability %  
----    --------    ---------   --------------  
A       40          20           50 
B        5          15          100 
C       30           0            0 

Answer 3

我认为您的结果表有一些错误。但是您可以通过键入以下内容来获得所需的结果：

Select final_tab.Item,
final_tab.ordered,
final_tab.Available,
CASE WHEN final_tab.Available = 0 THEN 0
ELSE 
(CASE WHEN final_tab.Available > final_tab.ordered THEN 100 
ELSE convert(float,final_tab.Available)/convert(float,final_tab.ordered)*100 END)
END AS [Availability %]
from
(Select tab1.Item,tab1.ordered,
(Table_2.Stock-Table_2.Released-Table_2.Packed)as Available 
from 
( SELECT Item,sum([Order]) as ordered
FROM Table_1
group by Item )as tab1
left join 
Table_2
on tab1.Item=Table_2.Item)as final_tab

Answer 4

您也可以尝试使用以下内容，请确保您已测试输出。

declare @t1 table ([item] char(1), [order] int)
insert into @t1
values ('A', 30),
('B', 3),
('C', 30),
('A', 15),
('A', 10),
('B', 2),
('C', 15)
declare @t2 table ([item] char(1), [stock] int, [released] int, [packed] int)
insert into @t2 
values ('A',30,10,0),
('B',20,0,5),
('C',10,5,5)

SELECT
T1.Item AS Item, 
SUM(T1.[Order]) AS [Order], 
T2.Stock - T2.Released as Available, 
case when SUM(T1.[Order]) < (T2.Stock - T2.Released) then 100
else  cast(cast((T2.Stock - T2.Released) as decimal) / cast(SUM(T1.[Order]) as decimal) * 100 as decimal(4,2))
end AS AvailablePercentage
FROM @T1 t1 
INNER JOIN @T2 t2 ON T1.Item = T2.Item
GROUP BY T1.Item, T2.Stock, T2.Released, T2.Packed