www.毛片,亚洲成人首页,国产天天操

本文介紹了Oracle UNPIVOT 和 SYSDATE 給出奇怪的結果的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學習吧！

問題描述

限時送ChatGPT賬號..

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

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 給出奇怪的結果的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！

【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題，如果有圖片或者內容侵犯了您的權益，請聯系我們刪除處理，感謝您的支持！

久久久久久久av_日韩在线中文_看一级毛片视频_日本精品二区_成人深夜福利视频_武道仙尊动漫在线观看

Oracle UNPIVOT 和 SYSDATE 給出奇怪的結果

問題描述

相關文檔推薦