SQL数据提取需求：有负值取首负行，无负值取末正行并重构表

库存数据表 #

需求 #

按产品、工厂、仓库分组处理：

• 若组内存在库存负值，提取首个出现负值的行
• 若组内无库存负值，提取最后一个正值的行
• 最终结果需重构为包含 Date_Start、Stock_Start、Date_Finished、Stock_Finished 等字段的结构

尝试过的SQL及错误 #

1. 筛选负值后取首行

SELECT Product,Plant,Store,Text,Date, Stock FROM table WHERE (Stock)<0 order by Product,Plant,Store,Text,Date, Stock asc

添加 FETCH FIRST 1 ROWS ONLY 时报错：

ERROR: Syntax error at or near "FIRST"

注：该语法仅部分数据库（如DB2、PostgreSQL 9.5+）支持，MySQL需用 LIMIT 1，SQL Server用 TOP 1，语法不匹配导致报错

2. 用窗口函数标记行号

WITH added_row_number AS (
 SELECT *,
 ROW_NUMBER() OVER(PARTITION BY Product,Plant,Store ORDER BY Date ASC) AS row_number
 FROM MM_STOCK
)
SELECT * FROM added_row_number WHERE (Stock)<0 AND row_number = 1;

添加CASE语句时报错：

ERROR: Syntax error at or near "CASE"

注：大概率是CASE语句语法格式错误，比如缺少END、条件表达式不完整，或放置位置不符合语法规则

解决方案 #

通用SQL实现（兼容多数数据库） #

通过窗口函数分别标记负值的首次出现和正值的末次出现，再筛选目标行并重构字段：

WITH ranked_stock AS (
    SELECT 
        Product,
        Plant,
        Store,
        Text,
        Date,
        Stock,
        -- 标记组内首个负值行（按日期升序，负值行的行号为1）
        ROW_NUMBER() OVER(
            PARTITION BY Product, Plant, Store 
            ORDER BY CASE WHEN Stock < 0 THEN Date END ASC, Date DESC
        ) AS neg_rank,
        -- 标记组内最后一个正值行（按日期降序，正值行的行号为1）
        ROW_NUMBER() OVER(
            PARTITION BY Product, Plant, Store 
            ORDER BY CASE WHEN Stock >= 0 THEN Date END DESC, Date ASC
        ) AS pos_rank
    FROM MM_STOCK
)
SELECT 
    Product,
    Plant,
    Store,
    Text,
    Date AS Date_Start,
    Stock AS Stock_Start,
    -- 取同组下一行数据作为Finished字段，可按需调整逻辑
    LEAD(Date) OVER(PARTITION BY Product, Plant, Store ORDER BY Date) AS Date_Finished,
    LEAD(Stock) OVER(PARTITION BY Product, Plant, Store ORDER BY Date) AS Stock_Finished
FROM ranked_stock
WHERE 
    -- 优先取首个负值行，若无则取最后一个正值行
    (neg_rank = 1 AND Stock < 0) 
    OR (neg_rank > 1 AND pos_rank = 1)

针对MySQL 8.0以下版本的兼容方案 #

若数据库不支持窗口函数，改用子查询实现：

SELECT 
    t1.Product,
    t1.Plant,
    t1.Store,
    t1.Text,
    t1.Date AS Date_Start,
    t1.Stock AS Stock_Start,
    t2.Date AS Date_Finished,
    t2.Stock AS Stock_Finished
FROM MM_STOCK t1
LEFT JOIN MM_STOCK t2 
    ON t1.Product = t2.Product 
    AND t1.Plant = t2.Plant 
    AND t1.Store = t2.Store 
    AND t2.Date = (SELECT MIN(Date) FROM MM_STOCK WHERE Product = t1.Product AND Plant = t1.Plant AND Store = t1.Store AND Date > t1.Date)
WHERE 
    (t1.Stock < 0 AND t1.Date = (SELECT MIN(Date) FROM MM_STOCK WHERE Product = t1.Product AND Plant = t1.Plant AND Store = t1.Store AND Stock < 0))
    OR 
    (NOT EXISTS (SELECT 1 FROM MM_STOCK WHERE Product = t1.Product AND Plant = t1.Plant AND Store = t1.Store AND Stock < 0) AND t1.Date = (SELECT MAX(Date) FROM MM_STOCK WHERE Product = t1.Product AND Plant = t1.Plant AND Store = t1.Store))

错误原因总结 #

1. FETCH FIRST 语法报错：不同数据库分页语法差异导致，需根据使用的数据库调整分页关键字。
2. CASE语句报错：多为语法格式问题，比如缺少END、条件不完整，或放置位置不符合SQL语法规则。

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