SQL多条件查询异常求助：仅返回部分指定条件结果

先排查结果异常的原因 #

你的SQL逻辑本身是正确的，(col_G = 'A1' OR col_G = '1001') AND (col_H = 'B2' OR col_H = 'B3') 理论上应该返回四种组合的结果：A1+B2、A1+B3、1001+B2、1001+B3。只出现最后一种结果，优先排查以下几个点：

• 验证目标组合是否存在有效数据：分别执行以下查询，确认表中有没有符合所有前置条件的对应数据：

  -- 检查A1+B2组合
  SELECT COUNT(*) 
  FROM table_001 
  WHERE col_A > '1977987' AND (col_B BETWEEN '1000' AND '6000') 
        AND col_C < '2' AND (col_d BETWEEN '12345' AND '23456') 
        AND col_E != 'STUFF' AND col_F != 'STUFF' 
        AND col_G = 'A1' AND col_H = 'B2';
  
  -- 检查A1+B3组合
  SELECT COUNT(*) 
  FROM table_001 
  WHERE col_A > '1977987' AND (col_B BETWEEN '1000' AND '6000') 
        AND col_C < '2' AND (col_d BETWEEN '12345' AND '23456') 
        AND col_E != 'STUFF' AND col_F != 'STUFF' 
        AND col_G = 'A1' AND col_H = 'B3';
  
  -- 检查1001+B2组合
  SELECT COUNT(*) 
  FROM table_001 
  WHERE col_A > '1977987' AND (col_B BETWEEN '1000' AND '6000') 
        AND col_C < '2' AND (col_d BETWEEN '12345' AND '23456') 
        AND col_E != 'STUFF' AND col_F != 'STUFF' 
        AND col_G = '1001' AND col_H = 'B2';
  

  如果这些查询返回0，说明表中确实没有对应数据，不是SQL逻辑的问题。

• 检查字段值的格式问题：字符类型字段常出现隐形空格、大小写不一致的情况，比如col_G存储的是'A1 '（末尾带空格）或'a1'，直接用=匹配会失败。可以用以下方式验证：

  -- 查看col_G的所有唯一值，确认是否有格式异常
  SELECT DISTINCT col_G FROM table_001 WHERE col_G LIKE '%A1%';
  -- 去除前后空格后再匹配
  SELECT COUNT(*) 
  FROM table_001 
  WHERE TRIM(col_G) = 'A1' AND TRIM(col_H) = 'B2' 
        AND col_A > '1977987' AND (col_B BETWEEN '1000' AND '6000') 
        AND col_C < '2' AND (col_d BETWEEN '12345' AND '23456') 
        AND col_E != 'STUFF' AND col_F != 'STUFF';
  

• 检查字段类型是否匹配：如果col_G或col_H是数值类型，用字符串'A1'或'1001'匹配会触发自动类型转换，可能导致匹配失败。可以用DESCRIBE table_001;（MySQL）或sp_help table_001（SQL Server）查看字段类型。

大表查询优化建议 #

针对60-300万行的大表，优化查询性能避免全表扫描：

• 添加复合索引：将WHERE子句的过滤字段和排序字段组合成索引，把过滤性强的字段放在前面：

  CREATE INDEX idx_table001_filter_sort ON table_001 (col_A, col_B, col_C, col_d, col_G, col_H);
  

  若是InnoDB表，索引最后加上排序字段col_d，可避免额外的排序操作。

• 精简查询列：只选择实际需要的列，减少数据传输和内存占用。如果必须选54列，尽量创建覆盖索引（包含所有查询列），避免数据库回表查询。

• 拆分查询（可选）：若索引优化后仍慢，可拆成多个小查询再合并，利用索引提升效率：

  SELECT (54 different columns) 
  FROM table_001 
  WHERE col_A > '1977987' AND (col_B BETWEEN '1000' AND '6000') 
        AND col_C < '2' AND (col_d BETWEEN '12345' AND '23456') 
        AND col_E != 'STUFF' AND col_F != 'STUFF' 
        AND col_G = 'A1' AND col_H = 'B2'
  UNION ALL
  SELECT (54 different columns) 
  FROM table_001 
  WHERE col_A > '1977987' AND (col_B BETWEEN '1000' AND '6000') 
        AND col_C < '2' AND (col_d BETWEEN '12345' AND '23456') 
        AND col_E != 'STUFF' AND col_F != 'STUFF' 
        AND col_G = 'A1' AND col_H = 'B3'
  UNION ALL
  SELECT (54 different columns) 
  FROM table_001 
  WHERE col_A > '1977987' AND (col_B BETWEEN '1000' AND '6000') 
        AND col_C < '2' AND (col_d BETWEEN '12345' AND '23456') 
        AND col_E != 'STUFF' AND col_F != 'STUFF' 
        AND col_G = '1001' AND col_H = 'B2'
  UNION ALL
  SELECT (54 different columns) 
  FROM table_001 
  WHERE col_A > '1977987' AND (col_B BETWEEN '1000' AND '6000') 
        AND col_C < '2' AND (col_d BETWEEN '12345' AND '23456') 
        AND col_E != 'STUFF' AND col_F != 'STUFF' 
        AND col_G = '1001' AND col_H = 'B3'
  ORDER BY col_d ASC;
  

  注意：UNION ALL不会去重，若有重复数据可改用UNION，但性能会稍差。

内容的提问来源于stack exchange，提问作者Jerome Dela Cruz