oracle - 在 unpivot 查询中查询 NULL 值

Question

我想从 Oracle DB 中获取列值为 NULL 的记录。我还在查询中使用 unpivot 。不知何故，由于 unpivot 关键字，NULL 值没有被选择。您能否帮助我了解如何在使用 unpivot 时获取相同的行。

编辑:

SELECT a.emp_id, a.emp_dept, b.emp_location
  FROM employee a,
       location b UNPIVOT (emp_id
                  FOR dummy_id
                  IN (emp_id AS 'EMP_ID', last_date AS 'LAST_DATE'))
 WHERE emp_id = 123 AND b.emp_loc_id = 'india' AND b.location IS NULL;

Answer 1

使用UNPIVOT INCLUDE NULLS :

SQL Fiddle

Oracle 11g R2 架构设置:

CREATE TABLE test ( id, a, b, c, d ) AS
SELECT 1, 1, 2, 3, 4 FROM DUAL UNION ALL
SELECT 2, 1, NULL, 3, NULL FROM DUAL;

查询 1:

SELECT *
FROM   test
UNPIVOT INCLUDE NULLS ( value FOR name IN ( a, b, c, d ) )

<强> Results :

| ID | NAME |  VALUE |
|----|------|--------|
|  1 |    A |      1 |
|  1 |    B |      2 |
|  1 |    C |      3 |
|  1 |    D |      4 |
|  2 |    A |      1 |
|  2 |    B | (null) |
|  2 |    C |      3 |
|  2 |    D | (null) |