我有一个查询，我正在从中提取事务。我希望能够在我的商品字段=a1和newvalue字段<>a1的情况下为每个案例编号的任何交易提取交易。换句话说，我有2个案例编号，每个案例编号有5笔交易，一个案例编号的交易记录是commodity=a1和newvalue=a1。另一个案例有一个记录，其中commodity=a1和newvalue=b2，这就是我希望在查询中返回的案例。请记住，前面的案例可能有相同的事务，但不应返回，因为存在newvalue=a1的记录。我附加了一个图像和黄色突出显示的记录是我期望我的输出。下面是我当前的“where”语句，我需要帮助重新编写。我还被告知，我可能需要一个“组由”的声明，我试图，但仍然拉相同的结果。

SELECT 
       Allcases.caseno as caseno, Allcases.division_desc as division_desc, Allcases.close_date as close_date, Allcases.week_of as week_of, 
       Allcases.case_type as case_type, 
       a.transactdate as transactdate, a.transacttypeid as transacttypeid, a.userid as userid,
       concat(RTRIM(Usr.fullname), ' <', RTRIM(Usr.EmailAddress), '>') as CR1_CR2_FULLNAME,
       Allcases.commodity as commodity,
       b.oldvalue as oldvalue, b.newvalue as newvalue, changereason as changereason       
FROM   
(
select b.*, sum(case when b.newvalue = 'A1' then 1 else 0 end) over(partition by Allcases.caseno) cnt_new_value_A1
from
       dataiku.qca_casedatachange_parquet b 
       INNER JOIN dataiku.qcatransact_parquet a 
              ON b.transactid = a.transactid
       INNER JOIN dataiku.qca_validated_cases_consolidated_parquet Allcases 
              ON a.casedataid = Allcases.casedataid
       INNER JOIN dataiku.set_qca_reclassification_head_parquet h
              ON Allcases.caseno = h.caseno
       INNER JOIN dataiku.qca_user_parquet Usr
              ON a.UserID = Usr.UserID
where 
    b.FieldID = 6
    AND a.transacttypeid IN (1, 2, 3)
    AND Allcases.commodity = 'A1'
)s
where cnt_new_value_A1 = 0
ORDER BY Allcases.caseno, A.transactid