的Oracle SQL - 如果显示记录存在其他显示父
问题描述:
我想基于对数据进行分类分为两类:如果在T2存在的值,显示“A”的Oracle SQL - 如果显示记录存在其他显示父
,否则显示“B”作为“类型”。有没有一种方法来实现这种情况下,或解码?
T1是T2的父亲。
T1
1
2
3
4
5
T2
1
1
3
3
3
4
理想的情况下我的输出将
Type
A
B
A
A
B
编辑:我想补充一点,A和B是文本值我想根据我的上述条件来显示,这不是从DB到来。另外,T2根本没有相应的记录,所以我不能真正检查null。
答
试试这个:
SELECT T1.Col,
CASE WHEN T2.Col IS NOT NULL THEN 'A' ELSE 'B' END AS Type
FROM T1
LEFT JOIN (
SELECT DISTINCT Col FROM T2
) AS T2 ON T1.Col = T2.Col
答
甲骨文设置:
CREATE TABLE T1 (column_name) AS
SELECT LEVEL FROM DUAL CONNECT BY LEVEL < 6;
CREATE TABLE t2 (column_name) AS
SELECT 1 FROM DUAL UNION ALL
SELECT 1 FROM DUAL UNION ALL
SELECT 3 FROM DUAL UNION ALL
SELECT 3 FROM DUAL UNION ALL
SELECT 4 FROM DUAL;
查询:
SELECT NVL2(T2.column_name, 'A', 'B') AS Type
FROM T1
LEFT OUTER JOIN
(SELECT DISTINCT column_name FROM T2) T2
ON T1.column_name = T2.column_name
ORDER BY T1.column_name;
输出:
TYPE
----
A
B
A
A
B