SQL EXISTS语句执行报错，请求分析原因并提供修正方案

错误原因 #

你写的EXISTS子句存在语法错误：EXISTS要求括号内是完整的SELECT子查询，但你直接将两个条件写在括号里，没有包含SELECT语句。SQL Server会将这种写法误判为非EXISTS引导的子查询，这类子查询要求只能返回单个表达式，因此抛出错误：

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

你的原意是筛选出满足ft.vendnm LIKE '%rocha%'且pn.fno = ft.fno的关联数据，但错误的EXISTS用法导致解析失败。

修正方案 #

针对你最初的测试SQL，有两种正确写法：

方式1：正确使用EXISTS子句 #

将条件放入EXISTS的子查询中，通过关联字段匹配：

SELECT *
FROM ft, pn
WHERE EXISTS (
    SELECT 1 -- EXISTS仅关心子查询是否有结果，SELECT 1是性能最优的写法
    FROM ft ft_sub
    WHERE ft_sub.vendnm LIKE '%rocha%'
      AND ft_sub.fno = pn.fno
      AND ft_sub.fno = ft.fno -- 关联外层的ft表
)

方式2：直接使用JOIN+WHERE条件（更直观） #

如果你的需求是获取ft和pn关联且ft.vendnm符合条件的数据，用INNER JOIN逻辑更清晰：

SELECT *
FROM ft
INNER JOIN pn ON ft.fno = pn.fno
WHERE ft.vendnm LIKE '%rocha%'

针对你的完整SQL的优化建议 #

你的完整SQL已经通过WHERE x.Vendnm LIKE '%rocha%'筛选了目标数据，但存在大量重复子查询，建议用CTE（公共表表达式）简化代码，提升可读性和执行性能：

修正后的完整SQL #

WITH pn_filtered AS (
    SELECT 
        pn.no,
        pn.etiliquido,
        pn.fdata,
        pn.vendedor
    FROM pn
    WHERE 
        pn.usr1 NOT LIKE '%Portes%'
        AND pn.ref <> ''
        AND pn.usr1 NOT IN ('12-DAS','13-DIDO','19-GIOTTO','19-GIOTTO BE-BÈ','10-ADIGRAF','27-OMYACOLOR','28-PONGO', '18-FILA', '36-TRATTO','48 - LYRA')
        AND pn.REF NOT IN ('DESC. FINANCEIRO','PORTES','DESP.BANCARIAS','COL. COM.','VENDA VIAT CI','RESP.TRANSPORTE','RECIGLAGEM','MARK. CONT. 2013','RAPPEL13','JUROS','VENDA VIAT SI','CREDITO SRCM','RAPPEL14','FEIRAS','MARK. CONT. 2014','MARK. CONT. 2015','RAPPEL15','RAPPEL16','MARK. CONT. 2016','RAPPEL17','MARK. CONT. 2017','RAPPEL18','MARK. CONT. 2018','RAPPEL19','MARK. CONT. 2019','MARK. CONT. 2020','RAPPEL20','MARK. CONT. 2021','RAPPEL21','MARK. CONT. 2022','RAPPEL22')
),
client_data AS (
    SELECT 
        cl.no,
        cl.nome AS Cliente,
        cl.zona AS Zona,
        cl.vendnm
    FROM cl
    WHERE cl.vendedor = 3
),
yearly_totals AS (
    SELECT
        SUM(CASE WHEN pf.fdata BETWEEN '2021-01-01' AND '2021-12-31' THEN pf.etiliquido ELSE 0 END) AS total_2021,
        SUM(CASE WHEN pf.fdata BETWEEN '2022-01-01' AND '2022-12-31' THEN pf.etiliquido ELSE 0 END) AS total_2022
    FROM pn_filtered pf
    JOIN client_data cd ON pf.no = cd.no
),
vt_totals AS (
    SELECT
        SUM(CASE WHEN pf.fdata BETWEEN '2021-01-01' AND '2021-12-31' THEN pf.etiliquido ELSE 0 END) AS VT1,
        SUM(CASE WHEN pf.fdata BETWEEN '2022-01-01' AND '2022-12-31' THEN pf.etiliquido ELSE 0 END) AS VT2
    FROM pn_filtered pf
    WHERE pf.vendedor = 3
)
SELECT 
    x.Num,
    x.Cliente,
    x.Zona,
    SUM(x.valor_1) AS Valor_1,
    SUM(x.valor_2) AS Valor_2,
    x.Vendnm,
    '2021-01-01 00:00:00.000' AS Data1,
    '2021-12-31 00:00:00.000' AS Data2,
    '2022-01-01 00:00:00.000' AS Data3,
    '2022-12-31 00:00:00.000' AS Data4,
    (SELECT VT1 FROM vt_totals) AS VT1,
    (SELECT VT2 FROM vt_totals) AS VT2,
    ISNULL(ROUND((yt.total_2022 - yt.total_2021) * 100, 2), 0) AS Diftotal,
    ISNULL(ROUND(CASE WHEN yt.total_2021 <> 0 THEN (yt.total_2022 - yt.total_2021) * 100.0 / yt.total_2021 ELSE 0 END, 2), 0) AS Perc_dif
FROM (
    SELECT
        pf.no AS Num,
        cd.Cliente,
        cd.Zona,
        SUM(pf.etiliquido) AS Valor_1,
        0 AS Valor_2,
        cd.vendnm
    FROM pn_filtered pf
    JOIN client_data cd ON pf.no = cd.no
    WHERE pf.fdata BETWEEN '2021-01-01' AND '2021-12-31'
    GROUP BY pf.no, cd.Cliente, cd.Zona, cd.vendnm

    UNION ALL

    SELECT 
        pf.no AS Num,
        cd.Cliente,
        cd.Zona,
        0 AS Valor_1,
        SUM(pf.etiliquido) AS Valor_2,
        cd.vendnm
    FROM pn_filtered pf
    JOIN client_data cd ON pf.no = cd.no
    WHERE pf.fdata BETWEEN '2022-01-01' AND '2022-12-31'
    GROUP BY pf.no, cd.Cliente, cd.Zona, cd.vendnm
) x
WHERE x.Vendnm LIKE '%rocha%'
GROUP BY x.Num, x.Zona, x.Cliente, x.Vendnm
CROSS JOIN yearly_totals yt
ORDER BY Valor_2 DESC;

优化说明 #

• 用CTE提取重复的筛选条件，避免多次编写相同的WHERE子句
• 合并重复的SUM计算，减少数据库重复扫描，提升查询性能
• 替换嵌套子查询为JOIN，增强代码可读性
• 增加除以0的判断逻辑，避免运行时出现除数为0的错误

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