请问这样此SQL语句为什么查询不到同一张表两个字段

select cf.id,
case when cf.statu in(0,1) then co.username when cf.statu in(2,3) then ss.username end as "创建人",
case when cf.statu in(1,2)then co.username when cf.statu in(0,3) then ss.username end as "发送人"
from cowf as cf,comuser as co,admin as ss
where cf.createid=(case when cf.statu in(0,1)then co.userid else ss.userid)
and cf.sendid=(case when cf.statu in(1,2)then co.userid else ss.userid)

cowf(
id number,
createid number,
sendid number,
statu number --状态识别ID
--0,comuser向admin发送
--1,comuser向comuser发送
--2,admin向comuser发送
--3,admin向admin发送
)
comuser(
userid number,
username varchar2(20)
)
admin(
userid number,
username varchar2(20)
)

问题是只能查询到当cowf.statu为0和2的记录,得不到1,3的记录,请问语句哪里错了?

sql

2个回答

因为你的条件只考虑了comuser和admin之间的发送啊。
简单点,4个状态分开写,可以直接利用索引,性能更好

    SELECT cf.id, c.username AS "创建人", s.username As "发送人"
      FROM cowf    AS cf
      JOIN comuser AS c ON cf.createid = c.userid
      JOIN admin   AS s ON cf.sendid   = s.userid
     WHERE cf.statu = 0
    UNION ALL
    SELECT cf.id, c.username AS "创建人", s.username As "发送人"
      FROM cowf    AS cf
      JOIN comuser AS c ON cf.createid = c.userid
      JOIN comuser AS s ON cf.sendid   = s.userid
     WHERE cf.statu = 1
    UNION ALL
    SELECT cf.id, c.username AS "创建人", s.username As "发送人"
      FROM cowf    AS cf
      JOIN admin   AS c ON cf.createid = c.userid
      JOIN comuser AS s ON cf.sendid   = s.userid
     WHERE cf.statu = 2
    UNION ALL
    SELECT cf.id, c.username AS "创建人", s.username As "发送人"
      FROM cowf    AS cf
      JOIN admin   AS c ON cf.createid = c.userid
      JOIN admin   AS s ON cf.sendid   = s.userid
     WHERE cf.statu = 3
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐