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的记录，请问语句哪里错了？

https://zhidao.baidu.com/question/554941465.html

因为你的条件只考虑了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