SQL查询语法错误排查：提示‘SQL命令未正确结束’求解决

错误核心原因 #

• 派生表作用域限制：你定义的派生表g0仅在它直接所属的外层查询（最外层FROM子句）中有效，无法在WHERE IN的嵌套子查询里直接作为表名引用。数据库会将g0识别为需要查找的物理表，找不到时就抛出"SQL command not properly ended"错误（不同数据库错误提示可能略有差异，但本质是作用域问题）。

修复方案 #

方案1：重复派生表逻辑（简单直接） #

将g0的派生逻辑在嵌套子查询中重复书写，让数据库能识别查询对象：

SELECT 
g0.ARCHIVE_PERIOD,g0.PROJECT_OWNER,g0.CONTRACT_NUMBER
FROM 
(
        SELECT ROW_NUMBER() OVER (
        ORDER BY ARCHIVE_PERIOD
        ) ID,
        ARCHIVE_PERIOD,
        PROJECT_OWNER,
        CONTRACT_NUMBER
        FROM ARC_WORK_CONTRACT_GENERAL WHERE CONTRACT_NUMBER='whatever number u want'
    )AS g0
WHERE g0.id IN
(
    SELECT 
       g2.id
    FROM
        (
            SELECT ROW_NUMBER() OVER (ORDER BY ARCHIVE_PERIOD) ID,
            ARCHIVE_PERIOD, PROJECT_OWNER, CONTRACT_NUMBER
            FROM ARC_WORK_CONTRACT_GENERAL WHERE CONTRACT_NUMBER='whatever number u want'
        ) g1
            INNER JOIN
        (
            SELECT ROW_NUMBER() OVER (ORDER BY ARCHIVE_PERIOD) ID,
            ARCHIVE_PERIOD, PROJECT_OWNER, CONTRACT_NUMBER
            FROM ARC_WORK_CONTRACT_GENERAL WHERE CONTRACT_NUMBER='whatever number u want'
        ) g2 ON g2.id=g1.id+1 AND g1.PROJECT_OWNER!=g2.PROJECT_OWNER AND g1.CONTRACT_NUMBER=g2.CONTRACT_NUMBER
)

方案2：使用CTE（公共表表达式，推荐） #

用CTE定义一次派生逻辑，可在整个查询中多次引用，解决作用域问题，代码更易维护：

WITH g0 AS (
    SELECT ROW_NUMBER() OVER (ORDER BY ARCHIVE_PERIOD) ID,
           ARCHIVE_PERIOD,
           PROJECT_OWNER,
           CONTRACT_NUMBER
    FROM ARC_WORK_CONTRACT_GENERAL 
    WHERE CONTRACT_NUMBER='whatever number u want'
)
SELECT 
ARCHIVE_PERIOD, PROJECT_OWNER, CONTRACT_NUMBER
FROM g0
WHERE id IN (
    SELECT g2.id
    FROM g0 g1
    INNER JOIN g0 g2 
        ON g2.id = g1.id + 1 
        AND g1.PROJECT_OWNER != g2.PROJECT_OWNER 
        AND g1.CONTRACT_NUMBER = g2.CONTRACT_NUMBER
)

方案3：用LAG/LEAD窗口函数优化（高效实现需求） #

你的需求是对比连续行的项目所有者差异，用窗口函数LAG/LEAD可直接在一次表扫描中完成，无需自连接，性能更优：

SELECT 
    ARCHIVE_PERIOD,
    PROJECT_OWNER,
    CONTRACT_NUMBER
FROM (
    SELECT 
        ARCHIVE_PERIOD,
        PROJECT_OWNER,
        CONTRACT_NUMBER,
        -- 获取上一行的项目所有者
        LAG(PROJECT_OWNER) OVER (ORDER BY ARCHIVE_PERIOD) AS PREV_OWNER,
        -- 获取下一行的项目所有者
        LEAD(PROJECT_OWNER) OVER (ORDER BY ARCHIVE_PERIOD) AS NEXT_OWNER
    FROM ARC_WORK_CONTRACT_GENERAL 
    WHERE CONTRACT_NUMBER='whatever number u want'
) t
-- 按需选择条件：
-- 若要输出"所有者变化后的行"，用 WHERE NEXT_OWNER != PROJECT_OWNER
-- 若要输出"所有发生变化的行（包括变化前一行）"，用 WHERE PREV_OWNER != PROJECT_OWNER OR NEXT_OWNER != PROJECT_OWNER
WHERE PREV_OWNER != PROJECT_OWNER OR NEXT_OWNER != PROJECT_OWNER

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