SELECT FactoryName, JiJXH, shi, AVG(sale) AS sale_avg FROM tmp_apply WHERE (FactoryName, JiJXH) IN ( SELECT FactoryName, JiJXH ,SUM(ShuL) ShuL_total FROM tmp_apply GROUP BY FactoryName, JiJXH HAVING SUM(ShuL) > 500 ) GROUP BY FactoryName, JiJXH, shi;提示 [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]在应使用条件的上下文(在 ',' 附近)中指定了非布尔类型的表达式。 (4145) [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]关键字 'GROUP' 附近有语法错误。

根据错误提示，这个问题可能与你的 IN 子查询中的条件表达式有关，因为在逗号附近指定了非布尔类型的表达式。

尝试以下 SQL 语句，它使用 EXISTS 子查询代替了 IN 子查询。这样更加简洁和易于理解，同时可以避免上述错误。

SELECT FactoryName, JiJXH, shi, AVG(sale) AS sale_avg  
FROM tmp_apply  
WHERE EXISTS (
  SELECT 1 
  FROM tmp_apply AS t 
  WHERE tmp_apply.FactoryName = t.FactoryName 
    AND tmp_apply.JiJXH = t.JiJXH 
  GROUP BY t.FactoryName, t.JiJXH 
  HAVING SUM(t.ShuL) > 500
) 
GROUP BY FactoryName, JiJXH, shi;

这个修改后的 SQL 语句中，我使用 EXISTS 子查询来代替了 IN 子查询，并且在子查询中指定了需要查询的条件。同时，我也对 AVG 函数的结果添加了一个别名，使查询结果更具可读性。

相关问题

SELECT FactoryName,JiJXH,shi,AVG(sale) sale_avg FROM tmp_apply WHERE (FactoryName,JiJXH) IN (SELECT FactoryName,JiJXH FROM tmp_apply GROUP BY FactoryName,JiJXH HAVING SUM(ShuL)>500) GROUP BY FactoryName,JiJXH,shi怎么修改

你的 SQL 语句看起来是想要查询 tmp_apply 表中，工厂名和机型号在满足某个条件的情况下的销售平均值。如果我理解得没错的话，你可以尝试这样修改你的 SQL 语句：

SELECT FactoryName, JiJXH, shi, AVG(sale) AS sale_avg 
FROM tmp_apply 
WHERE (FactoryName, JiJXH) IN (
  SELECT FactoryName, JiJXH 
  FROM tmp_apply 
  GROUP BY FactoryName, JiJXH 
  HAVING SUM(ShuL) > 500
) 
GROUP BY FactoryName, JiJXH, shi;

这个修改后的 SQL 语句与你的原始语句相比，只在最后加了一个分号，同时对 AVG 函数的结果添加了一个别名，使查询结果更具可读性。

SELECT shi,Xian,Name,XieYBH,ShuL,GuoJBTE,ShengBTE,sale,mode_sale,FactoryName,BusinessName,ChanPMC,JiJXH,GouJRQ FROM(tmp_apply t1 JOIN ( SELECT FactoryName, ChanPMC, JiJXH, sale AS mode_sale FROM ( SELECT FactoryName, ChanPMC, JiJXH, sale, ROW_NUMBER() OVER (PARTITION BY FactoryName, ChanPMC, JiJXH ORDER BY COUNT(*) DESC) AS rn FROM tmp_apply GROUP BY FactoryName, ChanPMC, JiJXH, sale ) t WHERE rn = 1) t2 ON t1.FactoryName=t1.FactoryName AND t1.ChanPMC=t2.ChanPMC AND t1.JiJXH=t2.JiJXH WHERE ABS(sale-mode_sale)>5; 提示> [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]关键字 'WHERE' 附近有语法错误。

这条 SQL 查询语句存在语法错误，问题在于 `FROM` 关键字后面的表格名 `tmp_apply` 应该与括号之间留一个空格，即应该写成 `FROM (tmp_apply t1 JOIN ...`。因此，正确的查询语句应该是：

SELECT shi,Xian,Name,XieYBH,ShuL,GuoJBTE,ShengBTE,sale,mode_sale,FactoryName,BusinessName,ChanPMC,JiJXH,GouJRQ
FROM (tmp_apply t1 JOIN (
    SELECT FactoryName, ChanPMC, JiJXH, sale AS mode_sale
    FROM (
        SELECT FactoryName, ChanPMC, JiJXH, sale,
            ROW_NUMBER() OVER (PARTITION BY FactoryName, ChanPMC, JiJXH ORDER BY COUNT(*) DESC) AS rn
        FROM tmp_apply
        GROUP BY FactoryName, ChanPMC, JiJXH, sale
    ) t
    WHERE rn = 1
) t2
ON t1.FactoryName=t2.FactoryName AND t1.ChanPMC=t2.ChanPMC AND t1.JiJXH=t2.JiJXH)
WHERE ABS(sale-mode_sale)>5;

你可以将这个修正后的查询语句尝试运行，看看是否能够正常执行。