問題描述

我有很長的 SELECT 查詢，但我已將相關(guān)部分粘貼到此處.

I have quite a long SELECT query but I have pasted the relevant part here.

我需要將 CASE 語句的結(jié)果用于另一個 CASE 語句.我正在 SQL Server 中執(zhí)行此操作.

I need to use the result of the of my CASE statement to use in another CASE statement. I'm doing this in SQL Server.

非常感謝您的幫助.

SELECT
    CompanyContact.Name AS CompanyName,
    CASE 
       WHEN SUBSTRING(HeadLease.TenantBreakNotice, LEN(HeadLease.TenantBreakNotice), 1) = 'M'
          THEN CONVERT(VARCHAR(10), DATEADD(DD, -365 / (12 / SUBSTRING(HeadLease.TenantBreakNotice, 1, LEN(HeadLease.TenantBreakNotice) - 1)), HeadLease.TenantBreakDate), 103)
       WHEN SUBSTRING(HeadLease.TenantBreakNotice, LEN(HeadLease.TenantBreakNotice), 1) = 'Y'
          THEN CONVERT(VARCHAR(10), DATEADD(DD, -365 * (SUBSTRING(HeadLease.TenantBreakNotice, 1, LEN(HeadLease.TenantBreakNotice) - 1)), HeadLease.TenantBreakDate), 103)
       ELSE HeadLease.TenantBreakNotice
    END AS [TenantBreakNotice],  <-- I need this to be used in the case statement below.
    CASE
       WHEN [TenantBreakNotice] < CONVERT(varchar(10), getdate(), 103)  
          THEN 'Expiry' 
       WHEN [TenantBreakNotice] IS NULL 
          THEN 'Expiry' 
       ELSE 'Break' 
    END AS [LeaseEventType]
FROM 
    HeadLease  

推薦答案

不能在定義列別名的同一個 select 中使用列別名.通常的解決方案是重復(fù)邏輯(難以維護)，使用子查詢或 CTE.SQL Server 提供了另一種優(yōu)雅的解決方案:

You cannot use a column alias in the same select where it is defined. The usual solution is to repeat the logic (hard to maintain), use a subquery, or CTE. SQL Server offers another elegant solution:

SELECT hl.Name AS CompanyName, v.TenantBreakNotice,
       (CASE WHEN v.TenantBreakNotice < CONVERT(varchar(10), getdate(), 103)  THEN 'Expiry' 
             WHEN TenantBreakNotice IS NULL THEN 'Expiry' 
             ELSE 'Break' 
        END) AS [LeaseEventType]

FROM HeadLease hl OUTER APPLY
     (VALUES (CASE WHEN SUBSTRING(hl.TenantBreakNotice, LEN(hl.TenantBreakNotice), 1) = 'M'
                   THEN CONVERT(VARCHAR(10), DATEADD(DAY, -365/(12/SUBSTRING(hl.TenantBreakNotice, 1, LEN(hl.TenantBreakNotice) -1)), hl.TenantBreakDate), 103)
                   WHEN SUBSTRING(hl.TenantBreakNotice, LEN(hl.TenantBreakNotice), 1) = 'Y'
                   THEN CONVERT(VARCHAR(10), DATEADD(DAY, -365*(SUBSTRING(hl.TenantBreakNotice,1, LEN(hl.TenantBreakNotice)-1)), hl.TenantBreakDate), 103)
                   ELSE hl.TenantBreakNotice
              END) v(TenantBreakNotice);

當(dāng)然，邏輯是不正確的，因為您將日期作為字符串進行比較.然而，這是你需要自己弄清楚的事情.不要將日期轉(zhuǎn)換為日期操作的字符串.并且，您應(yīng)該將結(jié)果輸出為 YYYY-MM-DD，以便格式明確.

Of course, the logic is incorrect, because you are comparing dates as strings. However, that is something you need to figure out yourself. Don't convert dates to strings for date operations. And, you should output the results as YYYY-MM-DD so the formats are unambiguous.

這篇關(guān)于在另一個 Case 語句中使用 Case 語句的結(jié)果的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！