如何使用 T-SQL 的 Case/When？

发布于 2024-09-11 17:42:31 字数 504 浏览 11 评论 0原文

我有一个巨大的查询，经常使用case/when。现在我这里有这个SQL，它不起作用。

 (select case when xyz.something = 1
 then
     'SOMETEXT'
 else
      (select case when xyz.somethingelse = 1)
      then
          'SOMEOTHERTEXT'
      end) 

      (select case when xyz.somethingelseagain = 2)
      then
          'SOMEOTHERTEXTGOESHERE'
      end)
 end) [ColumnName],

造成问题的是 xyz.somethingelseagain = 2，它表示无法绑定该表达式。 xyz 是在查询中进一步连接的表的一些别名。这里出了什么问题？删除 2 个案例/时间之一可以纠正这一问题，但我需要这两个案例，甚至可能需要更多案例。

原文

I have a huge query which uses case/when often. Now I have this SQL here, which does not work.

 (select case when xyz.something = 1
 then
     'SOMETEXT'
 else
      (select case when xyz.somethingelse = 1)
      then
          'SOMEOTHERTEXT'
      end) 

      (select case when xyz.somethingelseagain = 2)
      then
          'SOMEOTHERTEXTGOESHERE'
      end)
 end) [ColumnName],

Whats causing trouble is xyz.somethingelseagain = 2, it says it could not bind that expression. xyz is some alias for a table which is joined further down in the query. Whats wrong here? Removing one of the 2 case/whens corrects that, but I need both of them, probably even more cases.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

鲸落 2024-09-18 17:42:31

SELECT
   CASE 
   WHEN xyz.something = 1 THEN 'SOMETEXT'
   WHEN xyz.somethingelse = 1 THEN 'SOMEOTHERTEXT'
   WHEN xyz.somethingelseagain = 2 THEN 'SOMEOTHERTEXTGOESHERE'
   ELSE 'SOMETHING UNKNOWN'
   END AS ColumnName;

SELECT
   CASE 
   WHEN xyz.something = 1 THEN 'SOMETEXT'
   WHEN xyz.somethingelse = 1 THEN 'SOMEOTHERTEXT'
   WHEN xyz.somethingelseagain = 2 THEN 'SOMEOTHERTEXTGOESHERE'
   ELSE 'SOMETHING UNKNOWN'
   END AS ColumnName;

回复收藏 0 原文

夜深人未静 2024-09-18 17:42:31

一旦 WHEN 语句为 true，中断就会隐式发生。

您必须考虑哪个 WHEN 表达式最有可能发生。如果您将 WHEN 放在一长串 WHEN 语句的末尾，您的 sql 可能会变慢。所以把它放在前面作为第一个。

更多信息请参见：T-SQL 中的 case 语句中的中断

回复收藏 0 原文

や三分注定 2024-09-18 17:42:31

declare @n int = 7,
    @m int = 3;

select 
    case 
        when @n = 1 then
            'SOMETEXT'
    else
        case 
            when @m = 1 then
                'SOMEOTHERTEXT'
            when @m = 2 then
                'SOMEOTHERTEXTGOESHERE'
        end
    end as col1
-- n=1 => returns SOMETEXT regardless of @m
-- n=2 and m=1 => returns SOMEOTHERTEXT
-- n=2 and m=2 => returns SOMEOTHERTEXTGOESHERE
-- n=2 and m>2 => returns null (no else defined for inner case)

declare @n int = 7,
    @m int = 3;

select 
    case 
        when @n = 1 then
            'SOMETEXT'
    else
        case 
            when @m = 1 then
                'SOMEOTHERTEXT'
            when @m = 2 then
                'SOMEOTHERTEXTGOESHERE'
        end
    end as col1
-- n=1 => returns SOMETEXT regardless of @m
-- n=2 and m=1 => returns SOMEOTHERTEXT
-- n=2 and m=2 => returns SOMEOTHERTEXTGOESHERE
-- n=2 and m>2 => returns null (no else defined for inner case)

回复收藏 0 原文

撩发小公举 2024-09-18 17:42:31

如果逻辑测试针对单个列，那么您可以使用类似

USE AdventureWorks2012;  
GO  
SELECT   ProductNumber, Category =  
      CASE ProductLine  
         WHEN 'R' THEN 'Road'  
         WHEN 'M' THEN 'Mountain'  
         WHEN 'T' THEN 'Touring'  
         WHEN 'S' THEN 'Other sale items'  
         ELSE 'Not for sale'  
      END,  
   Name  
FROM Production.Product  
ORDER BY ProductNumber;  
GO

If logical test is against a single column then you could use something like

USE AdventureWorks2012;  
GO  
SELECT   ProductNumber, Category =  
      CASE ProductLine  
         WHEN 'R' THEN 'Road'  
         WHEN 'M' THEN 'Mountain'  
         WHEN 'T' THEN 'Touring'  
         WHEN 'S' THEN 'Other sale items'  
         ELSE 'Not for sale'  
      END,  
   Name  
FROM Production.Product  
ORDER BY ProductNumber;  
GO

More information - https://learn.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-2017

回复收藏 0 原文

~没有更多了~