SQL IN 语句未正确检查

Question

我正在使用具有多个"代码"或"id"关联的表中的列/字段。

表afacctbal有一个名为afacbbalid的列/字段，其中的可能性可以是COST，PRN或COLL。

我遇到的问题是拉动帐户上 COST 等于 0 的行。

我在 where 子句中尝试了一个子查询的 SQL 语句，但返回了一个一般错误。现在我正在尝试一个 IN 语句，但这似乎也不起作用。

这是我得到的：

select araccount.aracid as AccountID, arentity.arenst State, arentity.ARENNAME ClientName,
 afaccount.afaccurbal CurrentBal, afacctbal.afacbcurbal PrincipalBal,
  afacctbal.AFACBbalid
from araccount
 inner join arrelationship on araccount.aracid = arrelationship.arrelacid
 inner join arentity on arentity.arenid = arrelationship.ARRELENID
 inner join afaccount on afaccount.afacacctid = araccount.ARACID
 inner join afacctbal on afaccount.AFACKEY = afacctbal.AFACBACCTID
where afacctbal.afacbbalid in("COST",0)
    and afaccount.AFACRATEID = "MN100"
    and arentity.ARENST = "MN"
    and araccount.araclstdte > "2013-04-01"
order by afaccount.afaccurbal

问题出在我的 where 条款中这里：

where afacctbal.afacbbalid in("COST",0)

我将如何检查成本并检查它是否等于 0？

AFACCTBAL TABLLE使用的值： 阿法克巴利德 -- 余额ID AFACBCURBAL -- 每个余额 ID 的余额金额。表税表使用的值： AFACCURBAL -- 当前余额

Answer 1

您正在使用字符串"COST"然后0整数。因此，您需要在afacctbal.afacbbalid具有的列的数据类型中强制转换两者。

Answer 2

我认为

下面的查询将满足您的要求 -

select araccount.aracid as AccountID, arentity.arenst State, arentity.ARENNAME ClientName,
 afaccount.afaccurbal CurrentBal, afacctbal.afacbcurbal PrincipalBal,
  afacctbal.AFACBbalid
from araccount
 inner join arrelationship on araccount.aracid = arrelationship.arrelacid
 inner join arentity on arentity.arenid = arrelationship.ARRELENID
 inner join afaccount on afaccount.afacacctid = araccount.ARACID
 inner join afacctbal on afaccount.AFACKEY = afacctbal.AFACBACCTID
where ((afacctbal.afacbbalid ='COST'
    and afaccurbal.currentBal = 0) or 
     (afacctbal.afacbbalid ='PRN'
    and afaccurbal.currentBal > 0))
    and afaccount.AFACRATEID = "MN100"
    and arentity.ARENST = "MN"
    and araccount.araclstdte > "2013-04-01"
order by afaccount.afaccurbal