主页 / dba / 问题 / 1170
Accepted
Pranav Shah
Asked: 2011-02-11 15:42:46 +0800 CST

Oracle SQL - WHERE 子句中的 CASE

  • 772

有可能以某种方式做到这一点吗？

WITH T1 AS
(
   SELECT 1 AS SEQ, 'NOTHING 1' AS SOME_TYPE FROM DUAL UNION ALL
   SELECT 2 AS SEQ, 'NOTHING 2' AS SOME_TYPE FROM DUAL UNION ALL
   SELECT 3 AS SEQ, 'SOMETHING 1' AS SOME_TYPE FROM DUAL UNION ALL
   SELECT 4 AS SEQ, 'SOMETHING 2' AS SOME_TYPE FROM DUAL UNION ALL
   SELECT 5 AS SEQ, 'SOMETHING 3' AS SOME_TYPE FROM DUAL UNION ALL
   select 6 as seq, 'SOMETHING 4' AS SOME_TYPE from dual
)
, T2 AS
(
   SELECT 'A' AS COMPARE_TYPE FROM DUAL UNION ALL
   SELECT 'B' AS COMPARE_TYPE FROM DUAL 
)
SELECT T2.*, T1.* 
FROM T1, T2
WHERE  CASE T2.COMPARE_TYPE 
         WHEN 'A'
            THEN T1.SOME_TYPE LIKE 'NOTHING%'
         ELSE T1.SOME_TYPE NOT LIKE 'NOTHING%' 
      END

我知道我的 WHERE is 子句不正确。

任何帮助都有助于了解这种类型的陈述是否可行。

我不想编写动态 SQL。如果必须，我将编写 2 条不同的 SQL 语句。

谢谢

oracle case

3 个回答

  1. Best Answer

    感谢您发布示例数据。用文字和实际输出描述您希望查询返回的内容也很有帮助。

    我猜你想要类似的东西

    SQL> ed
Wrote file afiedt.buf

  1  WITH T1 AS
  2  (
  3  SELECT 1 AS SEQ, 'NOTHING 1' AS SOME_TYPE FROM DUAL UNION ALL
  4  SELECT 2 AS SEQ, 'NOTHING 2' AS SOME_TYPE FROM DUAL UNION ALL
  5  SELECT 3 AS SEQ, 'SOMETHING 1' AS SOME_TYPE FROM DUAL UNION ALL
  6  SELECT 4 AS SEQ, 'SOMETHING 2' AS SOME_TYPE FROM DUAL UNION ALL
  7  SELECT 5 AS SEQ, 'SOMETHING 3' AS SOME_TYPE FROM DUAL UNION ALL
  8  select 6 as seq, 'SOMETHING 4' AS SOME_type from dual
  9  )
 10  , T2 AS
 11  (
 12  SELECT 'A' AS COMPARE_TYPE FROM DUAL UNION ALL
 13  SELECT 'B' AS COMPARE_type FROM DUAL
 14  )
 15  SELECT T2.*, T1.*
 16    FROM T1, T2
 17   WHERE (CASE WHEN T2.COMPARE_TYPE = 'A' AND
 18                    T1.SOME_TYPE LIKE 'NOTHING%'
 19                 THEN 1
 20               WHEN T2.COMPARE_TYPE != 'A' AND
 21                    T1.SOME_TYPE NOT LIKE 'NOTHING%'
 22                 THEN 1
 23               ELSE 0
 24*           END) = 1
SQL> / 

C        SEQ SOME_TYPE
- ---------- -----------
A          1 NOTHING 1
A          2 NOTHING 2
B          3 SOMETHING 1
B          4 SOMETHING 2
B          5 SOMETHING 3
B          6 SOMETHING 4

6 rows selected.

    但是我对你的代码应该是什么意思做了很多猜测。

    这似乎与有人在OTN 论坛中提出的问题相同。我的答案在两个地方都是一样的。

    • 15

  2. 尝试以这种方式编写 where 子句：

    WHERE (T2.COMPARE_TYPE = 'A' AND T1.SOME_TYPE LIKE 'NOTHING%')
   OR (T2.COMPARE_TYPE <> 'A' AND T1.SOME_TYPE NOT LIKE 'NOTHING%')
    • 6

  3. Justin Cave 和 Eric Humphrey 的查询都返回不同的结果。这是第三个同样有效的答案，它返回第三组不同的结果：

    WHERE (T2.COMPARE_TYPE = 'A' AND T1.SOME_TYPE LIKE 'NOTHING%')
OR T1.SOME_TYPE NOT LIKE 'NOTHING%'

    只有您会知道哪个答案给出了您期望的结果，但它们都是给出的问题的正确答案。

    您的问题比许多问题要好，因为它是自包含的并包含源数据，但如果您也包含您正在寻找的输出，它将有所帮助。我建议您将其添加到问题中，并确保接受的答案与这些结果相匹配。

    • 4

相关问题