使用TSQL查找无向图连通分量出现语法错误如何解决

你遇到的语法报错主要是以下几个问题导致的：

• 核心语法错误：INSERT 语句用法不合法。SQL 中VALUES关键字仅用于插入手动指定的常量值，如果你要通过子查询的结果批量插入数据，应该直接使用INSERT INTO 表名(列名) SELECT 查询语句的格式，不需要加VALUES和外层括号。你代码里一共有3处这样的错误写法，正好对应报错的3条记录。
• 字段名不匹配：你定义的nodes表字段为nodeID，edges表字段为fromNodeID、toNodeID，但代码里错误使用了不存在的paperID、citedPaperID字段。
• 无向图遍历逻辑遗漏：目前的遍历逻辑只查询了节点作为边起点的关联节点，没有查询作为边终点的关联节点，会导致连通分量查询结果不全。
• 其他语法缺陷：NOT IN (SELECT * FROM graphs) 子查询中应该指定具体列node而非*；最后插入graphs表时子查询返回多个节点，无法用单值的VALUES语法插入；循环内创建的临时表没有删除，第二次循环会报表已存在的错误。

CREATE PROCEDURE getGraph
AS BEGIN
    -- 提前清理可能存在的结果表
    IF OBJECT_ID('graphs', 'U') IS NOT NULL DROP TABLE graphs;
    CREATE TABLE graphs (node int, id int);
    DECLARE @graphID int;
    SET @graphID = 0;

    WHILE ((SELECT COUNT(*) FROM graphs) < (SELECT COUNT(*) FROM nodes))
    BEGIN
        CREATE TABLE currentGraph (node int);
        CREATE TABLE temp(node int);
        DECLARE @start int;
        -- 修正字段名，指定子查询列名
        SET @start = (SELECT TOP (1) n.nodeID
                      FROM nodes n
                      WHERE n.nodeID NOT IN (SELECT node FROM graphs));
        INSERT INTO currentGraph VALUES (@start);
        -- 去掉VALUES和括号，同时查无向图两个方向的边
        INSERT INTO temp (node)
            SELECT e.toNodeID
            FROM currentGraph cg JOIN edges e ON cg.node = e.fromNodeID
            UNION
            SELECT e.fromNodeID
            FROM currentGraph cg JOIN edges e ON cg.node = e.toNodeID;

        /* If there is any new node, keep searching for current graph*/
        WHILE ((SELECT COUNT(*)
                FROM temp
                WHERE temp.node NOT IN (SELECT node FROM currentGraph)) <> 0)
        BEGIN
            -- 去掉VALUES和括号
            INSERT INTO currentGraph (node)
                SELECT t.node
                FROM temp t
                WHERE t.node NOT IN (SELECT node FROM currentGraph);
            DELETE FROM temp;

            -- 去掉VALUES和括号，同时查无向图两个方向的边
            INSERT INTO temp (node)
                SELECT e.toNodeID
                FROM currentGraph cg JOIN edges e ON cg.node = e.fromNodeID
                UNION
                SELECT e.fromNodeID
                FROM currentGraph cg JOIN edges e ON cg.node = e.toNodeID;
        END
        SET @graphID = @graphID + 1;
        -- 去掉VALUES和括号，改成INSERT SELECT格式
        INSERT INTO graphs (node, id)
            SELECT DISTINCT cg.node, @graphID FROM currentGraph cg;
        -- 清理当前循环的临时表，避免下次循环报错
        DROP TABLE currentGraph;
        DROP TABLE temp;
    END
END

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