SQL 语法错误(从事务代码为 211 和 292 的account_ID中排除事务代码为 293 的account_ID)

Question

sql语法错误(以293的交易代码为293的交易代码为211＆amp; 292(

SELECT *
FROM 
    (SELECT ACCOUNT_ID
     FROM Transaction_Data_Flexi
     WHERE TRAN_CODE = 211 OR TRAN_CODE = 292) AS trn1
LEFT JOIN
    (SELECT ACCOUNT_ID
     FROM Transaction_Data_Flexi
     WHERE TRAN_CODE = 293) AS trn2
WHERE 
    trn2.ACCOUNT_ID NOT IN trn1.ACCOUNT_ID
GROUP BY 
    ACCOUNT_ID

Answer 1

您缺少一个条件ON。如果您不打算指定条件，请使用CROSS JOIN而不是LEFT JOIN。

将有另一个错误 - 对于GROUP BY子句，您需要指定您要分组的列。

    GROUP BY  trn1.account_id, trn2.account_id

Answer 2

我建议使用group by和having：

这样做

SELECT ACCOUNT_ID
FROM Transaction_Data_Flexi
GROUP BY ACCOUNT_ID
HAVING SUM(IIF(TRAN_CODE = 211, 1, 0)) > 0 AND
       SUM(IIF(TRAN_CODE = 292, 1, 0)) > 0 AND
       SUM(IIF(TRAN_CODE = 293, 1, 0)) = 0;

这将检查给定交易的代码。having条件验证代码。

Answer 3

感谢伙计们的帮助！是的，您应该使用，但我想尝试使用"不"语句，并且我已经更新了以下查询。我仍然有问题，说"

"的"位置"陈述附近缺少操作员：

SELECT trn1.ACCOUNT_ID
FROM 
(
SELECT ACCOUNT_ID
FROM Transaction_Data_Flexi
WHERE TRAN_CODE = 211 OR TRAN_CODE = 292
) AS trn1
WHERE ACCOUNT_ID NOT IN 
(
SELECT ACCOUNT_ID
FROM Transaction_Data_Flexi
WHERE TRAN_CODE = 293
) AS trn2
GROUP BY ACCOUNT_ID