修改SQL查询新增库存预警remarks列

首先修正原查询中的笔误（CTE中select product应为product_cat，与分组字段保持一致），并新增remarks列实现库存预警逻辑：

WITH stock AS (
    SELECT product_cat, product_code,
           SUM(CASE WHEN transact_type = 'Purchases' THEN qty ELSE 0 END) AS Purchases,
           SUM(CASE WHEN transact_type = 'Sales' THEN qty ELSE 0 END) AS Sales
    FROM stockcheck.sql_transactions 
    GROUP BY product_cat, product_code 
    ORDER BY product_cat
)
SELECT stock.product_code, 
       stock.product_cat, 
       stock.Purchases, 
       stock.Sales, 
       (Purchases - Sales) AS Balance,
       sql_product_tbl.mini_qty_alert,
       CASE 
           WHEN (Purchases - Sales) < sql_product_tbl.mini_qty_alert THEN '立即下单'
           ELSE ''
       END AS remarks
FROM stock
LEFT JOIN stockcheck.sql_product_tbl
ON stock.product_code = sql_product_tbl.product_code

执行结果示例： #

内容的提问来源于stack exchange，提问作者Kobby Adom