如何使用联合而不是NULL>

I don't know how to get the to the one retrieved by:

我不知道怎样才能找到那个被找回的人:

SELECT 
    AddressLine1 + ', ' + AddressLine2 AS Address,  
    City  
FROM
    [Person].[Address] 
WHERE 
    [AddressLine1] is not null 
    AND [AddressLine2] is not null

I need to use COALESCE() function. Every time I try it gets errors. How should I use coalesce() to get the same?

我需要使用COALESCE()函数。每次我尝试它都会出错。如何使用coalesce()来获得相同的结果?

2 个解决方案

#1

You cannot do that. COALESCE is a function that returns the first non-null value.

你不能这样做。联合是返回第一个非空值的函数。

I think you intended to write this query:

我想你想写这个问题:

SELECT AddressLine1 +', '+ AddressLine2 AS Address, City FROM [Person].[Address] 
WHERE COALESCE([AddressLine1], [AddressLine2]) is not null

It will have the same effect as:

其效果与:

SELECT AddressLine1 +', '+ AddressLine2 AS Address, City FROM [Person].[Address] 
WHERE [AddressLine1] is not null OR [AddressLine2] is not null

The difference between OR in this query vs AND in yours is small, but oh so important. Apart from that, it's not much shorter, faster or better readable, so I don't think this is a good use case for COALESCE.

这个查询和您的查询之间的差异很小，但是非常重要。除此之外，它并不是更短、更快或可读性更好，所以我认为这不是一个很好的合并用例。

#2

Presumably, you want something like this:

大概，你想要这样的东西:

SELECT COALESCE(AddressLine1, '') +', '+ COALESCE(AddressLine2, '') AS Address, City
FROM [Person].[Address];

That way you can do the string concatenation, even when the values are NULL.

这样，即使值为空，也可以执行字符串连接。

#1