Oracle UNPIVOT 和 SYSDATE 给出奇怪的结果

Question

我正在尝试使用类似于以下的查询将列转置为行...

WITH 
query AS
(
    SELECT    SYSDATE AS SomeDate,
              'One' AS One,
              'Two' AS Two, 
              'Three' AS Three,
              'Four' AS Four,
              'Five' AS Five
        FROM dual
),
up_query AS
(
    SELECT * 
    FROM query
    UNPIVOT 
    ( 
     NUM FOR DUMMY 
     IN 
     ( 
      One AS 'One',
      Two AS 'Two',
      Three AS 'Three',
      Four AS 'Four',
      Five AS 'Five'
     )
    )
)
SELECT SYSDATE, b.*
  FROM up_query  b;

我期望 SomeDate 反映结果行的 SYSDATE... 但这就是我得到的结果：

SYSDATE   SOMEDATE       DUMMY  NUM
09-DEC-11 09-DEC-07      One    One
09-DEC-11 09-DEC-07      Two    Two
09-DEC-11 09-DEC-07      Three  Three
09-DEC-11 09-DEC-07      Four   Four
09-DEC-11 09-DEC-07      Five   Five

为什么 SOMEDATE 比 SYSDATE 早 4 年？

Answer 1

这似乎是 11.2.0.2 中的错误。我可以在 Linux x86-64, 11.2.0.2 上重现您的结果。

但是，在 11.2.0.3 上，在 Linux x86-64 上，我得到：

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Dec 10 01:20:32 2011

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> WITH
  2  query AS
  3  (
  4      SELECT    SYSDATE AS SomeDate,
  5                'One' AS One,
  6                'Two' AS Two,
  7                'Three' AS Three,
  8                'Four' AS Four,
  9                'Five' AS Five
 10          FROM dual
 11  ),
 12  up_query AS
 13  (
 14      SELECT *
 15      FROM query
 16      UNPIVOT
 17      (
 18       NUM FOR DUMMY
 19       IN
 20       (
 21        One AS 'One',
 22        Two AS 'Two',
 23        Three AS 'Three',
 24        Four AS 'Four',
 25        Five AS 'Five'
 26       )
 27      )
)
 28   29  SELECT SYSDATE, b.*
 30    FROM up_query  b;

SYSDATE   SOMEDATE  DUMMY NUM
--------- --------- ----- -----
10-DEC-11 10-DEC-11 One   One
10-DEC-11 10-DEC-11 Two   Two
10-DEC-11 10-DEC-11 Three Three
10-DEC-11 10-DEC-11 Four  Four
10-DEC-11 10-DEC-11 Five  Five

Answer 2

正如 Mark 在他的回答中提到的，这是至少 Oracle 11.2.0.1 和 11.2.0.2 版本中的一个错误。

但是根据这个 文章如果您遇到上述 Oracle 版本的问题，有一个解决方法，即将日期转换为 varchar 格式，然后将其转换回日期数据类型。

所以查询现在应该是：

WITH 
query AS
(
    SELECT     TO_CHAR(SYSDATE, 'RRRRMMDD') AS SomeDate,
              'One' AS One,
              'Two' AS Two, 
              'Three' AS Three,
              'Four' AS Four,
              'Five' AS Five
        FROM dual
),
up_query AS
(
    SELECT * 
    FROM query
    UNPIVOT 
    ( 
     NUM FOR DUMMY 
     IN 
     ( 
      One AS 'One',
      Two AS 'Two',
      Three AS 'Three',
      Four AS 'Four',
      Five AS 'Five'
     )
    )
)
SELECT SYSDATE, TO_DATE(SomeDate, 'RRRRMMDD') AS ActualSomeDate, b.*, 
  FROM up_query  b;