SQL中过滤别名列FamiliaOrigen报错，求正确实现方法

报错说明 #

你编写的SQL执行时出现以下错误：

FamiliaOrigen name is not valid

原因是SQL执行顺序限制：SELECT子句中定义的列别名，无法直接在WHERE或HAVING子句中使用（HAVING仅支持聚合函数或GROUP BY中包含的列）。FamiliaOrigen是SELECT里的子查询生成的别名，在WHERE/HAVING阶段还未被解析，因此报错。

解决方案 #

方案1：使用CTE（通用表达式）先计算结果，再过滤 #

先通过CTE生成包含FamiliaOrigen的数据集，再在外层WHERE中过滤非空值：

WITH CTE_Resultado AS (
    SELECT DISTINCT
        PA.Serie + '/' + CONVERT(varchar, PA.Numero) Partida,
        PA.Id_Cultivo,
        (SELECT _FA.Nombre AS Familia
         FROM FamiliasArticulo _FA
         INNER JOIN Articulos _A ON _A.Id_Familia = _FA.Id
                                 AND _A.Id = APNS.Id_Articulo
         WHERE _FA.Id_FamiliaSuperior IS NOT NULL) AS FamiliaOrigen
    FROM
        AlbaranesVenta as AV
    INNER JOIN
        LineasAlbaranVenta AS LAV ON LAV.Id_AlbaranVenta = AV.Id
    INNER JOIN
        LineasAlbaranVentaArticulo AS LAVA ON LAVA.Id_LineaAlbaranVenta = LAV.id
    INNER JOIN
        Articulos Producto ON Producto.Id = LAVA.Id_Articulo
                           AND Producto.TipoArticulo = 2
    INNER JOIN
        LineasAlbaranVentaComercializacionArticulo LAVCA ON LAVCA.Id_LineaAlbaranVentaArticulo = LAV.Id
    INNER JOIN
        LineasAlbaranVentaComercializacionArticulo_NumerosSerie LAVCA_NS ON LAVCA_NS.Id_LineasAlbaranVentaComercializacionArticulo = LAV.Id
    INNER JOIN
        ArticulosNumeroSerieComposicionVariable ANSCV ON ANSCV.ID = LAVCA_NS.id_numerosserie
    INNER JOIN
        ArticulosNumerosSerie ANS ON ANS.Id = ANSCV.Id_ArticuloNumeroSerie
    INNER JOIN 
        ArticulosNumeroSerieComposicionVariable_DatosOrigen ANSCV_DO ON ANSCV_DO.Id_ArticulosNumeroSerieComposicionVariable = ANSCV.Id
    FULL JOIN
        ArticulosPartida APNS ON APNS.Id = ANSCV_DO.Id_DatosOrigen
    INNER JOIN
        Partidas PA ON PA.Id = APNS.Id_Partida
                    AND PA.Id_PartidaSectorAgrario IS NOT NULL
)
SELECT Partida, Id_Cultivo, FamiliaOrigen
FROM CTE_Resultado
WHERE FamiliaOrigen IS NOT NULL;

方案2：用EXISTS子句替代SELECT中的子查询，直接在WHERE过滤 #

把FamiliaOrigen的判断逻辑移到WHERE的EXISTS中，确保过滤逻辑在SELECT之前执行：

SELECT DISTINCT
    PA.Serie + '/' + CONVERT(varchar, PA.Numero) Partida,
    PA.Id_Cultivo,
    (SELECT _FA.Nombre AS Familia
     FROM FamiliasArticulo _FA
     INNER JOIN Articulos _A ON _A.Id_Familia = _FA.Id
                             AND _A.Id = APNS.Id_Articulo
     WHERE _FA.Id_FamiliaSuperior IS NOT NULL) AS FamiliaOrigen
FROM
    AlbaranesVenta as AV
INNER JOIN
    LineasAlbaranVenta AS LAV ON LAV.Id_AlbaranVenta = AV.Id
INNER JOIN
    LineasAlbaranVentaArticulo AS LAVA ON LAVA.Id_LineaAlbaranVenta = LAV.id
INNER JOIN
    Articulos Producto ON Producto.Id = LAVA.Id_Articulo
                       AND Producto.TipoArticulo = 2
INNER JOIN
    LineasAlbaranVentaComercializacionArticulo LAVCA ON LAVCA.Id_LineaAlbaranVentaArticulo = LAV.Id
INNER JOIN
    LineasAlbaranVentaComercializacionArticulo_NumerosSerie LAVCA_NS ON LAVCA_NS.Id_LineasAlbaranVentaComercializacionArticulo = LAV.Id
INNER JOIN
    ArticulosNumeroSerieComposicionVariable ANSCV ON ANSCV.ID = LAVCA_NS.id_numerosserie
INNER JOIN
    ArticulosNumerosSerie ANS ON ANS.Id = ANSCV.Id_ArticuloNumeroSerie
INNER JOIN 
    ArticulosNumeroSerieComposicionVariable_DatosOrigen ANSCV_DO ON ANSCV_DO.Id_ArticulosNumeroSerieComposicionVariable = ANSCV.Id
FULL JOIN
    ArticulosPartida APNS ON APNS.Id = ANSCV_DO.Id_DatosOrigen
INNER JOIN
    Partidas PA ON PA.Id = APNS.Id_Partida
                AND PA.Id_PartidaSectorAgrario IS NOT NULL
WHERE EXISTS (
    SELECT 1
    FROM FamiliasArticulo _FA
    INNER JOIN Articulos _A ON _A.Id_Familia = _FA.Id
                            AND _A.Id = APNS.Id_Articulo
    WHERE _FA.Id_FamiliaSuperior IS NOT NULL
);

方案3：将子查询转换为JOIN，优化性能 #

把生成FamiliaOrigen的子查询改为INNER JOIN，既能直接过滤非空值，还可能提升查询性能：

SELECT DISTINCT
    PA.Serie + '/' + CONVERT(varchar, PA.Numero) Partida,
    PA.Id_Cultivo,
    _FA.Nombre AS FamiliaOrigen
FROM
    AlbaranesVenta as AV
INNER JOIN
    LineasAlbaranVenta AS LAV ON LAV.Id_AlbaranVenta = AV.Id
INNER JOIN
    LineasAlbaranVentaArticulo AS LAVA ON LAVA.Id_LineaAlbaranVenta = LAV.id
INNER JOIN
    Articulos Producto ON Producto.Id = LAVA.Id_Articulo
                       AND Producto.TipoArticulo = 2
INNER JOIN
    LineasAlbaranVentaComercializacionArticulo LAVCA ON LAVCA.Id_LineaAlbaranVentaArticulo = LAV.Id
INNER JOIN
    LineasAlbaranVentaComercializacionArticulo_NumerosSerie LAVCA_NS ON LAVCA_NS.Id_LineasAlbaranVentaComercializacionArticulo = LAV.Id
INNER JOIN
    ArticulosNumeroSerieComposicionVariable ANSCV ON ANSCV.ID = LAVCA_NS.id_numerosserie
INNER JOIN
    ArticulosNumerosSerie ANS ON ANS.Id = ANSCV.Id_ArticuloNumeroSerie
INNER JOIN 
    ArticulosNumeroSerieComposicionVariable_DatosOrigen ANSCV_DO ON ANSCV_DO.Id_ArticulosNumeroSerieComposicionVariable = ANSCV.Id
FULL JOIN
    ArticulosPartida APNS ON APNS.Id = ANSCV_DO.Id_DatosOrigen
INNER JOIN
    Partidas PA ON PA.Id = APNS.Id_Partida
                AND PA.Id_PartidaSectorAgrario IS NOT NULL
INNER JOIN Articulos _A ON _A.Id = APNS.Id_Articulo
INNER JOIN FamiliasArticulo _FA ON _A.Id_Familia = _FA.Id
                               AND _FA.Id_FamiliaSuperior IS NOT NULL;

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