执行SQL语句报错：AND操作符参数需为布尔值问题咨询

Alright, let's walk through why you're hitting that error and fix your SQL statement properly.

1. 核心错误：GROUP BY子句的语法误用 #

The error message argument of AND must be type boolean, not type numeric comes directly from this problematic line in your SQL:

group by recall_case_id AND ASIN not IN (select asin from booker.d_unified_cust_shipment_items where marketplace_id in (1,7))

GROUP BY is meant to list the columns you want to group your results by—not to combine a column with a logical condition using AND. When you write recall_case_id AND ..., the database tries to evaluate this as a boolean expression, but recall_case_id is a numeric type. Since you can't perform an AND operation between a number and a boolean condition, it throws that error.

2. 过滤条件放错位置 #

Your ASIN NOT IN (...) filter belongs in the WHERE clause, not after GROUP BY. The WHERE clause filters rows before grouping happens, while GROUP BY only defines how to aggregate your results.

3. 空IN列表的语法问题 #

recall_case_id in () is invalid in nearly all SQL engines—you can't have an empty list inside IN. If you need to exclude all recall_case_id values, use recall_case_id IS NULL instead. If you intended to include specific IDs, fill the list with valid values (e.g., IN (123, 456)). If this condition isn't needed at all, just remove it.

修正后的SQL语句 #

Here's the fixed version with all issues addressed (comments highlight key changes):

SELECT count(asin), recall_case_id
FROM hs3c_prod_stage.d_arnold_activity_batch_asin
WHERE region_id='1' 
  AND batch_create_date>'2020-01-01' 
  -- 把ASIN过滤条件移到WHERE子句中
  AND ASIN NOT IN (
    SELECT asin 
    FROM booker.d_unified_cust_shipment_items 
    WHERE marketplace_id IN (1,7)
  )
  -- 修复空IN列表问题（替换为实际ID或按需调整）
  AND recall_case_id IN (123, 456, 789) 
-- GROUP BY仅保留分组字段
GROUP BY recall_case_id

If you need to filter aggregated results (e.g., only keep groups with a count greater than 0), add a HAVING clause after GROUP BY—for example:

GROUP BY recall_case_id
HAVING count(asin) > 0

内容的提问来源于stack exchange，提问作者Sharath Devnur S