使用左联接命令查询时如何替换列值SQL Server 2008 r2 [英] How to replace a column values when querying with left join command SQL Server 2008 r2

问题描述

好的，让我用示例来解释我的问题

Alright let me explain my question with example

我们有一个包含以下内容的表

We have a table that contains

Id
Name
Number

现在的例子

1 House 4
2 Hospital 3
3 Airport 'null'
4 Station 2

---

 select t1.id, 
       t1.name,
       t2.name as name2
from your_table t1
left join your_table t2 on t1.number = t2.id

按上述查询时，确定包含列的"null"值给出错误.因此，我想以某种方式修改上面的查询，以将name2返回为null，并且不会为该行提供错误.

Ok when querying as the above, that 'null' value containing column is giving error. So i want to modify above query in a way that it will return name2 as null and won't give error for that rows.

所以我期望的结果应该是:

So the result I expect should be:

1 House Station
2 Hospital Airport
3 Airport null
4 Station Hospital

此null为字符串.

我得到的当前错误

消息245，第16级，状态1，第5行
将varchar值'null'转换为数据类型smallint时，转换失败.

Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the varchar value 'null' to data type smallint.

谢谢

推荐答案

您应修复数据库设计.同时，使用 NULLIF 可获得预期的结果:

You should fix your database design. Meantime, use NULLIF to get your expected results:

select t1.id, 
       t1.name,
       t2.name as name2
from your_table t1
left join your_table t2 on NULLIF( t1.number, 'NULL' ) = t2.id

这篇关于使用左联接命令查询时如何替换列值SQL Server 2008 r2的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！