SQL Server查询报'Incorrect syntax near the keyword 'GROUP''错误求助

咱们先看触发错误的核心原因：你把HAVING子句放在了GROUP BY前面，这完全违反了SQL的语法规则——HAVING是用来过滤分组后的结果集的，必须紧跟在GROUP BY之后。除此之外，你的查询还有几个小问题需要修正，我帮你梳理并给出修正后的版本：

具体问题点： #

• HAVING与GROUP BY顺序颠倒：这是直接触发语法错误的原因，必须把GROUP BY放在HAVING之前。
• ORDER BY语法错误：ORDER BY len ALLGENEDX.[Chr]里的函数调用缺少括号，正确写法是LEN(ALLGENEDX.[Chr])，同时列名最好明确归属表。
• 冗余且不完整的代码：你的查询里重复粘贴了整个SELECT块，最后一段还没写完（ALLGENEDX.Func#refGe），需要清理掉这部分冗余内容。
• 旧式JOIN写法：用逗号分隔表的写法容易混淆逻辑，建议改用显式INNER JOIN，让关联关系更清晰。

修正后的SQL语句： #

/****** Script for SelectTopNRows command from SSMS ******/
SELECT 
    ALLGENEDX.[id],
    ALLGENEDX.[Chr],
    ALLGENEDX.[Start],
    ALLGENEDX.[End],
    ALLGENEDX.[ref],
    ALLGENEDX.[alt],
    ALLGENEDX.[Func#refGene],
    ALLGENEDX.[Gene#refGene],
    ALLGENEDX.[Otherinfo],
    CASE 
        WHEN OTHERINFO = 'HOM' THEN 
            CASE WHEN PARENT_OTHERINFO = 'HET' THEN 1 ELSE 0 END
        ELSE 
            CASE WHEN PARENT_OTHERINFO <> 'HOM' AND PARENT_AMMOUNT > 1 THEN 1 ELSE 0 END
    END AS 'PARENT_INFO',
    CASE 
        WHEN OTHERINFO = 'HOM' THEN 
            CASE WHEN SIBLING_OTHERINFO = 'HET' THEN 1 ELSE 0 END
        ELSE 
            CASE WHEN SIBLING_OTHERINFO <> 'HOM' AND (SIBLING_AMMOUNT = 1 OR SIBLING_AMMOUNT IS NULL) THEN 1 ELSE 2 END
    END AS 'SIBLING_INFO'
FROM [Exome Data].[dbo].[ALLGENEDX]
INNER JOIN (
    SELECT 
        [ID], [Chr], [Start], [end], [REF], [ALT], [Func#refGene], [GENE#REFGENE],
        [OTHERINFO] AS PARENT_OTHERINFO,
        COUNT(ID) OVER (PARTITION BY [Chr], [Start], [End], [REF], [ALT], [Func#refGene], [GENE#REFGENE], [OTHERINFO]) AS PARENT_AMMOUNT
    FROM [Exome Data].DBO.ALLGENEDX 
    WHERE ID IN ('1646304GD', '1646307GD')
) AS PARENT 
    ON PARENT.[CHR] = ALLGENEDX.Chr 
    AND PARENT.[START] = ALLGENEDX.[START] 
    AND PARENT.[END] = ALLGENEDX.[END] 
    AND PARENT.[REF] = ALLGENEDX.[REF] 
    AND PARENT.[ALT] = ALLGENEDX.[ALT] 
    AND PARENT.[GENE#REFGENE] = ALLGENEDX.[GENE#REFGENE]
INNER JOIN (
    SELECT 
        [id], [Chr], [Start], [end], [REF], [ALT], [Func#refGene], [GENE#REFGENE],
        [OTHERINFO] AS SIBLING_OTHERINFO,
        COUNT(ID) OVER (PARTITION BY [Chr], [Start], [end], [REF], [ALT], [Func#refGene], [GENE#REFGENE], [OTHERINFO]) AS SIBLING_AMMOUNT
    FROM [Exome Data].DBO.ALLGENEDX 
    WHERE ID IN ('1646310GD', '1646314GD', '1646317GD', '1646320GD', '1646325GD')
) AS SIBLING 
    ON SIBLING.[CHR] = ALLGENEDX.Chr 
    AND SIBLING.[START] = ALLGENEDX.[START] 
    AND SIBLING.[END] = ALLGENEDX.[END] 
    AND SIBLING.[REF] = ALLGENEDX.[REF] 
    AND SIBLING.[ALT] = ALLGENEDX.[ALT] 
    AND SIBLING.[GENE#REFGENE] = ALLGENEDX.[GENE#REFGENE]
WHERE 
    ALLGENEDX.ID = '1646299GD' 
    AND ALLGENEDX.Func#refGene = 'exonic' 
    AND (EXAC_FREQ < '0.005' OR EXAC_FREQ IS NULL) 
    AND ([1000G_ALL] < '0.005' OR [1000G_ALL] IS NULL) 
    AND (GNOMAD_EXOME_ALL < '0.005' OR GNOMAD_EXOME_ALL IS NULL)
GROUP BY 
    ALLGENEDX.[id],
    ALLGENEDX.[Chr],
    ALLGENEDX.[Start],
    ALLGENEDX.[End],
    ALLGENEDX.[ref],
    ALLGENEDX.[alt],
    ALLGENEDX.[Func#refGene],
    ALLGENEDX.[Gene#refGene],
    ALLGENEDX.[Otherinfo],
    PARENT.[PARENT_OTHERINFO],
    SIBLING.[SIBLING_OTHERINFO]
HAVING 
    (CASE 
        WHEN OTHERINFO = 'HOM' THEN 
            CASE WHEN PARENT_OTHERINFO = 'HET' THEN 1 ELSE 0 END
        ELSE 
            CASE WHEN PARENT_OTHERINFO <> 'HOM' AND PARENT_AMMOUNT > 1 THEN 1 ELSE 0 END
    END > 0)
    OR
    (CASE 
        WHEN OTHERINFO = 'HOM' THEN 
            CASE WHEN SIBLING_OTHERINFO = 'HET' THEN 1 ELSE 0 END
        ELSE 
            CASE WHEN SIBLING_OTHERINFO <> 'HOM' AND (SIBLING_AMMOUNT = 1 OR SIBLING_AMMOUNT IS NULL) THEN 1 ELSE 2 END
    END > 0)
ORDER BY 
    LEN(ALLGENEDX.[Chr]),
    ALLGENEDX.Chr,
    ALLGENEDX.Start;

额外说明： #

我把旧式的逗号JOIN改成了显式INNER JOIN，这样表之间的关联逻辑更直观，也能避免不小心写出笛卡尔积。另外，在HAVING里给两个CASE表达式加了括号，让逻辑分组更清晰，可读性更好。

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