SQL，在查詢期間根據資料條件創建新列

我已經設法拼湊了一個有效的 SQL 查詢，使用聯合和表的連接的組合，為我提供了我需要的臨時結果。

SELECT  n.study_id AS StudyId,
        n.practice_id AS PracticeId,
    n.FluVaxCode,
    n.Date,
    date(p.BaseStart / 1000, 'unixepoch') AS BaseStart, 
    date(p.BaseEnd / 1000, 'unixepoch') AS BaseEnd,
    date(p.OutcomeStart / 1000, 'unixepoch') AS OutcomeStart, 
    date(p.OutcomeEnd / 1000, 'unixepoch') AS OutcomeEnd,
    CASE WHEN Date BETWEEN BaseStart AND BaseEnd THEN 'Y' ELSE 'N' END AS BaseVax,
    CASE WHEN Date BETWEEN OutcomeStart AND OutcomeEnd THEN 'Y' ELSE 'N' END AS OutcomeVax
FROM toypractice p INNER JOIN
     (SELECT  t.study_id, t.practice_id,
     date(t.event_date / 1000, 'unixepoch') AS Date, 
     t.code_id AS FluVaxCode
     FROM toytherapy t
     WHERE t.code_id IN ('dher.', 'a6b1.', 'bk31.')
     UNION 
     SELECT  c.study_id, c.practice_id, 
     DATE(c.event_date / 1000, 'unixepoch') AS Date, 
     c.code_id AS FluVaxCode
     FROM toyclinical c
     WHERE c.code_id IN ('1383.', '229..', 'X77RW')
     ORDER BY FluVaxCode DESC
     ) n 
     ON p.practice_id = n.practice_id;

我最終得到了多列，包括三列：

Date, BaseStart, BaseEnd

所有這些都是 YYYY-MM-DD 格式（我認為）。有沒有辦法，在查詢程序中，我可以在查詢的末尾添加一些代碼，以便它會在輸出中創建一個新列，這樣如果 Date 介于 BaseStart 和 BaseEnd 之間，則新列中的值將是 ' Y'，否則新列中的值將是 'N'？在這種情況下，我使用 sqlite 進行開發/測驗，但它最終必須在 mssqlserver 2019 中作業。謝謝。J

uj5u.com熱心網友回復：

嘗試將以下內容添加到您當前的選擇串列中：

    , CASE WHEN n.Date BETWEEN date(p.BaseStart / 1000, 'unixepoch') AND date(p.BaseEnd / 1000, 'unixepoch') THEN 'a' ELSE 'b' END AS is_between

這可以通過不同的方式完成。但以上應該有效。

這是另一個應該起作用的調整。我已經將原始 event_date 添加到n派生表的結果中，并CASE在外部 SELECT 串列中添加了相應的運算式。其他 CASE 運算式可能需要進行類似的調整。

SELECT n.study_id AS StudyId
     , n.practice_id AS PracticeId
     , n.FluVaxCode
     , n.Date
     , date(p.BaseStart / 1000, 'unixepoch') AS BaseStart
     , date(p.BaseEnd / 1000, 'unixepoch') AS BaseEnd
     , date(p.OutcomeStart / 1000, 'unixepoch') AS OutcomeStart
     , date(p.OutcomeEnd / 1000, 'unixepoch') AS OutcomeEnd
     , CASE WHEN Date BETWEEN BaseStart AND BaseEnd THEN 'Y' ELSE 'N' END AS BaseVax
     , CASE WHEN Date BETWEEN OutcomeStart AND OutcomeEnd THEN 'Y' ELSE 'N' END AS OutcomeVax
     , CASE WHEN n.event_date BETWEEN p.BaseStart AND p.BaseEnd THEN 'a' ELSE 'b' END AS is_between
  FROM toypractice p
  JOIN (
         SELECT t.study_id, t.practice_id
              , t.event_date
              , date(t.event_date / 1000, 'unixepoch') AS Date
              , t.code_id AS FluVaxCode
           FROM toytherapy t
          WHERE t.code_id IN ('dher.', 'a6b1.', 'bk31.')
          UNION 
         SELECT c.study_id, c.practice_id
              , c.event_date
              , DATE(c.event_date / 1000, 'unixepoch') AS Date
              , c.code_id AS FluVaxCode
           FROM toyclinical c
          WHERE c.code_id IN ('1383.', '229..', 'X77RW')
          ORDER BY FluVaxCode DESC
       ) n 
    ON p.practice_id = n.practice_id
;

小提琴

像這樣的東西：

sqlite 的特定測驗用例，作為起點。

• cte - 提供幾行代表您當前的查詢結果
• cte2 - 顯示我們如何將日期 (conv_date) 轉換為與 Base 值兼容
• 最終查詢運算式 - 顯示如何使用來自 cte2 的派生 conv_date 正確計算 BETWEEN。

注意，我放棄了直接使用 date 的嘗試，這會產生錯誤的is_between結果。 is_between2顯示正確的結果。

WITH cte (date, BaseStart, BaseEnd) AS (
         SELECT 1467241200000, '2016-06-20', '2016-06-30' UNION
         SELECT 1467241200000, '2017-06-20', '2017-06-30'
     )
SELECT date(date / 1000, 'unixepoch'), BaseStart, BaseEnd
     , CASE WHEN Date BETWEEN BaseStart AND BaseEnd THEN 'a' ELSE 'b' END AS is_between
     , CASE WHEN date(date / 1000, 'unixepoch') BETWEEN BaseStart AND BaseEnd THEN 'a' ELSE 'b' END AS is_between2
  FROM cte
;

and also:

WITH cte (date, BaseStart, BaseEnd) AS (
         SELECT 1467241200000, '2016-06-20', '2016-06-30' UNION
         SELECT 1467241200000, '2017-06-20', '2017-06-30'
     )
   , cte2 AS (
         SELECT *
              , date(date / 1000, 'unixepoch') AS conv_date
           FROM cte
     )
SELECT date(date / 1000, 'unixepoch'), BaseStart, BaseEnd
     , CASE WHEN Date      BETWEEN BaseStart AND BaseEnd THEN 'a' ELSE 'b' END AS is_between
     , CASE WHEN conv_date BETWEEN BaseStart AND BaseEnd THEN 'a' ELSE 'b' END AS is_between2
  FROM cte2
;

結果：

一般回答：

SELECT Date, BaseStart, BaseEnd
     , CASE WHEN Date BETWEEN BaseStart AND BaseEnd THEN 'a' ELSE 'b' END AS is_between
  FROM ( your SQL ) AS derived_table
;

or

WITH cte AS (
       your sql
     )
SELECT Date, BaseStart, BaseEnd
     , CASE WHEN Date BETWEEN BaseStart AND BaseEnd THEN 'a' ELSE 'b' END AS is_between
  FROM cte
;

如果您選擇的識別符號與資料庫支持的 SQL 語言中的保留或關鍵字沖突，則可能需要添加引號。

標籤：sql 日期 条件语句

上一篇：變數的頻率（按月）

下一篇：Python：將數值轉換為日期，如SAS