語境

我正在實作一個“職位發布”功能，它將列出可供用戶使用的職位。可以為陣列中的間隔start_date和/或稀疏日期設定作業。可用性檢查應使用模型上存在的先前描述的日期資訊對模型進行。end_dateservice_datesjobsavailabilities

它們的結構如下

作業

        Column          |             Type                               
------------------------- ------------------------------
 id                      | bigint                        
 service_dates           | timestamp without time zone[]
 start_date              | timestamp without time zone   
 end_date                | timestamp without time zone

可用性

        Column          |             Type                               
------------------------- ------------------------------
 id                      | bigint   
 user_id                 | integer                                          
 date                    | date
 spaces_left             | integer

那時加入時，我想將作業日期與表中存在的匹配日期交叉availabilities。問題是作業的日期可以通過兩種不同的方式獲得。

對于稀疏的日期，如果我執行以下左連接，我可以讓它作業：

SELECT j.*
FROM jobs j
LEFT JOIN 
    availabilities a 
        ON a.user_id = 1234
        AND a.date in (
          --this will compose a set of the sparse dates
          SELECT UNNEST(j.service_dates)
        )

對于start_dateandend_date選項，這可以解決問題：

SELECT j.*
FROM jobs j
LEFT JOIN 
    availabilities a 
        ON a.user_id = 1234
        AND a.date in (
          --this will compose a set with the date range between the two dates
          SELECT GENERATE_SERIES(j.start_date::date, j.end_date::date, '1 day'::interval)
        )

我的問題是我需要根據jobs.service_dates特定作業記錄的存在條件申請一個或另一個。最初我認為我可以執行以下操作：

SELECT j.*
FROM jobs j
LEFT JOIN 
    availabilities a 
        ON a.user_id = 1234
        AND a.date in (
          -- use one or the other based on whether or not there's something on j.service_dates
          CASE cardinality(j.service_dates) > 0
          WHEN TRUE THEN
             (SELECT UNNEST(j.services))
          ELSE
            (SELECT GENERATE_SERIES(j.start_date::date, j.end_date::date, '1) day'::interval)
          END
        )

但我收到以下錯誤： ERROR: more than one row returned by a subquery used as an expression

如果我嘗試從CASE那里的陳述句回傳的任何內容中進行選擇，我會得到以下資訊： ERROR: set-returning functions are not allowed in CASE

我認為我有我需要的東西，即如何在已經弄清楚的場景中邏輯關聯表格。我需要的是一種有條件地應用任一邏輯的方法。

更新 1

正如@Kadet 指出的那樣，表格user_id中缺少，availabilitites所以我添加了這一點。他的回答也讓我完成了我想要的，我最終沒有采用完全相同的解決方案，但我CASE在加入時應用了他建議的方式，而不是我嘗試的方式。他的回答沒有任何問題，我只是沒有使用它，因為這個查詢還有其他特殊性，我沒有提出問題（為簡單起見），這些特殊性在我選擇的方式下會更好地作業。我是這樣做的：

LEFT JOIN
    availabilities a
      ON a.user_id = 1234
      AND case when cardinality(j.service_dates) > 0
        THEN a.date IN ( SELECT unnest(j.service_dates) )
        ELSE a.date IN ( SELECT GENERATE_SERIES(j.start_date::date, j.end_date::date, '1 day'::interval) )
      END

更新 2

從概念上講，對@Ely 的榮譽提及他的回答是完全可以接受的，我相信這是正確的。這對我不起作用，因為我沒有在我的問題的初稿中公開細節，這是并且service_dates不是相互排斥的。前 2 個始終存在，填充有的最小值和最大值。但他的想法可能適用于任何有類似問題而沒有特定限制的人。如果那是你，那就給他投票吧:)start_dateend_dateservice_date

uj5u.com熱心網友回復：

此表結構無效或不完整（可用性表中沒有 user_id 欄位）但只是為了展示一個想法


SELECT j.*
FROM jobs j
LEFT JOIN 
    availabilities a 
        ON a.user_id = 1234
WHERE 
   case when cardinality(j.service_dates) > 0
     then a.date = any(j.service_dates)
     else a.date between start_date and end_date
   end

或者，如果需要生成系列

SELECT j.*
FROM jobs j
LEFT JOIN 
    availabilities a 
        ON a.user_id = 1234
WHERE 
   case when cardinality(j.service_dates) > 0
     then a.date = any(j.service_dates)
     else a.date = any ( select GENERATE_SERIES(j.start_date::date, j.end_date::date, '1 day'::interval))
   end

uj5u.com熱心網友回復：

怎么樣(A && !B) || (!A && B)- 這基本上就是你想要實作的目標。要么之一，要么另一個。

我會嘗試將其翻譯成以下內容：

SELECT j.*
FROM jobs j
LEFT JOIN 
    availabilities a 
        ON a.user_id = 1234
        AND
        (
          (
            a.date in (SELECT UNNEST(j.service_dates)
            AND (cardinality(j.service_dates) = 0)
          )
          OR
          (
            a.date in (SELECT GENERATE_SERIES(j.start_date::date, j.end_date::date, '1 day'::interval)
            AND NOT (cardinality(j.service_dates) = 0) 
          )
        )

可能存在語法錯誤。我并不精通，也沒有從你寫的東西中得到啟發，但我希望你能明白。

轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/481677.html

標籤：sql 数据库 PostgreSQL 加入左连接

上一篇：如何在postgresql中使用時區更新日期？

下一篇：確定逗號分隔字串的長度

有條件地使用set-retuning函式作為join子句

語境

作業

可用性

更新 1

更新 2