WHERE子句返回结果不符合预期，求助排查比较运算符失效问题

以下是你的SQL语句中导致WHERE子句结果不符合预期的关键问题及修复方案：

1. WHERE子句不能使用列别名
   你在WHERE里写了Country in ('USA')，但Country是country_txt的查询别名，WHERE子句执行逻辑早于SELECT列别名的生成，无法识别该别名，必须替换为原始列名：

   country_txt IN ('USA')
   

2. 行级过滤与分组后过滤混淆
   若(Col1-Col2) >=8是要过滤单条数据中Col1减Col2的结果≥8，放在WHERE里没问题；但如果你的需求是分组后"Number of men"的总和≥8，必须把这个条件移到HAVING子句中——WHERE负责分组前过滤行数据，HAVING才是分组后过滤聚合结果的正确方式：

   GROUP BY country_txt, DATE_FROM_PARTS(iyear, imonth, iday)
   HAVING SUM(Col1-Col2) >=8
   

3. 冗余语法与类型匹配问题

   • iyear=('2022')的括号完全多余，若iyear是数值类型，直接写iyear=2022即可；如果是字符串类型，iyear='2022'更简洁。
   • 带空格的列别名需要用引号或方括号包裹（不同数据库语法略有差异，比如SQL Server用[]，MySQL用 ），避免解析错误，比如AS "Number of events"。
   • 确认eventid是数值类型，若为字符串类型，>=10会按字符顺序比较（比如"100"会小于"9"），需先转换类型：CAST(eventid AS INT) >=10。

修复后的SQL示例（行级过滤场景） #

SELECT 
    country_txt AS "Country"
    ,DATE_FROM_PARTS(iyear, imonth, iday) AS "Date"
    ,COUNT(eventid) AS "Number of events"
    ,SUM(Col1-Col2) AS "Number of men"
    ,SUM(Col3) AS "Number of women"
    ,SUM(Col4) AS "Number of kids"
FROM xxx
WHERE iyear = 2022 
  AND country_txt IN ('USA') 
  AND eventid >= 10 
  AND (Col1 - Col2) >= 8
GROUP BY country_txt, DATE_FROM_PARTS(iyear, imonth, iday)
ORDER BY "Country" ASC, "Date" ASC;

修复后的SQL示例（分组后过滤场景） #

SELECT 
    country_txt AS "Country"
    ,DATE_FROM_PARTS(iyear, imonth, iday) AS "Date"
    ,COUNT(eventid) AS "Number of events"
    ,SUM(Col1-Col2) AS "Number of men"
    ,SUM(Col3) AS "Number of women"
    ,SUM(Col4) AS "Number of kids"
FROM xxx
WHERE iyear = 2022 
  AND country_txt IN ('USA') 
  AND eventid >= 10
GROUP BY country_txt, DATE_FROM_PARTS(iyear, imonth, iday)
HAVING SUM(Col1-Col2) >= 8
ORDER BY "Country" ASC, "Date" ASC;

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