Oracle UNPIVOT 和 SYSDATE 给出奇怪的结果 [英] Oracle UNPIVOT and SYSDATE giving weird results

问题描述

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

WITH查询AS(选择 SYSDATE 作为某个日期，一"作为一，二"作为二，三"作为三，四"作为四，五"作为五从双),up_query AS(选择 *FROM 查询转轴(数字为假人在(一个作为一个"，两个 AS '两个'，三作为三"，四作为四"，五作为五")))选择系统日期，b.*FROM up_query b;

我期待 SomeDate 为结果行反映 SYSDATE...但这是我得到的结果:

SYSDATE SOMEDATE DUMMY NUM09-DEC-11 09-DEC-07 一一09-DEC-11 09-DEC-07 二二09-DEC-11 09-DEC-07 三三09-DEC-11 09-DEC-07 四 四09-DEC-11 09-DEC-07 五五

为什么 SOMEDATE 比 SYSDATE 早 4 年?

解决方案

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

但是根据 this 文章 如果您坚持使用上述 Oracle 版本，则有一个解决方法，即将日期转换为 varchar 格式，然后将其转换回日期数据类型.>

所以查询现在应该是:

WITH查询AS(SELECT TO_CHAR(SYSDATE, 'RRRRMMDD') AS SomeDate,一"作为一，二"作为二，三"作为三，四"作为四，五"作为五从双),up_query AS(选择 *FROM 查询转轴(数字为假人在(一个作为一个"，两个 AS '两个'，三作为三"，四作为四"，五作为五")))SELECT SYSDATE, TO_DATE(SomeDate, 'RRRRMMDD') AS ActualSomeDate, b.*,FROM up_query b;

I am trying to transpose columns to rows using query similar to the following...

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;

I was expecting SomeDate to reflect SYSDATE for the resulting rows... But this is the result I am getting:

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

Why is the SOMEDATE 4 years earlier than SYSDATE?

解决方案

As Mark mentioned in his answer, this is a bug in Oracle 11.2.0.1 and 11.2.0.2 versions atleast.

However as per this article there is a workaround if you are stuck with the Oracle versions mentioned above, which is to convert the date to varchar format and then convert it back to date datatype.

So the query should now be:

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;

这篇关于Oracle UNPIVOT 和 SYSDATE 给出奇怪的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！