如何在TSQL中创建一个布尔计算字段并在该计算字段上进行连接？

Question

首先检查一下这段代码。它看起来应该可以工作，但实际上却不能！（惊喜！）

无论如何，这是我最先尝试的：

SELECT
Status as status,
Address as ip,
PCName as pc_name,
(Numbers.Phone = 'CPU/' + PCName) as cpu_contact,
(Numbers.Phone = 'PC/' + PCName) as pc_contact,
(Numbers.Phone = 'LOGIN/' + PCName) as login_contact,
FROM IPAddress
WHERE $where  --Generated In code
JOIN Numbers
  ON ('CPU/' + PCName = Numbers.Phone) 
  OR ('PC/' + PCName = Numbers.Phone) 
  OR ('LOGIN/' + PCName = Numbers.Phone)

我需要一些布尔计算字段，并根据相似条件进行连接。我还希望结果能够折叠成单行。例如，我认为当前设置会执行以下操作：

status ip  cpu_contact pc_contact login_contact
-----------------------------------------------
foo    bar true        false      false
foo    bar false       true       false
foo    bar false       false      true

当然，我更愿意。

status ip  cpu_contact pc_contact login_contact
-----------------------------------------------
foo    bar true        true       true

有什么想法吗？重新设计数据库不是一个选项。如果是的话，我会这样做 :-)

- Frew

Answer 1

您可以使用GROUP BY和SUM来合并行：

SELECT
  Status as status, Address as ip, PCName as pc_name,
  cast(sum(case when (Numbers.Phone = 'CPU/' + PCName) then 1 else 0 end) as bit)
    as cpu_contact,
  cast(sum(case when (Numbers.Phone = 'PC/' + PCName) then 1 else 0 end)) as bit)
    as pc_contact,
  cast(sum(case when (Numbers.Phone = 'LOGIN/' + PCName) then 1 else 0 end) as bit)
    as login_contact,
FROM
  IPAddress
    JOIN Numbers ON 
      ('CPU/' + PCName = Numbers.Phone) OR ('PC/' + PCName = Numbers.Phone) OR 
      ('LOGIN/' + PCName = Numbers.Phone)
WHERE
  $where  --Generated In code
GROUP BY
  Status, Address, PCName

因为你正在对行进行逻辑或运算，所以总和为零是假的，而任何大于0的值都是真的。

- casperOne

---

哇，这真的很复杂！不过没关系。我认为它能完成任务；虽然我们有很多列没有列出来，但我宁愿程序的一部分复杂一些，也不希望整个程序都变得复杂。谢谢！ - Frew Schmidt

Answer 2

你需要使用Case/When进行比较。在这种情况下，我硬编码了1或0，但T-SQL会将硬编码的数字转换为int。如果你想要布尔值（位），你需要手动进行转换，像这样...

Convert(Bit, Case When Numbers.Phone = 'CPU/' + PCName Then 1 Else 0 End) as cpu_contact,
Convert(Bit, Case When Numbers.Phone = 'PC/' + PCName Then 1 Else 0 End) as pc_contact,
Convert(Bit, Case When Numbers.Phone = 'LOGIN/' + PCName Then 1 Else 0 End) as login_contact,

- G Mastros

Answer 3

SELECT
Status as status,
Address as ip,
PCName as pc_name,
case when sum(case when Numbers.Phone = 'CPU/' + PCName then 1 end) > 0 then 'true' else 'false' end as cpu_contact,
case when sum(case when Numbers.Phone = 'PC/' + PCName then 1 end) > 0 then 'true' else 'false' end as pc_contact,
case when sum(case when Numbers.Phone = 'LOGIN/' + PCName then 1 end) > 0 then 'true' else 'false' end as login_contact
FROM IPAddress
JOIN Numbers
  ON ('CPU/' + PCName = Numbers.Phone) 
  OR ('PC/' + PCName = Numbers.Phone) 
  OR ('LOGIN/' + PCName = Numbers.Phone)
WHERE -- your condition goes here
group by status, address, pc_name   

- Michael Buen