Sql Server 2000：返回“true”或“假”基于25列中的任意一列为“真”

发布于 2024-08-08 23:14:30 字数 612 浏览 5 评论 0原文

我必须创建一个查询来检查几个不同的列，如果其中任何一个列的值为 1，我想返回 true。

理想的输出如下：

ID:55
姓名：约翰·多伊
IsDealerType1:True
IsDealerType2:True
IsDealerType3:False
IsDealerType4:False
IsDealerType5:True

问题是，我有大约 20 个名为 1a、1b、1c、1d 等的列，而不是那 5 个经销商列。如果任何“1”列为 true，则 IsDealerType1 应该为 true。

我试图避免在 VB.NET 代码中编写一些内容来检查每一列，只是因为在 SQL 中应该很容易避免这种纯粹的丑陋 - 如果我知道如何做到这一点 - 但我不确定如何做构造查询。我一直在尝试诸如……

SELECT id, 
      name, 
      (1a or 1b or 1c or 1d) as IsDealerType1, 
      (2a or 2b or 2c or 2d) as IsDealerType2 
where id = 55

但显然，我做得不对。

任何帮助表示赞赏。谢谢！

原文

I have to create a query that checks across several different columns, and if any of them have a 1, I want to return true.

Ideal output would be along the lines of:

ID:55
Name:John Doe
IsDealerType1:True
IsDealerType2:True
IsDealerType3:False
IsDealerType4:False
IsDealerType5:True

The problem is, instead of those 5 dealer columns, I have about 20 columns named 1a, 1b, 1c, 1d, etc. If any of the "1" columns is true, then IsDealerType1 should be true.

I'm trying to avoid writing something in the VB.NET code to check each and every column, just because that sheer ugliness should be easy to avoid in SQL - if only I knew how to do it - but I'm not sure how to construct the query. I've been trying stuff like...

SELECT id, 
      name, 
      (1a or 1b or 1c or 1d) as IsDealerType1, 
      (2a or 2b or 2c or 2d) as IsDealerType2 
where id = 55

... but obviously, I'm not doing it right.

Any help is appreciated. Thanks!

分享到QQ

分享到微博

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

发布评论

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

能怎样 2024-08-15 23:14:30

我喜欢拉塞尔的，但我还要添加这个：

CASE WHEN 1 IN (1a,1b,1c,1d) THEN 1 ELSE 0 END As IsDealerType1

I like Russel's, but I'm gonna add this as well:

CASE WHEN 1 IN (1a,1b,1c,1d) THEN 1 ELSE 0 END As IsDealerType1

回复收藏 0 原文

不知所踪 2024-08-15 23:14:30

(1a+1b+1c+1d)>的情况0 THEN 1 ELSE 0 END 作为 IsDealerType1

回复收藏 0 原文

煮酒 2024-08-15 23:14:30

使用 SQL 按位 OR 运算符。避免比较和强制转换。

示例：Joel 的答案将整数 1 或 0 传递给客户端，您可能期望的是位（布尔值）。莱姆斯的答案需要演员表和比较。

SELECT id, 
      name, 
      (1a | 1b | 1c | 1d) as IsDealerType1, 
      (2a | 2b | 2c | 2d) as IsDealerType2 
where id = 55

Use the SQL bitwise OR operator. Avoids comparisons and casts.

Example: Joel's answers passes integer 1 or 0 to the client, where you'd arguably expect bit (boolean). Remus' answers needs casts and comparisons.

SELECT id, 
      name, 
      (1a | 1b | 1c | 1d) as IsDealerType1, 
      (2a | 2b | 2c | 2d) as IsDealerType2 
where id = 55

回复收藏 0 原文

蓝色星空 2024-08-15 23:14:30

在 SQL 中，BIT 类型不能在布尔表达式中使用（哦！！），它们需要与整数进行比较：

SELECT id, name, 
   cast(
    case when ([1a]=1 or [1b]=1 or [1c]=1 or [1d]=1) then 1 else 0 end
    as bit) as IsDealerType1,
    cast(case when ([2a]=1 or [2b]=1 or [2c]=1 or [2d]=1) then 1 else 0 end
    as bit) as IsDealerType2 
from [table]
where id = 55

In SQL the BIT types cannot be used in boolean expressions (d'oh!!), they need to be compared with ints:

SELECT id, name, 
   cast(
    case when ([1a]=1 or [1b]=1 or [1c]=1 or [1d]=1) then 1 else 0 end
    as bit) as IsDealerType1,
    cast(case when ([2a]=1 or [2b]=1 or [2c]=1 or [2d]=1) then 1 else 0 end
    as bit) as IsDealerType2 
from [table]
where id = 55

回复收藏 0 原文

~没有更多了~