使用"AS“时的SQL大小写

Question

我的目标是为我们的股票系统创建一份报告，以显示我们是 in 股票还是OUT of OUT of products A&B& C。我们的股票分为两栏，分别是physical_qty和allocated_qty。

使用此查询，我可以从physical_qty中减去allocated_qty，并将其显示为stock

SELECT     
   warehouse, product, description, physical_qty - allocated_qty AS stock
FROM         
   scheme.stockm
WHERE     
   (warehouse = '01') AND (product IN ('A', 'B', 'C'))

这给了我一个我们可以出售的股票数量的数值。

如何评估股票是否<1，显示OUT，否则在中显示

谢谢

Answer 1

如下所示：

SELECT     warehouse, product, description, 
physical_qty - allocated_qty AS stock,
CASE WHEN (physical_qty - allocated_qty) < 1 THEN 'OUT'
ELSE 'IN' END AS Stock_Status
FROM         scheme.stockm
WHERE     (warehouse = '01') AND (product IN ('A', 'B', 'C'))

Answer 2

试一试

CASE WHEN physical_qty - allocated_qt < 1 THEN 'OUT' ELSE 'IN' END