请问在Oracle中如何实现Case when then 中存放表结果？

Question

我现在有以下语句：
select q.workClassID from 
(case when to_number( substr(t.endtime,0,2))<to_number( substr(t.begintime,0,2))
then (select t.workClassID from policestationworkclass t where to_number( substr(t.endtime,0,2))>=5 
and to_number( substr(t.begintime,0,2))>=5 and t.policeStationID=6) 
when (to_number( substr(t.endtime,0,2))>to_number( substr(t.begintime,0,2)))
then (select t.workClassID from policestationworkclass t where to_number( substr(t.begintime,0,2))<=12 
AND to_number( substr(t.endtime,0,2))>=12 and t.policeStationID=6) end) q
请高手看看为什么错误，我应该如何用case来选择不同条件下的标数据？

Answer 1

SELECT q.workclassid
  FROM (SELECT CASE
                  WHEN TO_NUMBER (SUBSTR (t.endtime, 0, 2)) <
                                        TO_NUMBER (SUBSTR (t.begintime, 0, 2))
                     THEN (SELECT t.workclassid
                             FROM policestationworkclass t
                            WHERE TO_NUMBER (SUBSTR (t.endtime, 0, 2)) >= 5
                              AND TO_NUMBER (SUBSTR (t.begintime, 0, 2)) >= 5
                              AND t.policestationid = 6)
                  WHEN (TO_NUMBER (SUBSTR (t.endtime, 0, 2)) >
                                        TO_NUMBER (SUBSTR (t.begintime, 0, 2))
                       )
                     THEN (SELECT t.workclassid
                             FROM policestationworkclass t
                            WHERE TO_NUMBER (SUBSTR (t.begintime, 0, 2)) <= 12
                              AND TO_NUMBER (SUBSTR (t.endtime, 0, 2)) >= 12
                              AND t.policestationid = 6)
               END
          FROM DUAL) q
你居然里层sql没有select 和from 太强了
把我改的from dual 改成你需要的表

Answer 2

t.workclassid
你这个t表在哪都不知道，整个SQL里就没找到此表SELECT q.workclassid
  FROM (SELECT CASE
                  WHEN TO_NUMBER (SUBSTR (t.endtime, 0, 2)) <
                                        TO_NUMBER (SUBSTR (t.begintime, 0, 2))
                     THEN (SELECT t.workclassid
                             FROM policestationworkclass t
                            WHERE TO_NUMBER (SUBSTR (t.endtime, 0, 2)) >= 5
                              AND TO_NUMBER (SUBSTR (t.begintime, 0, 2)) >= 5
                              AND t.policestationid = 6)
                  WHEN (TO_NUMBER (SUBSTR (t.endtime, 0, 2)) >
                                        TO_NUMBER (SUBSTR (t.begintime, 0, 2))
                       )
                     THEN (SELECT t.workclassid
                             FROM policestationworkclass t
                            WHERE TO_NUMBER (SUBSTR (t.begintime, 0, 2)) <= 12
                              AND TO_NUMBER (SUBSTR (t.endtime, 0, 2)) >= 12
                              AND t.policestationid = 6)
               END
          FROM t) q

Answer 3

不行。你要用动态sql拼接
或者这样试试
select q.workClassID
  from (select t.workClassID
               from policestationworkclass t
              where to_number(substr(t.endtime, 0, 2)) >= 5
                and to_number(substr(t.begintime, 0, 2)) >= 5
                and t.policeStationID = 6
                and (
                    to_number(substr(t.endtime, 0, 2)) < to_number(substr(t.begintime, 0, 2))
                    and to_number(substr(t.endtime, 0, 2)) >= 5
                    and to_number(substr(t.begintime, 0, 2)) >= 5
                    )
                        or
                    (
                    to_number(substr(t.endtime, 0, 2)) >
                    to_number(substr(t.begintime, 0, 2)) 
                    and to_number(substr(t.begintime, 0, 2)) <= 12
                    AND to_number(substr(t.endtime, 0, 2)) >= 12
                    )
                )    
         ) q 

Answer 4

看了下你的语句
改一下
SELECT q.workclassid
  FROM policestationworkclass t1
 WHERE EXISTS (
          SELECT 1
            FROM policestationworkclass t
           WHERE TO_NUMBER (SUBSTR (t.endtime, 0, 2)) >= 5
             AND TO_NUMBER (SUBSTR (t.begintime, 0, 2)) >= 5
             AND t.policestationid = 6
             AND t.workclassid = t1.workclassid)
    OR EXISTS (
          SELECT 1
            FROM policestationworkclass t
           WHERE TO_NUMBER (SUBSTR (t.begintime, 0, 2)) <= 12
             AND TO_NUMBER (SUBSTR (t.endtime, 0, 2)) >= 12
             AND t.policestationid = 6
             AND t.workclassid = t1.workclassid)

Answer 5

2楼的 大哥你的方法我执行后说“单行子查询返回多于一行”，是不是我的WHEN 条件不对?

Answer 6

我详细说下我的问题吧？我现在有一张作息时间表，数据
ID   PID  WorkClassID  begintime  endtime       
1 6 21 05:00 15:00
2 6 24 15:00 23:00
3 6 25 23:00 07:00 我现在要在任何时候传入时间判断和PID来获取WorkClassID，由于时间有跨天的，所以要判断，请问如何写SQL语句？注意：begintime  和endtime是字符串类型请高手帮忙，谢谢

Answer 7

WITH t AS
     (SELECT 1 ID, 6 pid, 21 workclassid, '05:00' begintime, '15:00' endtime
        FROM DUAL
      UNION ALL
      SELECT 2, 6, 24, '15:00', '23:00'
        FROM DUAL
      UNION ALL
      SELECT 3, 6, 25, '23:00', '07:00'
        FROM DUAL)
SELECT tt.workclassid
  FROM (SELECT t.*,
               CASE
                  WHEN TO_NUMBER (SUBSTR (t.endtime, 0, 2)) <
                            TO_NUMBER (SUBSTR (t.begintime, 0, 2))
                     THEN CASE
                            WHEN TO_NUMBER (SUBSTR (t.begintime, 0, 2)
                                           ) >= 5
                               THEN 1
                            ELSE 0
                         END
                  WHEN TO_NUMBER (SUBSTR (t.endtime, 0, 2)) >=
                                        TO_NUMBER (SUBSTR (t.begintime, 0, 2))
                     THEN CASE
                            WHEN TO_NUMBER (SUBSTR (t.begintime, 0, 2)) <= 12
                               THEN 1
                            ELSE 0
                         END
               END begin_flag,
               CASE
                  WHEN TO_NUMBER (SUBSTR (t.endtime, 0, 2)) <
                              TO_NUMBER (SUBSTR (t.begintime, 0, 2))
                     THEN CASE
                            WHEN TO_NUMBER (SUBSTR (t.endtime, 0, 2)) >=
                                                                   5
                               THEN 1
                            ELSE 0
                         END
                  WHEN TO_NUMBER (SUBSTR (t.endtime, 0, 2)) >=
                              TO_NUMBER (SUBSTR (t.begintime, 0, 2))
                     THEN CASE
                            WHEN TO_NUMBER (SUBSTR (t.endtime, 0, 2)) >=
                                                                  12
                               THEN 1
                            ELSE 0
                         END
               END end_flag
          FROM t) tt
 WHERE tt.begin_flag = 1 AND tt.end_flag = 1结果
Row# WORKCLASSID1 21
2 25你看看是不是符合你的逻辑

Answer 8

上面忘记加where PID=6SELECT tt.workclassid
  FROM (SELECT t.*,
               CASE
                  WHEN TO_NUMBER (SUBSTR (t.endtime, 0, 2)) <
                            TO_NUMBER (SUBSTR (t.begintime, 0, 2))
                     THEN CASE
                            WHEN TO_NUMBER (SUBSTR (t.begintime, 0, 2)
                                           ) >= 5
                               THEN 1
                            ELSE 0
                         END
                  WHEN TO_NUMBER (SUBSTR (t.endtime, 0, 2)) >=
                                        TO_NUMBER (SUBSTR (t.begintime, 0, 2))
                     THEN CASE
                            WHEN TO_NUMBER (SUBSTR (t.begintime, 0, 2)) <= 12
                               THEN 1
                            ELSE 0
                         END
               END begin_flag,
               CASE
                  WHEN TO_NUMBER (SUBSTR (t.endtime, 0, 2)) <
                              TO_NUMBER (SUBSTR (t.begintime, 0, 2))
                     THEN CASE
                            WHEN TO_NUMBER (SUBSTR (t.endtime, 0, 2)) >=
                                                                   5
                               THEN 1
                            ELSE 0
                         END
                  WHEN TO_NUMBER (SUBSTR (t.endtime, 0, 2)) >=
                              TO_NUMBER (SUBSTR (t.begintime, 0, 2))
                     THEN CASE
                            WHEN TO_NUMBER (SUBSTR (t.endtime, 0, 2)) >=
                                                                  12
                               THEN 1
                            ELSE 0
                         END
               END end_flag
          FROM t
          where pid=6
          
          ) tt
 WHERE tt.begin_flag = 1 AND tt.end_flag = 1

Answer 9

oracle 中有 decode 函數和 case when 的功能差不多

Answer 10

decode只能做等于判断，不能做> <判断

Answer 11

SELECT q.workclassid
  FROM (SELECT CASE
                  WHEN TO_NUMBER (SUBSTR (t.endtime, 0, 2)) <
                                        TO_NUMBER (SUBSTR (t.begintime, 0, 2))
                     THEN (SELECT t.workclassid
                             FROM policestationworkclass t
                            WHERE TO_NUMBER (SUBSTR (t.endtime, 0, 2)) >= 5
                              AND TO_NUMBER (SUBSTR (t.begintime, 0, 2)) >= 5
                              AND t.policestationid = 6)
                  WHEN (TO_NUMBER (SUBSTR (t.endtime, 0, 2)) >
                                        TO_NUMBER (SUBSTR (t.begintime, 0, 2))
                       )
                     THEN (SELECT t.workclassid
                             FROM policestationworkclass t
                            WHERE TO_NUMBER (SUBSTR (t.begintime, 0, 2)) <= 12
                              AND TO_NUMBER (SUBSTR (t.endtime, 0, 2)) >= 12
                              AND t.policestationid = 6)
               END
          FROM DUAL) q