sql - 如果为空，则将字段转换为位 0，如果不是，则转换为位 1

Question

我使用这种方法来检查一行是否在另一个表中是外键的。

select
  personID,
  name,
  (
    /* CAST THIS PART TO BIT return 1 if field is found, 0 if not */
    select top 1 parents.personID
    from parents
    where parents.personID=persons.personID

  ) as inUse
from persons

可以按照我评论的去做吗？

Answer 1

你可以使用exists和case表达式:

select personID
     , name
     , case when exists ( select *
                          from parents
                          where parents.personID=persons.personID) then 1 else 0 end as inUse
from persons

或者只是执行一个left join:

select distinct pe.personID
     , pe.name
     , case when pa.personID is null then 0 else 1 end as inUse
from persons pe
left join parents pa on pa.personID = pe.personID