需要在oracle中寫一條SQL查詢

employees我有一個表名ID和hire_date列。所以，我需要撰寫查詢，它將給出如下輸出：

當它會hire_date is null給出的。反之亦然，就像什么時候那樣它會給出ID=101hire_dateID=110hire_date is nullid=110hire dateID=101

uj5u.com熱心網友回復：

您可以在分析函式中使用條件聚合來避免自聯接：

SELECT e.*,
       CASE
       WHEN hire_date IS NULL AND id = 101
       THEN COALESCE(
              MIN(CASE id WHEN 110 THEN hire_date END) OVER (),
              DATE '1900-01-01' -- Default if both NULL
            )
       WHEN hire_date IS NULL AND id = 110
       THEN COALESCE(
              MIN(CASE id WHEN 101 THEN hire_date END) OVER (),
              DATE '1900-01-01' -- Default if both NULL
            )
       ELSE hire_date -- value for other rows.
       END AS value
FROM   employees e;

其中，對于樣本資料：

CREATE TABLE employees (id, name, hire_date) AS
SELECT 101, 'Alice', DATE '1970-01-01' FROM DUAL UNION ALL
SELECT 110, 'Beryl', NULL              FROM DUAL UNION ALL
SELECT 111, 'Carol', DATE '1980-01-01' FROM DUAL;

輸出：

小提琴

uj5u.com熱心網友回復：

你的意思是這樣嗎？

SELECT
    id,
    hire_date,
    CASE
        WHEN hire_date IS NULL AND id = 101 THEN
            ( SELECT hire_date FROM employees WHERE id = 110 )
        WHEN hire_date IS NULL AND id = 110 THEN
            ( SELECT hire_date FROM employees WHERE id = 101 )
        ELSE
            NULL
    END AS hire_date2
FROM
    employees

uj5u.com熱心網友回復：

試試這個方法...

-- sample data
WITH
    emps AS
        (
            SELECT 101 "ID", 'John' "NAME", To_Date('01.08.2000', 'dd.mm.yyyy') "HIRE_DATE" FROM DUAL Union All
            SELECT 110 "ID", 'Jane' "NAME", Null "HIRE_DATE" FROM DUAL Union All
            SELECT 120 "ID", 'Mark' "NAME", To_Date('01.10.2000', 'dd.mm.yyyy') "HIRE_DATE" FROM DUAL
        )
-- main SQL
SELECT
    ID, NAME, HIRE_DATE,
    CASE WHEN ID = 101 THEN Nvl(HIRE_DATE, ( SELECT hire_date FROM emps WHERE id = 110 )    )
         WHEN ID = 110 THEN Nvl(HIRE_DATE, ( SELECT hire_date FROM emps WHERE id = 101 )    )
    ELSE
        HIRE_DATE
    END "ALTERNATIVE_HIRE_DATE"
FROM
    emps

結果 ：

如果有可能在兩個hiredate（ids 101 和110）中都有空值，那么你可以使用嵌套的 Nvl() 函式來做到這一點。

-- sample data (2)
WITH
    emps AS
        (
            SELECT 101 "ID", 'John' "NAME", Null "HIRE_DATE" FROM DUAL Union All
            SELECT 110 "ID", 'Jane' "NAME", Null "HIRE_DATE" FROM DUAL Union All
            SELECT 120 "ID", 'Mark' "NAME", To_Date('01.10.2000', 'dd.mm.yyyy') "HIRE_DATE" FROM DUAL
        )
-- main SQL
SELECT
    ID, NAME, HIRE_DATE,
    CASE WHEN ID = 101 THEN Nvl(Nvl(HIRE_DATE, ( SELECT hire_date FROM emps WHERE id = 110 )    ), To_Date('01.01.2000', 'dd.mm.yyyy'))
         WHEN ID = 110 THEN Nvl(Nvl(HIRE_DATE, ( SELECT hire_date FROM emps WHERE id = 101 )    ), To_Date('01.01.2000', 'dd.mm.yyyy'))
    ELSE
        HIRE_DATE
    END "ALTERNATIVE_HIRE_DATE"
FROM
    emps

結果 ：

標籤：sql甲骨文加入

上一篇：檢查資料框中的一對列是否與另一個資料框中的一對列匹配

下一篇：在laravel中回傳錯誤的ID