求助：将指定Excel公式转换为SQL语法计算

First, let's fill in the missing part of your Excel formula (I'm guessing the else clause returns 0, which is standard for calculating excess stock):

=IF(
([@[Net Available Stock Quantity]] + [@Subcon] + [@[In Progress WIP]] +
IFERROR([@[Raw Material In Store]]/[@[Demand Material]], 0) +
IFERROR([@[Outstanding Raw Material]]/[@[Demand Material]], 0) +
[@[Outstanding WIP]]) > [@[Maximun Stock]],
([@[Net Available Stock Quantity]] + [@Subcon] + [@[In Progress WIP]] +
IFERROR([@[Raw Material In Store]]/[@[Demand Material]], 0) +
IFERROR([@[Outstanding Raw Material]]/[@[Demand Material]], 0) +
[@[Outstanding WIP]]) - [@[Maximun Stock]],
0
)

Quick Translation Cheat Sheet #

• Excel's IF maps to SQL's CASE statement (it's more universally supported than database-specific IF functions).
• Excel's IFERROR(value, 0) becomes COALESCE(value, 0) in standard SQL (use IFNULL if you're working with MySQL/MariaDB).

Standard SQL Version #

Here's the direct conversion that works in most databases (PostgreSQL, SQL Server, etc.):

SELECT
  -- First, calculate the total available stock (optional, but helpful for debugging)
  (
    "Net Available Stock Quantity" + 
    "Subcon" + 
    "In Progress WIP" + 
    COALESCE("Raw Material In Store" / "Demand Material", 0) + 
    COALESCE("Outstanding Raw Material" / "Demand Material", 0) + 
    "Outstanding WIP"
  ) AS total_available_stock,
  -- The core IF logic translated to CASE
  CASE
    WHEN (
      "Net Available Stock Quantity" + 
      "Subcon" + 
      "In Progress WIP" + 
      COALESCE("Raw Material In Store" / "Demand Material", 0) + 
      COALESCE("Outstanding Raw Material" / "Demand Material", 0) + 
      "Outstanding WIP"
    ) > "Maximun Stock" THEN (
      "Net Available Stock Quantity" + 
      "Subcon" + 
      "In Progress WIP" + 
      COALESCE("Raw Material In Store" / "Demand Material", 0) + 
      COALESCE("Outstanding Raw Material" / "Demand Material", 0) + 
      "Outstanding WIP"
    ) - "Maximun Stock"
    ELSE 0
  END AS excess_stock
FROM your_table_name; -- Replace with your actual table name

Pro Tip: Avoid Repetition #

That long calculation is repeated twice in the CASE statement, which is messy and inefficient. Use a CTE (Common Table Expression) to compute it once:

WITH stock_totals AS (
  SELECT
    *,
    (
      "Net Available Stock Quantity" + 
      "Subcon" + 
      "In Progress WIP" + 
      COALESCE("Raw Material In Store" / "Demand Material", 0) + 
      COALESCE("Outstanding Raw Material" / "Demand Material", 0) + 
      "Outstanding WIP"
    ) AS total_available_stock
  FROM your_table_name
)
SELECT
  total_available_stock,
  CASE
    WHEN total_available_stock > "Maximun Stock" THEN total_available_stock - "Maximun Stock"
    ELSE 0
  END AS excess_stock
FROM stock_totals;

Database-Specific Adjustments #

• MySQL/MariaDB: Swap COALESCE for IFNULL (though COALESCE does work here too). Also, use backticks (`) instead of double quotes for column names with spaces, like `Net Available Stock Quantity`.
• Division by Zero: The COALESCE/IFNULL handles cases where Demand Material is zero, just like Excel's IFERROR does.

内容的提问来源于stack exchange，提问作者S. Ideal