我是这样写的,执行不了:
select case
when q.mode = 0 then
(select userid from user where organid=q.organid)
when q.mode = 1 then
(select userid from qmuser)
when q.mode = 2 then
(select userid from tbuser)
end
from table1 q where q.id=1因为这种子查询有多个结果 select userid from user where organid=q.organid
select case
when q.mode = 0 then
(select userid from user where organid=q.organid)
when q.mode = 1 then
(select userid from qmuser)
when q.mode = 2 then
(select userid from tbuser)
end
from table1 q where q.id=1因为这种子查询有多个结果 select userid from user where organid=q.organid
from table1 q ,user a,qmuser b,tbuser c
where q.id=1
and a.organid=q.organid
and b.organid=q.organid
and c.organid=q.organid
不好意思,理解有誤。。
錯得好離譜。。抱謙。。
這樣的語句用一條sql基本很難實現吧。。
他只能对应着他的then只能是一条记录的一个字段
select DEPTNO,case
when DEPTNO = 10 then (select EMPNO from emp where EMPNO = 7369)
// 对应着DEPTNO = 10的记录,显示7369,他只能对应着一个具体的输出,而不是一个表,一个记录
end
from emp
DEPTNO CASEWHENDEPTNO=10THEN(SELECTEMPNOFROMEMPWHEREEMPNO=7369)END
20
30
30
20
30
30
10 7369
20
10 7369
30
20
30
20
10 7369
select (case when A.userid is null then .懒得写了,判断bc的. else a.userid end ) userid
from
table1 q ,
(select 0 mode,userid from user where organid=q.organid) A,
(select 1 mode,userid from qmuser) B,
(select 2 mode,userid from tbuser) C
where q.id=1
and q.mode = a.mode(+)
and q.mode = b.mode(+)
and q.mode = c.mode(+)
from
table1 q ,
(select 0 mode,userid from user where organid=q.organid) A,
(select 1 mode,userid from qmuser) B,
(select 2 mode,userid from tbuser) C
where q.id=1
and q.mode = a.mode(+)
and q.mode = b.mode(+)
and q.mode = c.mode(+)貌似可以,不錯~
即使不写存储过程,在程序里也可以分开写呀
你先把table1 查出来
根据结果在决定调用那一个方法
when q.mode = 0 then
(select userid from user where organid = q.organid and rownum<2)
when q.mode = 1 then
(select userid from qmuser and rownum<2)
when q.mode = 2 then
(select userid from tbuser and rownum<2)
end
from table1 q
where q.id = 1
我要查询的是多条记录,不是一个记录
应该不行吧
sql的结果是一个笛卡尔积
你这个查询本身就违背了笛卡尔积的原理呀
SELECT userid FROM USER U, TABLE1 Q
WHERE U.organid = Q.organid AND
Q.id = 1 and
Q.mode = 0
UNION
SELECT SELECT userid FROM qmuser U, TABLE1 Q
WHERE U.organid = Q.organid AND
Q.id = 1 and
Q.mode = 1
UNION
SELECT SELECT userid FROM tbuser U, TABLE1 Q
WHERE U.organid = Q.organid AND
Q.id = 1 and
Q.mode = 2