在另一个 Case 语句中使用 Case 语句的结果

Use result of Case statement in another Case statement(在另一个 Case 语句中使用 Case 语句的结果)
本文介绍了在另一个 Case 语句中使用 Case 语句的结果的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

问题描述

我有很长的 SELECT 查询,但我已将相关部分粘贴到此处.

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

我需要将 CASE 语句的结果用于另一个 CASE 语句.我正在 SQL Server 中执行此操作.

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 中使用列别名.通常的解决方案是重复逻辑(难以维护),使用子查询或 CTE.SQL Server 提供了另一种优雅的解决方案:

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);

当然,逻辑是不正确的,因为您将日期作为字符串进行比较.然而,这是你需要自己弄清楚的事情.不要将日期转换为日期操作的字符串.并且,您应该将结果输出为 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.

这篇关于在另一个 Case 语句中使用 Case 语句的结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!

相关文档推荐

Creating table with T-SQL - can#39;t see created tables in Object explorer(使用 T-SQL 创建表 - 在对象资源管理器中看不到创建的表)
How to check if VARCHAR strings are (not) hexadecimal?(如何检查 VARCHAR 字符串是否为(非)十六进制?)
Arithmetic overflow error converting IDENTITY to data type int(将 IDENTITY 转换为数据类型 int 的算术溢出错误)
Where clause if there are multiple of the same ID(如果有多个相同的 ID,Where 子句)
Azure SQL: Invalid Object Name using Powershell#39;s quot;Invoke-sqlcmdquot; on Adventureworks(Azure SQL:使用 Powershell 的“Invoke-sqlcmd的无效对象名称在 Adventureworks 上)
How to Convert Table Data into xml format using sql with multiple sub nodes(如何使用具有多个子节点的sql将表数据转换为xml格式)