問題描述

我正在嘗試使用類似于以下的查詢將列轉(zhuǎn)換為行...

WITH查詢AS(選擇 SYSDATE 作為某個日期，一"作為一，二"作為二，三"作為三，四"作為四，五"作為五從雙),up_query AS(選擇 *FROM 查詢轉(zhuǎn)軸(數(shù)字為假人在(一個作為一個"，兩個 AS '兩個'，三作為三"，四作為四"，五作為五")))選擇系統(tǒng)日期，b.*FROM up_query b;

我期待 SomeDate 為結(jié)果行反映 SYSDATE...但這是我得到的結(jié)果:

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 版本中的一個錯誤.

但是根據(jù) this 文章 如果您堅持使用上述 Oracle 版本，則有一個解決方法，即將日期轉(zhuǎn)換為 varchar 格式，然后將其轉(zhuǎn)換回日期數(shù)據(jù)類型.>

所以查詢現(xiàn)在應(yīng)該是:

WITH查詢AS(SELECT TO_CHAR(SYSDATE, 'RRRRMMDD') AS SomeDate,一"作為一，二"作為二，三"作為三，四"作為四，五"作為五從雙),up_query AS(選擇 *FROM 查詢轉(zhuǎn)軸(數(shù)字為假人在(一個作為一個"，兩個 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;

這篇關(guān)于Oracle UNPIVOT 和 SYSDATE 給出奇怪的結(jié)果的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！