有可能以某种方式做到这一点吗?
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。 如果有必要,我会写两条不同的 SQL 语句。
谢谢
感谢您提供示例数据。用文字和实际输出来描述您希望查询返回的内容也会有所帮助。
我猜您想要的是类似于
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 论坛 上提出的问题相同。 我的答案在两个地方都是一样的。
试着这样写 where 子句:
WHERE (T2.COMPARE_TYPE = 'A' AND T1.SOME_TYPE LIKE 'NOTHING%')
OR (T2.COMPARE_TYPE <> 'A' AND T1.SOME_TYPE NOT LIKE 'NOTHING%')
贾斯汀-凯夫和埃里克-汉弗莱的查询都返回了不同的结果。 下面是第三个同样有效的答案,它返回了第三组不同的结果:
WHERE (T2.COMPARE_TYPE = 'A' AND T1.SOME_TYPE LIKE 'NOTHING%')
OR T1.SOME_TYPE NOT LIKE 'NOTHING%'
只有你自己才知道哪个答案会给出你所期望的结果,但它们都是对所给问题的正确回答。
您的问题比很多问题都要好,因为它是自包含的,包含了源数据,但如果您也包含您正在寻找的输出结果,那将会有所帮助。 我建议你将其添加到问题中,并确保所接受的答案与这些结果相匹配。